Verifying Code format

[brharrii]

I got some code from a website that is instructing me how to create an audit log. I'm very new to using VBA and The code was lumped into one paragraph so I wanted to make sure I inserted carriage returns in the appropriate places. Would someone mind taking a quick look and letting me know if this looks correct?

Thanks
Bruce

Sub AuditTrail(frm As Form, recordid As Control)   'Track changes to data.   'recordid identifies the pk field's corresponding   'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler   'Get changed values.
For Each ctl In frm.Controls
With ctl     'Avoid labels and other controls with Value property.
If .ControlType = acTextBox Then
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 If
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:   MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub

 

[VilaRestal]

Looks fine to me. Although I would replace:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

with

CurrentDb.Execute strSQL

 

[brharrii]

Thanks VilaRestal,

What does that do? I'm trying to understand as much as I can as well to help reduce the number of questions I have to post on here

 

[VilaRestal]

It's a better way of executing the SQL string:
As you can see it doesn't require turning off warnings.
And if the SQL encounters errors then it will raise a VBA error so the user will be aware it hasn't worked. DoCmd.RunSQL would stay silent about any failings on the SQL side.

 

[pbaldy]

And if the SQL encounters errors then it will raise a VBA error so the user will be aware it hasn't worked.

I suspect you meant:

CurrentDb.Execute strSQL, dbFailOnError

else the above wouldn't happen.

 

[VilaRestal]

Indeed, I did mean that