Audit Tail to Track Multiple Fields

Question

I am using using an audit trail to track multiple fields that include textboxes and combo boxes. The audit trail is working perfectly in one database, but when I try to duplicate it in a different database it doesn't work so well.

In the one where it works I am using  . . .the following code with the "bolded" portion being the part used to track changes in all fields

'Get changed values.

For Each ctl In MyForm.Controls

With ctl

Select Case ctl.ControlTypeCase acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox'If .OldValue = "" Or IsNull(.OldValue) Then

'varBefore = "Empty"

'MsgBox ctl.Name

'End If

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

varBefore = .OldValue

varAfter = .Value

strControlName = .Name

'Build INSERT INTO statement.

strSQL = "INSERT INTO " _

& "AuditTrail (EditDate, User, RecordID, SourceTable, " _

& " SourceField, BeforeValue, AfterValue) " _

& "VALUES (Now()," _

& cDQ & Environ("username") & cDQ & ", " _

& cDQ & recordid.Value & cDQ & ", " _

& cDQ & MyForm.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

ErrHandler:

If Err.Number <> 3251 Then MsgBox Err.Description & vbNewLine & Err.Number, vbOKOnly, "Error Here"

Exit Sub

End Sub

When I use this same code in a different database it doesn't work . . .I get the error "You entered an expression that has no value 2427". Yet if i change the code to track textbx only it works perfectly. The problem is, the form has fields with textbx and combobx so I need it to track both.

Also, when I change a value in the combo box I get the code "Type mismatch 13" although it still records the change in the audit trail table.

Thanks in advance for any suggestions.

Answer 1

Hi nsdavis,

If the code works in one database but fails in the other, it appears the issue would be a datatype difference in your source table (the table you form is bound to) or to your audit trail table in which you are inserting the record into.

Does the code get to the point where you debug.print strSQL?

Regards,

Dennis

Answer 2

I found a code that works:

If .ControlType = acTextBox Or .ControlType = acComboBox Or .ControlType = acListBox Then

If ctl.Visible And ctl.Enabled And Not ctl.Locked Then

But I still get and error when I change a value in the combo box I get the code "Type mismatch 13" although it still records the change in the audit trail table. 

Does anyone know how to get this to stop popping up? Otherwise the code seems to work fine.

Thanks

Answer 3

I use a much simpler Audit Trail. Following is my Audit Trail procedure:

Public Sub AuditTrail(strTable As String, strField As String, lngRecord As Long, varOld As Variant, varNew As Variant)

Dim strSQL As String, strUser As String

If fIsLoaded("frmLogin") Then

strUser = Forms!frmLogin!cboUser

Else

strUser = fOSUserName()

End If

strSQL = "INSERT INTO tblAuditTrail (Tablename, Fieldname, RecordID, OldValue, NewValue, ChangeDate, ChangeBy) " & _

"VALUES('" & strTable & "', '" & strField & "', " & lngRecord & ", '" & varOld & "', '" & varNew & "', #" & _

Now() & "#, '" & strUser & "');"

CurrentDb.Execute strSQL

End Sub

I call this function in the Before Update event of any control I want to Audit using the code:

Call AuditTrail(Screen.ActiveForm.RecordSource, Screen.ActiveControl.ControlSource, Me.ChangeID, Screen.ActiveControl.OldValue, Screen.ActiveControl)

Me.ChangeID is the control holding the PK value for the record.

This allows me to be flexible at what values I want to audit or not.