Need small help with VBA code

[SKV]

Hi,

I have copied this code from the link below to create an audit trail
Link : http://www.techrepublic.com/article/a-simple-solution-for-tracking-changes-to-access-data/6166807

However on pasting this code, I am getting some syntax error which being very new to VBA I am not able to figure it out. I am pasting the formatted code below please let me know what am I missing in the code to work.
The ERROR is coming at INSERT INTO statement onwards.

Thanks in advance for your help

Const cDQ As String = """"

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"

 

[Kreszch68]

Hi, the next code will work

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 strSQL As String
Dim aValues(5) As String
Dim sValues As String
Const cUser As Integer = 0
Const cRecordID As Integer = 1
Const cSourceTable As Integer = 2
Const cSourceField As Integer = 3
Const cBeforeValue As Integer = 4
Const cAfterValue As Integer = 5
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
                        
                            aValues(cUser) = Environ("username")
                            aValues(cRecordID) = RecordID.Value
                            aValues(cSourceTable) = frm.RecordSource
                            aValues(cSourceField) = .Name
                            aValues(cBeforeValue) = .OldValue
                            aValues(cAfterValue) = .Value
                            sValues = "'" & Join(aValues, "', '") & "'"
                    
                            'Build INSERT INTO statement.
                            strSQL = SQL_Audit(sValues)
                                '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 ctl
Set ctl = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub

And the function to create the SQL statement

Public Function SQL_Audit(sValues As String) As String
SQL_Audit = "INSERT INTO Audit " _
          & "(EditDate, User, RecordID, SourceTable, SourceField, BeforeValue, AfterValue) " _
          & "VALUES #Now()#, " & sValues
End Function

 

[SKV]

I pasted both your codes and it is not working. I mean don't see any data getting captured in the audit table. Please advice.

 

[Kreszch68]

Ok, just to make sure we are on the same page.

I assume you have a button on the form from where you trigger the code?
And in the click event of this button you have

AuditTrail Me, Me.(yourcontrol with the recordID)

Is the field EditDate in your Audit table a Date field or a Text field?

Do you see a result from the debug.print sSQL?