Audit code check

G

Guest

I am using some downloaded code that logs all changes to a database. 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
 
D

Douglas J. Steele

If .ControlType = acTextBox Then
If Nz(.Value, "") <> Nz(.OldValue, "") Then
varbefore = Nz(.OldValue, "Null")
Varafter = Nz(.Value, "Null")
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

That will show either beforevalue or aftervalue as the string Null. If you
want an actual Null value to be stored rather than that string, you'll need
to change the SQL statements.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top