Display date record saved/modified

Question

I want to have a field on my table in Microsoft Access.  where every time I save a record it automatically puts in a date that the record was modified/saved.  (I am using Microsoft Access 2013

I would appreciate your help.

Answer 1

Add a date/time field to your table; let's say you name it LastModified.

Open the form used to enter/edit records in the table in design view. (If you don't have such a form yet, create it.

Add a text box bound to the LastModified field to the form. If you wish, you can hide it by setting its Visible property to No.

Create a Before Update event procedure for the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me.LastModified = Now

End Sub

Now will store the date and time in the LastModified field; if you only want the date, use Date instead of Now.

Answer 2

You might like to take a look at ChangedRecordDemo.zip in my public databases folder at:

https://onedrive.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.

This little demo file illustrates means of both date/time-stamping changes to a row in a table, or logging changes.  In the latter case the first entry in the log for each row is the name of the user who inserted the row, and the date inserted.  The key thing about this file, however, is that it only stamps/logs actual changes to values in the row, not merely updates.  A row can be updated without any change to the values in the columns.

Answer 3

One example would be:

Create 4 fields in your table:

CreationDate

CreationUserId

ModificationDate

ModificationUserId

Then you need to create the following events:

Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.CreationDate = Now

    Me.CreationUserId = fOSUserName

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me.ModificationDate = Now

    Me.ModificationUserId = fOSUserName

End Sub

Where fOSUserName comes from: http://access.mvps.org/access/api/api0008.htm

If you display these fields on your form, make sure they can't be edited by your users so no one can mess around with you tracking data.