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

ID: Q183792


The information in this article applies to:


SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you how to create a procedure in Visual Basic for Applications that will keep 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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
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. 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:


  4. 
          Function AuditTrail()
    
          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 Function
          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 GoTo TryNextC
    
                   ' If control was previously Null, record "previous
                   ' value was blank."
                   If IsNull(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 C.Value <> C.OldValue Then
                      MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                         C.Name & "==previous value was " & C.OldValue
                   End If
             End Select
          TryNextC:
             Next C
          End Function 
  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()" (without the quotation marks).


  8. 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 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.


REFERENCES

For more information about creating and running custom Visual Basic functions, search the Help Index for "Custom Functions," or ask the Microsoft Access 97 Office Assistant.

Additional query words: Tracking Information


Keywords          : kbdta AccCon PgmHowto KbVBA 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 6, 1999