This is a follow up to my last post. I am using the following code to record an audit trail. One problem with this code is that any time an existing record is updated, the message that prints contains the statement "previous value was blank" for all data fields that are blank even if that field was not updated. Why is it doing this and can it be corrected? If a data field is blank and is not updated (i.e. remains blank after the record was updated) there should be no need to print the message that the previous value was blank. Thanks for any help.
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 " & Date & " 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()
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" 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:
MyForm.LastUpdated = Now()
Exit Function
Err_Handler:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
End If
Resume TryNextC
End Function