Anticlue

Tracking Changes in Access 2000

When working with data, one of the main questions that always arise is who updated it last and when. Here is a trick I use to do it in Access databases.

First, I add the fields to the table I want to track on DateModified and WhoModified. Then, I add the fields to the query that runs the data entry form.

In my data entry form, frmEntry, I add the following code to save when the save button is clicked.

Forms![frmEdit]![txtDateModified] = Now()
Forms![frmEdit]![txtWhoModified] = OSUserName()

Next, I add the module for the function OSUserName() and use the following code, which was found at the Access Web Site.

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function OSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
OSUserName = Left$(strUserName, lngLen - 1)
Else
OSUserName = vbNullString
End If
End Function

Finally in the form control, I turn off the tab stop and set the default value for the fields to be:
txtDateModified = Now()
txtWhoModified = OSUserName()
so that adding new records already has a value to show to the user that they are updating it.