Log changes code

[Guest]

This is a repost from 3 days ago.

I am using some downloaded code that logs all changes to a table made on a
form. It works great except it does not log changes when I am changing the
field to a null or from a null. It ignores null changes. Can anyone help me
revise
this code so it will also keep null changes? Below is the code.

Thanks in advance

Sub AuditTrail(frm As Form, recordid As Control)
'track Changes to data
'recordid identifies the pk fields corresponding
'control in form, 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
Else
End If
Else
End If
End With
Next
Set ctl = Nothing

For Each ctl In frm.Controls
With ctl
'avoid labels and other controls with value property
If .ControlType = acComboBox 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
Else
End If
Else

 

[Douglas J. Steele]

Change

If .Value <> .OldValue Then

to

If Nz(.Value, "Null") <> Nz(.OldValue, "Null") Then

If you're content having the word Null appear as the old or new value in
your audit table, also change

varbefore = .OldValue
Varafter = .Value

to

varbefore = Nz(.OldValue, "Null")
Varafter = Nz(.Value, "Null")

Otherwise, you'll need to make changes to the SQL statement along the lines
of:

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 & ", " _
& IIf(IsNull(varbefore), Null, cDQ & varbefore & cDQ) & ", " _
& IIf(IsNull(Varafter), Null, cDQ & Varafter & cDQ) & ")"

 

[Guest]

Thanks Doug. workrd beautifully.

--
Chris

Change

If .Value <> .OldValue Then

to

If Nz(.Value, "Null") <> Nz(.OldValue, "Null") Then

If you're content having the word Null appear as the old or new value in
your audit table, also change

varbefore = .OldValue
Varafter = .Value

to

varbefore = Nz(.OldValue, "Null")
Varafter = Nz(.Value, "Null")

Otherwise, you'll need to make changes to the SQL statement along the lines
of:

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 & ", " _
& IIf(IsNull(varbefore), Null, cDQ & varbefore & cDQ) & ", " _
& IIf(IsNull(Varafter), Null, cDQ & Varafter & cDQ) & ")"