How to Create an Audit Trail of Record Changes in a Form

MORE INFORMATION

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. In Microsoft Access 97, open the sample database Northwind.mdb.
2. Open the Customers form in Design view.
3. Add the following code to the "General Declarations" section of the form:

   Option Explicit
   Dim MyForm As Form, C As Control, X As Integer
   Dim MyArray() As Variant
   					

4. Add the following code to the On_Current Event of the form:

   Private Sub Form_Current ()
       Set MyForm = Me.Form
       ReDim MyArray(MyForm.Controls.Count - 1)
       On Err GoTo TryNextC
       X = -1
       For Each C In MyForm.Controls
           X = X + 1
           Select Case C.ControlType
               Case acTextBox, acComboBox, acListBox, acOptionGroup 'Skip Updates field.
                   If C.Name = "Updates" Then GoTo TryNextC
                   MyArray(X) = C.Value
           End Select
   TryNextC:
       Next C
   End Sub
   					

5. Add the following code to the Before_Update Event of the form, and then save the form:

         Private Sub Form_BeforeUpdate (Cancel As Integer)
   
         Dim MyForm As Form, C As Control
         Set MyForm = Screen.ActiveForm
         On Err GoTo TryNextC
         ' 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 """
            Exit Sub
         End If
   
         ' Check each data entry control for change and record
         ' old value of Control.
         'Set the Array Counter      
            X = -1       
            For Each C In MyForm.Controls
   
                 ' Only check data entry type controls.
            X = X + 1         
            Select Case C.ControlType
   
               Case acTextBox, acComboBox, acListBox, acOptionGroup
                  ' Skip Updates field.
                  If C.Name = "Updates" Then GoTo TryNextC
   
                  ' If control was previously Null, record "previous
                  ' value was blank."
                  If IsNull(MyArray(X)) Then
                     MyForm!Updates = MyForm!Updates & Chr(13) & _
                        Chr(10) & C.Name & "--previous value was blank"
                  ' If control had previous value, record previous value.
                  ElseIf C.Value <> MyArray(X) Then
                     MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                        C.Name & "==previous value was " &MyArray(X)
                  End If
            End Select
         TryNextC:
            Next C
         End Sub
   					

6. Open the Customers table in Design view, and then add a new field that is called Updates. Set the data type of the field to Memo. Close and then save the table.
7. Open the Customers form in Design view.
8. Click Field List on the View menu, and then 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 then press SHIFT+ENTER to save the record.

NOTE: The Updates field has an entry that shows the change that you made to the Company Name field. You can also hide the Updates field if you do not want to see the Updates field on the form.