Audit Trails - Only show changes

[jcpender]

I've been looking through all of the related threads for audit trails, but I haven't come across my situation. I'm only looking to capture the previous value of fields that have been changed. I don't need to know that every other field on a record hasn't been modified. I've applied the following code from MS, but I can't seem to tweak it to only show the modified info. Any suggestions?

Option Explicit

Function AuditTrailNew()
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!Updates2 = MyForm!Updates2 & Chr(13) & Chr(10) & _
"Changes made on " & Date & " " & Time & " by " & CurrentUser() & ";"

'If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
MyForm!Updates2 = MyForm!Updates2 & 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 Updates2 field.
If C.name <> "Updates2" Then

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

 

[RuralGuy]

I've used Roger's AuditTrail code for years with great success. Maybe that is what you are looking for. He has two examples on this link under AuditTrail.

 

[jcpender]

Hey Rural Guy, thanks for the links. I looked at them, but they're a little more involved at than I was looking for. After tinkering with this for a while, I was finally able to figure it out .

I changed the following:

' If control had previous value, record previous value.
ElseIf IIf(IsNull(C.Value), "", C.Value) <> C.OldValue Then
MyForm!Updates2 = MyForm!Updates2 & Chr(13) & Chr(10) & _
C.name & "==previous value was " & C.OldValue

and changed it to:

' If control had previous value, record previous value.
If C.Value <> C.OldValue Then
MyForm!Updates2 = MyForm!Updates2 & Chr(13) & Chr(10) & _
C.name & "==previous value was " & C.OldValue

 

[RuralGuy]

Glad you got it sorted. Thanks for posting back.