Microsoft KB Archive/183792

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

Article ID: 183792

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q183792



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



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:  In Microsoft Access 97, open the sample database Northwind.mdb. Open the Customers form in Design view.  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   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   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  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.</li> Open the Customers form in Design view.</li> Click Field List on the View menu, and then drag the Updates field from the field list to the form.</li> 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.</li></ol>

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.

<div class="references_section">