Audit Trail

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Someone in this forum pointed me to the suggested code in
http://support.microsoft.com/?kbid=197592 for keeping an audit trail of
changes made to certain fields.

I've used the code, modified it to my forms, but keep getting the following
error:

Error#: 2447
Description: There is an invalid use of the .(dot) or ! operator or invalid
parentheses.

Has anyone tried out this code before? Thanks.
ck
 
CK

Without seeing the modifications you made, what comes to mind first is, as
the error message suggests, that you may have used punctuation in an invalid
manner.

Have you set a breakpoint and stepped through your code to find the exact
line of code that "breaks"?
 
Jeff,
I've posted my code here and the only thing I've changed is the field name
from Updates to Updated:

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!Updated = MyForm!Updated & 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!Updated = MyForm!Updated & 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 <> "Updated" Then

' If control was previously Null, record "previous
' value was blank."
If IsNull(C.OldValue) Or C.OldValue = "" Then
MyForm!Updated = MyForm!Updated & 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!Updated = MyForm!Updated & 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


Are you able to spot anything wrong? Thanks.
ck
 
Thanks Jeff. I did that and found the cause of it but boy, was it tedious to
step through each of the fields one by one, each time, removing a field and
then trying again.
ck
 
Jeff, I can't seem to get the audit trail code to work with a subform. Do you
know how to do it? Thanks.
ck
 
Back
Top