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

Article translations Article translations
Article ID: 183792 - View products that this article applies to.
This article was previously published under Q183792
Moderate: Requires basic macro, coding, and interoperability skills.

Expand all | Collapse all

SUMMARY

This article describes how you can create a procedure in Visual Basic for Applications that keeps an audit trail of the changes that are made to a record 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.

REFERENCES

For more information about how you can create and how you can run custom Visual Basic functions, search the Help Index for "Custom Functions", or ask the Microsoft Access 97 Office Assistant.

Properties

Article ID: 183792 - Last Review: January 22, 2007 - Revision: 4.3
APPLIES TO
  • Microsoft Access 97 Standard Edition
Keywords: 
kbhowto kbprogramming KB183792
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

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