Audit trail question #2

[jpkeller55]

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.

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

[pbaldy]

This test:

If IsNull(C.OldValue) Or C.OldValue = "" Then

only examines the old value, so every previously empty field will meet the test. You'd also want to test that the current value is not Null/"" (or that it's different than the old value).

[jpkeller55]

OK, I think I understand. But won't this cause some sort of trouble if the user updates a field with a value to a null value?

[pbaldy]

Wouldn't that be caught by the ElseIf?

[jpkeller55]

Yes, I guess you are right. So, take a look at this and tell me if this is what you were saying:

If IsNull(C.OldValue) Or C.OldValue = "" And Not IsNull(C.Value) And C.Value <> "" Then
                   MyForm!Updates = MyForm!Updates & Chr(13) & _
                   Chr(10) & C.Name & "--previous value was blank"

[pbaldy]

I'm a big believer in testing, but offhand I'd make sure the desired logic was maintained by adding parentheses:

If (IsNull(C.OldValue) Or C.OldValue = "") And (Not IsNull(C.Value) And C.Value <> "") Then

I would likely just use Nz() instead of the dual test:

If Nz(C.OldValue, "") = ""

Or

Len(C.OldValue & vbNullString)

which will be 0 for either Null or "".

[jpkeller55]

OK it worked when I put the parentheses in. Thanks! I am not sure about the second AND in this statement. Should it be AND or OR? I tried it both ways and it seemed to work which is now confusing me.

If (IsNull(C.OldValue) Or C.OldValue = "") And (Not IsNull(C.Value) And C.Value <> "") Then

[pbaldy]

You want And, because of the Not (value not Null AND not ""). Basically anything would pass the Or test, as everything is either not Null or not "" (since Null is not ""). Personally I'd use one of the other tests, which to me are easier to understand.

[jpkeller55]

Thanks Paul. I tried this approach per your suggestion:

If (Len(C.OldValue & vbNullString) = 0) And (Len(C.Value & vbNullString) > 0) Then

This is working well too and I think this is what I am going with. Thanks again for your help and teaching.

[pbaldy]

Glad we found a working solution for you.