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

[Connie Gerregano]

I have used the following code from Microsoft Knowledge
base Article - 197592 successfully to record changes to
data on a form using the BeforeUpdate event and putting a
textbox called Updates on the form bound to the field
Updates in the table.

My problem is that it works well on the main form, but
does not seem to work on a subform even though I have
added the Updates field to the table the subform is bound
to and have an Updates textbox on the subform. Is there
some way I can make this work on subforms as well as the
main form?

Function AuditTrail()
On Error GoTo Err_Handler

Dim MyForm As Form, C As Control, xName As String
Set MyForm = Screen.ActiveForm

'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 """
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

' If control was previously Null, record "previous
If IsNull(C.OldValue) Or 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 If
End Select
Next C

TryNextC:
Exit Function

Err_Handler:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf
& "Description: " & Err.Description
End If
Resume TryNextC
End Function

 

[Allen Browne]

Change the AuditTrail() function so it accepts a reference to the form,
instead of relying on Screen.ActiveForm. That is:
Function AuditTrail(MyForm As Form)

The call it (step 7 of the k.b. article), by setting the form's Before
Update property to exactly this:
=AuditTrail([Form])

If you need to audit deletions as well, see:
Audit Trail - Log changes at the record level
at:
http://members.iinet.net.au/~allenbrowne/AppAudit.html