Error with AuditTable Module

[dixie617]

Having some trouble on a module and cannot figure out why it won’t work, the error I am getting is, if you have a chance could you take a look and see what I did wrong, it has been a while since I played with VBA:

The error: "Syntax error in query expression 'SELECT Issues.IssueID'. 3075"

This is what I did, I created a table called “AuditTable” with the following fields:

Field Data Type
EditRecordID AutoNumber
EditDate Date/Time
User Text
RecordID Text
SourceTable Text
SourceField Text
BeforeValue Text
AfterValue Text

Then created a module to in VBE that would work on a change to the values in the main issues form and calling it using the Before Update

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, IssuesID)
End Sub


The module is this:

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

Select Case .ControlType

Case acTextBox
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "AuditTable (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & CurrentUser() & 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

Case acComboBox

If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "AuditTable (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & CurrentUser() & 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 Select

End With

Next

Set ctl = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub

Thank you
Dix

 

[dhookom]

You have

Debug.Print strSQL

Why not share the results in the debug window?

Duane
Hook'D on Access
MS Access MVP

 

[dixie617]

it is not giving me the option with this error, not sure why. All it gives me is the 'OK' button.

Thanks
Dix

 

[dhookom]

You aren't being of much help. Can you read faq705-7148 and see if that helps identify your issue?

Duane
Hook'D on Access
MS Access MVP

 

[dixie617]

I tried to debug the code by stepping through it, it stops right after it finds the PK of the record being modified. then gives me the error.

 

[dhookom]

I would place a breakpoint in the code on the For Each statement and open the debug window to test the values of RecordID, Frm.RecordSource, .Name, etc.

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
            Select Case .ControlType
                Case acTextBox
                    If .Value <> .OldValue Then
                        varBefore = .OldValue
                        varAfter = .Value
                        strControlName = .Name
                        'Build INSERT INTO statement.
                        strSQL = "INSERT INTO " _
                        & "AuditTable (EditDate, User, RecordID, SourceTable, " _
                        & " SourceField, BeforeValue, AfterValue) " _
                        & "VALUES (Now()," _
                        & cDQ & CurrentUser() & 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
                Case acComboBox
                    If .Value <> .OldValue Then
                        varBefore = .OldValue
                        varAfter = .Value
                        strControlName = .Name
                        'Build INSERT INTO statement.
                        strSQL = "INSERT INTO " _
                        & "AuditTable (EditDate, User, RecordID, SourceTable, " _
                        & " SourceField, BeforeValue, AfterValue) " _
                        & "VALUES (Now()," _
                        & cDQ & CurrentUser() & 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 Select
        End With
    Next
    Set ctl = Nothing
Exit Sub

Duane
Hook'D on Access
MS Access MVP