Recipe 6.1 Save with Each Record the Name of the Last Person Who Edited It and the Date and Time

6.1.1 Problem

Your application is used in a multiuser environment with users regularly adding and editing records. Access keeps track of when an object was created and last modified. However, it does not track this information at the record level. With each record, you want to log who created the record, who last edited the record, and the date and time associated with each of these actions. Is this possible?

6.1.2 Solution

Access has no built-in feature that records who edited a record and when the edit was made, but it's fairly easy to create your own. You'll need to add four fields to each of your tables to hold this information. You'll also need to create two simple procedures and attach them to the BeforeInsert and BeforeUpdate events of your forms.

To add this functionality to your applications, follow these steps:

Table 6-1. New fields for tblCustomer

Field name	Field type	Default value
DateCreated	Date/Time	=Now( )
UserCreated	Text (20)
DateModified	Date/Time	=Now( )
UserModified	Text (20)

Table 6-2. New controls for frmCustomer1

Control name	Control source
txtDateCreated	DateCreated
txtUserCreated	UserCreated
txtDateModified	DateModified
txtUserModified	UserModified

Figure 6-1. Referencing the event procedures for frmCustomer1

To see an example, load the frmCustomer1 form from 06-01.MDB. This form, shown in Figure 6-2, allows you to enter and edit data in the tblCustomer table. Make a change to an existing record, and the DateModified and UserModified fields will be updated with the current date and time and username. Add a new record, and the DateCreated and UserCreated fields will be updated.

Figure 6-2. The frmCustomer1 form

6.1.3 Discussion

To keep track of the username and the date and time a record is created and updated, you must do two things:

• Create additional fields in the table to hold the information.

• Create the application code to ensure that these fields are properly updated when a record is added or modified.

We added four fields to tblCustomer: two fields to hold the username and date/time the record was created, and another two fields to hold the username and date/time the record was last modified. You don't have to create all four fields, only the fields for which you wish to log information.

We also created event procedures to update these columns whenever a record is inserted or updated. The Now function supplies the date and time; if you'd prefer to record only the date of the change without a time, you can use the Date function instead. The built-in CurrentUser function saves the name of the current user.

Access doesn't support the specification of calculated fields at the table level, so all of the logic presented in this solution occurs at the form level. This means that you must recreate this logic for every form that updates the data in this table. It also means that if you add new records or update existing records outside of a form—perhaps by using an update query or by importing records from another database—the fields in Table 6-1 will not all be automatically updated.

You can ensure that one of the fields, DateCreated, is correctly updated for every record by adding the following expression to its DefaultValue property:

=Now( )

Unfortunately, you can't use the DefaultValue property for either of the updated fields, because DefaultValue is evaluated only when the record is initially created. You can't use this property to update the UserCreated field, either, because DefaultValue cannot call built-in or user-defined functions (except for the special Now and Date functions).

You may have noticed that placing the four controls from Table 6-2 on the form takes up a considerable amount of screen space. Fortunately, you don't need controls to make this technique work, because Access lets you refer to a form's record-source fields directly. In the sample database you'll find a second version of the form, frmCustomer2, that demonstrates this variation of the technique. Notice that there are no txtDateCreated, txtUserCreated, txtDateModified, or txtUserModified controls on frmCustomer2, yet when you enter or edit a record using this form, the fields in tblCustomer are correctly updated. Here's the BeforeUpdate event procedure for this form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.DateModified = Now( )
    Me.UserModified = CurrentUser( )
End Sub

Access lets you refer to fields in a form's underlying record source (in this example, the DateModified and UserModified fields in tblCustomer) as if they were controls on the form, even though they're not. Because of this, it's a good idea to name the controls on a form differently from the underlying fields. Then you can be sure that you are always referring to the correct object.

Another consideration is that the CurrentUser function is useful only if you have implemented user-level security on your database. In an unsecured Access database it will always return "Admin", which is not very informative. In that case, you can use Windows API calls to retrieve either the computer name or the network login (or both) of the current user, instead of the Access security account. In the sample application, frmCustomer3 calls acbNetworkUserName when a record is inserted or edited. Here are the API declaration and the function, which you can find in basNetworkID:

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

Function acbNetworkUserName( ) 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 = GetUserName(strUserName, lngLen)
    If lngX <> 0 Then
        acbNetworkUserName = Left$(strUserName, lngLen - 1)
    Else
        acbNetworkUserName = ""
    End If
End Function

The basNetworkID module also includes the following API call, which you can use to obtain the name of the current user's computer:

Private Declare Function GetComputerName _
 Lib "kernel32" Alias "GetComputerNameA" _
 (ByVal lpBuffer As String, nSize As Long) As Long

Private Const acbcMaxComputerName = 15

Public Function acbComputerName( ) As String
    ' Retrieve the name of the computer.
    Dim strBuffer As String
    Dim lngLen As Long
    
    strBuffer = Space(acbcMaxComputerName + 1)
    lngLen = Len(strBuffer)
    If CBool(GetComputerName(strBuffer, lngLen)) Then
        acbComputerName = Left$(strBuffer, lngLen)
    Else
        acbComputerName = ""
    End If
End Function

Another option is to create your own public function called CurrentUser that returns the network name. That way, you won't need to change any of the code that calls CurrentUser in your forms. Access will use your function rather than the built-in one, and if you do implement Access security, all you need to do is rename or remove the custom CurrentUser function to have the form code start retrieving Access security names using the built-in CurrentUser function.

6.1.4 See Also

For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.