Audit trail question

[jpkeller55]

I am using the following code obtained from http://support.microsoft.com/kb/197592 to create an audit trail. I would like to have the audit trail field that is created not print the statement "previous value was blank." for new records. The form I want to attach this module to has over 30 fields which would create a lot of pointless verbiage in this field. I think it should be obvious for a new record that all the fields were previously blank. All I would want to print in the audit trail is the "new record" portion. Can anybody help me with tweaking this code to accomplish this? Thanks!

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 " & Date & " by " & CurrentUser() & ";"
 
    '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 """
    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" 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

[DaveT]

The easiest approach is to simply rem out (put single apostrophe as first character of the line telling vba to ignore the line) the lines shown below:

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

[jpkeller55]

Thanks Dave. That works, but it also removes the previous value was blank for existing records that have a field updated from blank to some value. Is there a way to get the message to print for existing records when applicable but not the new records?

[jpkeller55]

Actually, I think I found the answer. I put an Exit Function command as shown below and that seems to work.

'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 """
         Exit Function
    End If

[DaveT]

Good. Presume you're using workgroup for 2003 so that CurrentUser means something.

MS removed workgroups from 2007, but I have used 2007 program against 2003 file format w/secure workgroups OK.

[jpkeller55]

Yes, that is correct. The currentUser is the persons network ID name.