You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q183792
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.
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY
This article describes how you can create a procedure in Visual Basic forApplications that keeps an audit trail of the changes that are made toa 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 ExplicitDim MyForm As Form, C As Control, X As IntegerDim 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 SelectTryNextC:    Next CEnd 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 theMicrosoft Access 97 Office Assistant.
Tracking Information inf
Properties

Article ID: 183792 - Last Review: 01/22/2007 17:54:22 - Revision: 4.3

  • Microsoft Access 97 Standard Edition
  • kbhowto kbprogramming KB183792
Feedback