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.
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.
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 ExplicitDim MyForm As Form, C As Control, X As IntegerDim 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 SelectTryNextC: Next CEnd 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.
Open the Customers form in Design view.
Click Field List on the View menu, and then drag the Updates field from the field list to the form.
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.
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.