Audit Trail works.......Partialy.

[RetiringSoon]

I found coding for an audit trail in Microsoft, and with slight modifications it works but for one problem. It looks at EVERY field on the form and if it blank, that is outut to the Updates field even if NO changes were made.

Here is my coding:

Function AuditTrail()
On Error GoTo Err_Handler
    
    Dim MyForm As Form
    Dim C As Control
    Dim xName As String
    
    Set MyForm = Screen.ActiveForm

    'If new record, record it in audit trail and exit sub.
    If MyForm.NewRecord = True Then
        MyForm!Updates = MyForm!Updates & "New Record !" & Chr(13) & Chr(10) & _
        "Created: " & Date & " at " & Time() & Chr(13) & Chr(10) & _
        "By " & GetName("1") & " on " & GetName("2") & Chr(13) & Chr(10) & _
        "____________________________"
        GoTo TryNextC
    End If

'Set date and current user if form has been updated.
    MyForm!Updates = MyForm!Updates & "Changed Record !" & Chr(13) & Chr(10) & _
        "Changed: " & Date & " at " & Time() & Chr(13) & Chr(10) & _
        "By " & GetName("1") & "   on " & GetName("2") & Chr(13) & Chr(10)
    
    '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 value was BLANK."
                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 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:
    Exit Function
      
Err_Handler:
    If Err.Number <> 64535 Then
        MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
    End If
    Resume TryNextC

End Function

And the output is:
-------------------------------------------------
Changed Record !
Changed: 2/22/2012 at 12:14:05 PM
By jesseh01 on A1B2C3

PdaNum: Previous Value was BLANK
ECworkNum: Previous Value was BLANK
Suffix: Previous Value was BLANK
MotherName: Previous Value was => C
FatherName: Previous Value was BLANK
MotherNationality: Previous Value was => D
FatherNationality: Previous Value was BLANK
OtherNum: Previous Value was BLANK
Legal City: Previous Value was BLANK
Legal State: Previous Value was BLANK
Legal Zip: Previous Value was BLANK


The form has "Many" more field, but I have pulled a sample for you to see. What am trying to do?

If the control did not change, go to the next control.
If the control changed, make the change and log it.


Can someone help me out?

-------------------------------------------------------

 

[JonFer]

I think I would replace the conditional logic with a single check for a change between OldValue and Value properties:

' Skip Updates field.
If C.Name <> "Updates" Then

' If control OldValue <> current value, show old/new values after field name
If nz(C.Value, "") <> nz(C.OldValue,"") Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
C.Name & ": " & iif(nz(C.OldValue,"")="","<blank>",C.OldValue) & " / " & iif(nz(C.Value,"")="","<blank>",C.Value)
End If
End If

...