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

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

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
					

      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