Access Default Value =NOW() form field does not update with current time

Question

I recently added an access app to my site through Access 2013.

I created two fields in a table to represent the date (only) and the second field to represent Time (only).  These were set with the default value of =NOW() and the date and time subtype.

When I launched the app, the first record I added upon opening the database contained an appropriate date and time.  However, as I continued to add records, even after several minutes of no data entry, then clicking on the "add" button I added a new record but it had the same time as my first record.

When I exited and re-opened the app, the time updates appropriately but only on one record.

This condition also persisted when data was entered from the Access 2013 program on my desktop.

How can I get the default value of =NOW() to update with each record rather than only upon entry into the database?

Answer 1

But it is very important to understand that the DefaultValue property is evaluated and applied when the first character is entered on a new record.

That's not quite the case, Marshall.  When a row is inserted via a bound form, if the form is in single form view  the DefaultValue property assigns a value to the bound control when the form is moved to an empty new record; in the case of a form in continuous forms view or datasheet view the value is assigned when the form is opened regardless of which row focus is set to, and does not change when the user moves to an empty new row without undertaking any other updates to existing rows.

Because of this it is not adequate to rely solely on the DefaultValue property to assign a date/time stamp value.  It should also be assigned either in the form's BeforeInsert event procedure, to record when the user begins to insert data, or in the BeforeUpdate event procedure, conditional on the NewRecord property being True, to record when the row is saved.  The former approach is adopted in my ChangedRecordDemo.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

While this file illustrates rather more than time-stamping the insertion of a row in a table, as far as that is concerned it provides a solution to what the OP is attempting, though like you, I'd draw his attention to the pointlessness of having two columns for the date/time value.

You can see the behaviour I describe above if you open the Contacts table in my demo directly in datasheet view and wait a few moments before inserting a new row; the date/time value doesn't change.  If you open the frmChangedRecord form on the other hand the default value shows in the control when you move the form to a new record, but as soon as you start to enter data it changes to the current time by virtue of the form's BeforeInsert event procedure:

Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.DateTimeStamp = Now()

End Sub

Answer 2

I'm not sure what you are describing as the problem.  But it is very important to understand that the DefaultValue property is evaluated and applied when the first character is entered on a new record.  It has no effect on existing records.

Could you please describe what your "add" button is doing?

Another thing is that a Date/Time field contains both the date and the time.  It is stored as a double precision floating point number with the integer part the number of days since 1899-12-30 and the time part as the fraction of a day.  There is almost no justifiable reason to use separate date and time fields.