I thought I had this solved as the code was working on my test scenario. However, my test scenario was on just a solo form. My application has a subform and I can't get this audit trail to work. Any suggestions?
Code:
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 " & Now() & " by " & Environ("USERNAME") & ";"
'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 """
MyForm.Lastupdated = Now()
MyForm.LastUpdatedBy = Environ("USERNAME")
Exit Function
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" And C.Name <> "LastUpdated" Then
' ' If control was previously Null, record "previous
' value was blank."
If (Len(C.OldValue & vbNullString) = 0) And (Len(C.Value & vbNullString) > 0) Then
MyForm!Updates = MyForm!Updates & Chr(13) & _
Chr(10) & C.Name & "--previous value was blank"
' If control had previous value, record previous value.
ElseIf IIf(IsNull(C.Value), "", 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:
MyForm.Lastupdated = Now()
MyForm.LastUpdatedBy = Environ("USERNAME")
Exit Function
Err_Handler:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
End If
Resume TryNextC
End Function