Audit script will not apply a value when updating a blank field

[Kevin McLaren]

Hi
I have an auditing script that creates a before and after value to records updated via a form and places them into another table. This way changes to the database can be tracked.
It works fine, until a blank textbox field is updated to contain a value. So if the field being updated doesn't contain a value, it doesnt put the before and after values into the audit table.

Here is the code:

Sub AuditTrail(frm As Form, recordid As Control)
 
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
 
On Error Resume Next
 
'Get changed values.
For Each ctl In frm.Controls
 
With ctl
 
Select Case .ControlType
 
Case acTextBox
If .Value <> .OldValue Then
varBefore = Nz(ctl.OldValue, poo)
varAfter = Nz(ctl.Value, poo)
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
 
 
Case acCheckBox
 
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
 
End Select
 
End With
 
Next
 
Set ctl = Nothing
Exit Sub
 
End Sub
 

I have spent a considerable amount of time on this and would appreciate if anyone could help.
Thanks

[danp129]

Remove "On error resume next", what happens?

Set a break point and step through the code on an update that you know will cause the problem... What is the old/new value? What happens when it tries to compare the old/new value with and without "On error resume next"?

"On Error Resume Next" should almost never be used. When it is used, it should precede only the line of code that can cause an error that cannot otherwise be handled or you know the there is no possibility of it continuing causing an issue. After such error causing lines, add "On Error Goto 0".

At a bare minimum, I would have:

Sub MySub
On Error Goto MyErrorTrap:
' Your code below
x=1*"B"
' Your code above
exit sub
MyErrorTrap:
msgbox "There was an error"
resume next
end sub

Here's an example you can learn more from

Sub test()
    On Error GoTo ErrorTrap:
StartOver: ' Optional, used to show second example
' vvvv YOUR CODE HERE vvvv
 
 
    x = "A" * 1 ' Cause first error
 
    MsgBox "Continuing after first error"
 
    ' Cause another error
    x = "A" * 1
 
    MsgBox "Continuing after second error"
 
 
 
' ^^^^ YOUR CODE HERE ^^^^
 
 
    Exit Sub ' Your code is done, so exit the procedure
 
' Your error trap
ErrorTrap:
    ' Show the error if you want
    MsgBox "Runtime Error: '" & Err.Number & "'" & Replace(Space(2), " ", vbCrLf) & Err.Description, vbExclamation, "My Application Title"
 
    Dim ret As VbMsgBoxResult
 
    ' Ask if they'd like to continue
    ret = MsgBox("It appears an error has occured, would you like to attempt to continue the process with possibly unexpected results?", vbYesNo)
 
    ' Resume the code just like "On Error Resume Next" does if the user clicked Yes
    If ret = vbYes Then Resume Next
 
    ' User hit No on previous message box if we made it to here...
 
    ' Here's an example of resuming at a label to go do a different section of code
    ret = MsgBox("Would you like to start over from the beginning?")
    If ret = vbYes Then Resume StartOver:
    ' It did not have to start from beginning, just whatever was a safe place to resume
 
    ' User hit No on previous message box if we made it to here...
 
    MsgBox "You have said no on both questions, in some cases, quitters really do win!"
End Sub