INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...I am very happy with the whole site and would like to extend my compliments to all of you who work to make it one of the most useful sites (If not THE Most Useful) ...and the easiest to navigate..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Other topics Forum

Error with AuditTable Module

Read More Threads Like This One

dixie617 (Programmer)

19 Jan 12 15:49

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 (Programmer)

19 Jan 12 16:35

You have

CODE

Debug.Print strSQL

Why not share the results in the debug window?

Duane
Hook'D on Access
MS Access MVP

dixie617 (Programmer)

19 Jan 12 19:15

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

Thanks
Dix

dhookom (Programmer)

19 Jan 12 20:05

You aren't being of much help. Can you read FAQ705-7148: How to debug your code and see if that helps identify your issue?

Duane
Hook'D on Access
MS Access MVP

dixie617 (Programmer)

23 Jan 12 9:36

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 (Programmer)

23 Jan 12 9:51

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.

CODE

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Other topics

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Error with AuditTable Module

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!