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:

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:

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.

 
Home | About |
Links |

Contact | Excel |
Access