DataWright Information Services

Consulting and Resources for Excel and Access

Tracking data changes in Access

There are situations where you want to be able to track changes to records in a multi-user database. This page contains articles covering two options: storing when a record was changed and by whom; and storing the old value of a field before it changed. The first option only keeps a record of the last change, and won't tell you what was changed on the form. The second will give you an audit trail, and you can track the full history of changes for any field.

Simple tracking: Recording the last change made to a record

If you are only interested in knowing who created the record, and who changed it, you can do it by adding four fields to the table that underlies the form (and, if the form is based on a query, adding the new fields to the query). The fields are:

Field Data Type Comment
CreateBy Text
CreateDate DateTime Default value =Now()
ModBy Text
ModDate DateTime  

To use the fields, you need to add code to the Current and BeforeUpdate events of the form, as shown below:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ModBy = Environ("username")
    ModDate = Now()
End Sub

Private Sub Form_Current()
    If Me.NewRecord Then
        CreateBy = Environ("username")
    End If
End Sub

When you go to a record, the Current event fires. You can check to see if it is a new record and, if so, write the current logged-in user's name to the CreateBy field. CreateDate is filled automatically by setting its default value.

When you save a record, one of the events that fires is BeforeUpdate. This is just before the record is actually saved, so you can write the current user's name and the timestamp to the two Mod fields.

Although this is simple to set up, you need to add the fields to every table / query whose changes you want to track. The second method takes a bit more explaining but centralises all of the logging to a custom table, and allows you to build reports covering the history.

Audit trail: Logging all changes to a field

Let's assume that you want to keep a full history of changes to a field, in case you want to restore a value at a later date. This situation requires that you keep multiple records (one for each change that you track) and because of that, it makes sense to create a custom table for the purpose. In this example the log table will be called ztblDataChanges, and it will have these fields:

Field Data Type Comment
LogID AutoNumber Primary Key
FormName Text
ControlName Text
FieldName Text  
RecordID Number (Long Integer)
UserName Text
OldValue Text
NewValue Text
TimeStamp DateTime Default value =Now()

Most of the fields are text, including the OldValue and NewValue fields. The reason for this is that text fields will accept numeric data, but number fields will not accept text. You can always convert back to the correct data type when you restore the value. The exception is the RecordID, and I have made that a Number because all of my tables use AutoNumber primary keys. If you use any text-based primary keys, make this field text too.

The code uses a custom function which needs to be put into a standard code module. The code is shown below:

Function LogChanges(lngID As Long, Optional strField As String = "")
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varOld As Variant
    Dim varNew As Variant
    Dim strFormName As String
    Dim strControlName As String
    
    varOld = Screen.ActiveControl.OldValue
    varNew = Screen.ActiveControl.Value
    strFormName = Screen.ActiveForm.Name
    strControlName = Screen.ActiveControl.Name
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset
    
    With rst
        .AddNew
        !FormName = strFormName
        !ControlName = strControlName
        If strField = "" Then
            !FieldName = strControlName
        Else
            !FieldName = strField
        End If
        !RecordID = lngID
        !UserName = Environ("username")
        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If
        !NewValue = CStr(varNew)
        .Update
    End With
    'clean up
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

Explaining the code

Of the seven fields that we populate in ztblDataChanges, only one has to be directly passed to the function; the ID or the current record. Everything else is retrieved automatically using Screen.ActiveControl, Screen.ActiveForm, or the Environ function. There is an option to provide the field name where it doesn't match the name of the control.

When populating the fields, there are two If statements to handle exceptions. The first one checks to see whether the second parameter has been provided. If not, the code assumes that the control and field names are the same (usually the case if you add a bound control to a form), and uses the control name to populate the field name. If not, the user-provided field name is used.

        If strField = "" Then
            !FieldName = strControlName
        Else
            !FieldName = strField
        End If

The second checks to see whether an OldValue existed. If not (eg. for a new record, or for an existing record where a field is being populated for the first time), this field is skipped to avoid errors.

        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If

Using the function

To call the function use either of the following syntax options. Assuming that the form's key field is CustomerID, and the field and control names match, use this syntax:

Private Sub Address1_BeforeUpdate(Cancel As Integer)
    Call LogChanges(CustomerID)
End Sub

If the field and control names do not match, use this instead:

Private Sub txtAddress1_BeforeUpdate(Cancel As Integer)
    Call LogChanges(CustomerID, "Address1")
End Sub

Potential uses for the logging data

Without going overboard you can track changes to critical fields on any form in your database. The information stored in the table can be used to report on a range of things such as:

  • Usage patterns -- who uses which forms
  • How often are particular forms used? If you want to rationalise your database, this can be useful information
  • Obviously, the stored data can be used to restore changed values if necessary

Another advantage of this method is that, once the table is created and the code has been placed in a module, you don't have to make structural changes to any other tables in order to track usage.