Audit Trail

[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

 

[Jeff Boyce]

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

 

[Guest]

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

 

[Jeff Boyce]

Nothing jumped out -- try a breakpoint and stepping through...

 

[Guest]

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

 

[Guest]

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

 

[Jeff Boyce]

The "breakpoint" I was referring to is something you'd set in the code
module.

 

[Jeff Boyce]

Sorry, don't know about that one...