none
How to auto-populate a date fields in an Access 2007 Form

    Question

  • I am not a developer, but I am familiar with MS Access 2003. I am very new to Access 2007 and in the process of building a database that will be used to track purchase order requests.  I have created a form that co-workers will use to enter information into a table.  I want to set up the form to auto-populate two different fields with forecast dates after the user enters a sent date in a field.  The forecast date in the second field is dependent on the date in the first forcast field.  If the user doesn't enter a sent date, both forecast fields should remain blank.

    I have been working on this for several days and have tried to use the DateAdd function to accomplish this task, with no success.  Do I need to use VBA or a macro?  Any advice, code examples, etc. would be greatly appreciated.

    Thanks!

    Wednesday, September 15, 2010 2:46 PM

Answers

  • I am not a developer, but I am familiar with MS Access 2003. I am very new to Access 2007 and in the process of building a database that will be used to track purchase order requests.  I have created a form that co-workers will use to enter information into a table.  I want to set up the form to auto-populate two different fields with forecast dates after the user enters a sent date in a field.  The forecast date in the second field is dependent on the date in the first forcast field.  If the user doesn't enter a sent date, both forecast fields should remain blank.

    I have been working on this for several days and have tried to use the DateAdd function to accomplish this task, with no success.  Do I need to use VBA or a macro?  Any advice, code examples, etc. would be greatly appreciated.

    Thanks!


    Generally, you should not store dependant data.  Your forecast dates should be displayed in unbound textboxes with a ControlSource using the DateAdd, i.e. =DateAdd("d", 6, [SentTime]) (or whatever the formula is).  This value will not be stored, but you can use this formula in any query, form, or report that requires this information.  That way, your forecasted values will always be correct.

     


    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    • Marked as answer by Bessie Zhao Wednesday, September 22, 2010 9:42 AM
    Wednesday, September 15, 2010 4:41 PM
  • I'd at least partially agree with Roger: if this forecast date is in fact dependent on the entered date, then it should not be stored but just calculated dynamically.

    If, on the other hand, you want the "two weeks later" or whatever it is to be a default which the user can choose to edit, so that for some records the valid value is different from the calculation, you should indeed store it. To do so you can use the SetValue action in a Macro, or VBA code. In either case you would use the AfterUpdate event of the "sent date" field. The VBA (with which I'm more familiar) would be something like

    Private Sub txtSaleDate_AfterUpdate()
    If IsDate(Me!txtSaleDate) Then ' see if user entered a date
      If IsNull(Me!txtDeliveryDate) Then ' don't stomp on existing data
       Me!txtDeliveryDate = DateAdd("d", 14, Me!txtSaleDate)
      End If
    End If
    End Sub
    
    

    Tweak the fieldnames and expressions to match your own needs of course.

     


    John W. Vinson/MVP
    • Marked as answer by Bessie Zhao Wednesday, September 22, 2010 9:42 AM
    Wednesday, September 15, 2010 5:21 PM
  • cyber..

    They can be either*, but using the controlnames is typical.

    *(so long as Access can recognise the names at runtime)

     

    • Marked as answer by Bessie Zhao Wednesday, September 22, 2010 9:42 AM
    Friday, September 17, 2010 4:48 PM

All replies

  • Hi

    I have no experience with A2007 but I think that DateAdd function is the way to go.

    Where or how have you been tring to use it?

    You should be able to set the value of the forecast fields if you use it in the After Update event of the sent date field.


    Bob Fitz BizSoftware
    Wednesday, September 15, 2010 3:14 PM
  • I am not a developer, but I am familiar with MS Access 2003. I am very new to Access 2007 and in the process of building a database that will be used to track purchase order requests.  I have created a form that co-workers will use to enter information into a table.  I want to set up the form to auto-populate two different fields with forecast dates after the user enters a sent date in a field.  The forecast date in the second field is dependent on the date in the first forcast field.  If the user doesn't enter a sent date, both forecast fields should remain blank.

    I have been working on this for several days and have tried to use the DateAdd function to accomplish this task, with no success.  Do I need to use VBA or a macro?  Any advice, code examples, etc. would be greatly appreciated.

    Thanks!


    Generally, you should not store dependant data.  Your forecast dates should be displayed in unbound textboxes with a ControlSource using the DateAdd, i.e. =DateAdd("d", 6, [SentTime]) (or whatever the formula is).  This value will not be stored, but you can use this formula in any query, form, or report that requires this information.  That way, your forecasted values will always be correct.

     


    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    • Marked as answer by Bessie Zhao Wednesday, September 22, 2010 9:42 AM
    Wednesday, September 15, 2010 4:41 PM
  • I'd at least partially agree with Roger: if this forecast date is in fact dependent on the entered date, then it should not be stored but just calculated dynamically.

    If, on the other hand, you want the "two weeks later" or whatever it is to be a default which the user can choose to edit, so that for some records the valid value is different from the calculation, you should indeed store it. To do so you can use the SetValue action in a Macro, or VBA code. In either case you would use the AfterUpdate event of the "sent date" field. The VBA (with which I'm more familiar) would be something like

    Private Sub txtSaleDate_AfterUpdate()
    If IsDate(Me!txtSaleDate) Then ' see if user entered a date
      If IsNull(Me!txtDeliveryDate) Then ' don't stomp on existing data
       Me!txtDeliveryDate = DateAdd("d", 14, Me!txtSaleDate)
      End If
    End If
    End Sub
    
    

    Tweak the fieldnames and expressions to match your own needs of course.

     


    John W. Vinson/MVP
    • Marked as answer by Bessie Zhao Wednesday, September 22, 2010 9:42 AM
    Wednesday, September 15, 2010 5:21 PM
  • Thanks for the code example, John!  I'll try it out today.

    Sharon G.

    Thursday, September 16, 2010 12:30 PM
  • John,

    I have a quick, easy question for you.  The filed names in your example, are they the actual name of the fields or the control source?

    Sharon G.

    Friday, September 17, 2010 1:22 PM
  • cyber..

    They can be either*, but using the controlnames is typical.

    *(so long as Access can recognise the names at runtime)

     

    • Marked as answer by Bessie Zhao Wednesday, September 22, 2010 9:42 AM
    Friday, September 17, 2010 4:48 PM