How to create an audit trail of record changes in a form in Access 2000

Article translations Article translations
Article ID: 197592 - View products that this article applies to.
This article was previously published under Q197592
For a Microsoft Access 97 version of this article, see 183792.
Moderate: Requires basic macro, coding, and interoperability skills.

Expand all | Collapse all


This article shows you how to create a procedure in Microsoft Visual Basic for Applications that will keep an audit trail of the changes that are made to a record in a form.


Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To create an audit trail of changes to a form, follow these steps:
  1. Open the sample database Northwind.mdb.
  2. Create a module and type the following line in the Declarations section if it is not already there:
    Option Explicit
  3. Type the following procedure:
    Function AuditTrail()
    On Error GoTo Err_Handler
        Dim MyForm As Form, C As Control, xName As String
        Set MyForm = Screen.ActiveForm
        'Set date and current user if form has been updated.
        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
        "Changes made on " & Date & " by " & CurrentUser() & ";"
        'If new record, record it in audit trail and exit sub.
        If MyForm.NewRecord = True Then
            MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
            "New Record """
        End If
        'Check each data entry control for change and record
        'old value of Control.
        For Each C In MyForm.Controls
        'Only check data entry type controls.
        Select Case C.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup
                ' Skip Updates field.
                If C.Name <> "Updates" Then
                ' If control was previously Null, record "previous
                ' value was blank."
                    If IsNull(C.OldValue) Or C.OldValue = "" Then
                        MyForm!Updates = MyForm!Updates & Chr(13) & _
                        Chr(10) & C.Name & "--previous value was blank"
                    ' If control had previous value, record previous value.
                    ElseIf IIF(IsNull(C.Value),"",C.Value) <> C.OldValue Then
                        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                        C.Name & "==previous value was " & C.OldValue
                    End If
                End If
            End Select
        Next C
        Exit Function
        If Err.Number <> 64535 Then
            MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
        End If
        Resume TryNextC
    End Function
  4. Save the Module as Module1 and close the Visual Basic Editor.
  5. Open the Customers table in Design view and add a new field called Updates. Set the data type of the field to Memo. Close and save the table.
  6. Open the Customers form in Design view.
  7. In the BeforeUpdate event of the form, type =AuditTrail().
  8. If the field list is not displayed, click Field List on the View menu and drag the Updates field from the field list to the form.
  9. Open the form in Form view, make a change to the Company Name field of the current record, and press SHIFT+ENTER to save the record.
Note that the Updates field has an entry showing the change that you made to the Company Name field. You can also hide the Updates field if you do not want to see it on the form.


Article ID: 197592 - Last Review: October 11, 2006 - Revision: 5.1
  • Microsoft Access 2000 Standard Edition
kbhowto kbprogramming KB197592

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from