Banking

Timestamp new Access records

A simple Access field property can add the date and time for each new record created.
Auditing records is as complex or simple as the internal process requires. However, most auditing processes have at least one thing in common -- they all want to know when each record was created. Fortunately, Access can automate this requirement without a bit of VBA code. If you need to add a Date Created field to a table, do so as follows:

  1. Open the table in Design view.
  2. Insert a new record row and give the field a meaningful name, such as DateCreated.
  3. Click the Data Type column's drop-down arrow and choose Date/Time.
  4. With that record row still selected, find the Default Value property field and enter the Now() function. This will include both the date and time. If you prefer just the date, use the Date() function.
  5. Save the table and close it.

When you add a record, Access will automatically insert the date (and time). However, as is, a user could overwrite the default value. If using this date for auditing purposes, consider limiting access to this field so users can't change the timestamp. In fact, you don’t need to display this value in forms and reports, other than those used specifically for auditing purposes. Those entering data don't need to know that Access is timestamping the record.

Setting a default value is best done when you create the table because this property works only with new records. If the table contains preexisting data, you must manually update those records. In this case, you might not know the appropriate dates, so be sure to accommodate null values in your queries and functions if you leave this field blank for preexisting records.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

8 comments
alan
alan

To further enhance record logging one can add another field to the table to record the time and date when a record was modified. Using the 'dirty' event or simple VBA will do this for you.

carbondog
carbondog

This works great! How do you capture the user who created the record? I've used Environ("username") previously but now A2007 doesn't like that (at least, without changing the registry or signing the code) and won;t let me use UDF as default values in a table...

hisb79
hisb79

I also use a date_modified field and add this code to the form: Private Sub Form_BeforeUpdate(Cancel As Integer) Me!date_modified = Now() End Sub

tballky
tballky

Could be wise to use the OnChange event to update records with last user and timestamp only when a value in the record is actually changed.

JSmotherman
JSmotherman

I found some code at Allen Browne's website that captures the Windows login ID, and I use that as the name of the user who created or modified a record in my audit process. Look at http://allenbrowne.com/AppAuditCode.html specifically for the apiGetUserName and NetworkUserName() function declarations. HTH.

dogknees
dogknees

This is one I always struggle with. Specifically, if I don't change anything on the form, then hit Save, it shouldn't change the Modified date as the data was not modified. Same as overtyping a field with the same value isn't a change and the Change events should not fire. I also have trouble implementing an effective Undo option. How do we roll back the Modified date when the user hits undo? I'm not really in favour of the warning "You won't be able to undo this" as it breaks the metaphor. Any ideas or pointers would be appreciated.

ssharkins
ssharkins

Thanks for sharing that code!

Glenn from Iowa
Glenn from Iowa

I prefer teaching users about the record selector rather than putting a Save button on a form, but as far as I can tell, running the DoCmd.SaveRecord method still does not run the Before_Update event unless something has changed. If you find differently (perhaps a different method of saving the record or a different version of Access), you can surround the main line of code with an If statement that will keep it from updating the date unless something has actually been changed: Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.Dirty Then Me!date_modified = Now() End If End Sub I'm kind of in favor of still changing the date modified if the user overtypes the data with identical data, but if you really want to do it, you could use this code: '************************************************* Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.Dirty and VerifyDirty Then Me!date_modified = Now() End If End Sub Function VerifyDirty() As Boolean Dim ctlC As Control ' For each control. For Each ctlC In Me.Controls If SupportsOldValue(ctlC) Then If ctlC.Value ctlC.OldValue Then VerifyDirty = True Exit For End If End If Next ctlC End Function Function SupportsOldValue(ByRef ctlCurr As Control) _ As Boolean Select Case ctlCurr.ControlType Case acOptionButton, acCheckBox, _ acOptionGroup, acTextBox, _ acListBox, acComboBox, _ acToggleButton: ' Changeable control types SupportsOldValue = True Case acBoundObjectFrame, acSubform, _ acObjectFrame, acCustomControl, _ acTabCtl, acPage, _ acAttachment: ' Need to have special code for SupportsOldValue = False Case acLabel, acRectangle, _ acLine, acImage, acCommandButton, _ acPageBreak: ' Unable to be changed SupportsOldValue = False End Select End Function '************************************************* You don't actually need the last two Case statements in the SupportsOldValue function, but I thought I'd include them in case you really wanted to do something with the second group. I see what you mean about the undo option. It almost seems like a bug, since the Undo button on the Quick Access toolbar (in 2007) says "Undo 0 actions," but it still lets the user press Ctrl-Z, and then runs the BeforeUpdate event, changing the time. You'd almost have to keep track of the recent changes made and compare those during the BeforeUpdate event, but that's really not a good option. Let's just say if you need that stringent of auditing requirements, you shouldn't be using Access! Here are the reference pages for more info for Office 2007: .Dirty property - http://msdn.microsoft.com/en-us/library/bb240721.aspx Dirty Event - http://msdn.microsoft.com/en-us/library/bb214796.aspx .OldValue property - http://msdn.microsoft.com/en-us/library/bb225221.aspx

Editor's Picks