Syntax error (missing operator) in query expression when running Audit Trail module

A

AliG

Registered User.
Local time
Today, 10:03
Joined
May 3, 2016
Messages
21
Hi guys,

Not sure if I should be asking this here or in Queries, but maybe someone can help me out.

I am trying to run an Audit Trail module on an Invoice form to keep track of changes. The same module works fine with several of my other forms, and my Invoice form doesn't cause any errors when I don't have the Audit Trail running. I am no expert and am not sure what to do to solve this! Here is the SQL for the form's record source query, which is what causes the error:

Code: 
SELECT tblInvoice.*, tblAssignment.RateOut, tblTaskOrder.TaskOrderID, tblTaskOrder.TaskOrderName, tblPeople.PeopleID, tblPeople.[Firstname] & " " & [Lastname] AS FullName, tblVendor.VendorName
FROM (((tblInvoice INNER JOIN tblAssignment ON tblInvoice.AssignmentID = tblAssignment.AssignmentID) INNER JOIN tblTaskOrder ON tblAssignment.TaskOrderID = tblTaskOrder.TaskOrderID) INNER JOIN tblPeople ON tblAssignment.PeopleID = tblPeople.PeopleID) INNER JOIN tblVendor ON tblPeople.Vendor = tblVendor.VendorID;

Here is the Audit Trail code that works on other forms:

Code: 
Option Compare Database
Option Explicit

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 " _
           & "tblAudit (EditDate, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & 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"
End Sub

And I have the Audit Trail Module set to run in the form's BeforeUpdate Event like so (which is successful in my other forms where I use the module):

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, InvoiceID)
End Sub

Any ideas for me? TIA!!
 
Hi guys,

Not sure if I should be posting this here or in Modules, but maybe someone can help me out.

I am trying to run an Audit Trail module on an Invoice form to keep track of changes. The same module works fine with several of my other forms, and my Invoice form doesn't cause any errors when I don't have the Audit Trail running. I am no expert and am not sure what to do to solve this! Here is the SQL for the form's record source, which is what causes the error:
Code: 
  SELECT tblInvoice.*, tblAssignment.RateOut, tblTaskOrder.TaskOrderID, tblTaskOrder.TaskOrderName, tblPeople.PeopleID, tblPeople.[Firstname] & " " & [Lastname] AS FullName, tblVendor.VendorName
FROM (((tblInvoice INNER JOIN tblAssignment ON tblInvoice.AssignmentID = tblAssignment.AssignmentID) INNER JOIN tblTaskOrder ON tblAssignment.TaskOrderID = tblTaskOrder.TaskOrderID) INNER JOIN tblPeople ON tblAssignment.PeopleID = tblPeople.PeopleID) INNER JOIN tblVendor ON tblPeople.Vendor = tblVendor.VendorID;

And here is the Audit Trail module code:
Code: 
Option Compare Database
Option Explicit

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 " _
           & "tblAudit (EditDate, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & 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"
End Sub

It's pulled in from my form's BeforeUpdate Event, which is successful with all the other forms I use this module on. I'm thinking it's my query that needs tweaking, but I am totally lost. Any ideas for me? TIA!!
 
use querydef to insert the record:
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
Dim qd As DAO.QueryDef
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
                
                strSQL = "INSERT INTO " _
                & "tblAudit (EditDate, RecordID, SourceTable, " _
                & " SourceField, BeforeValue, AfterValue) " _
                & "VALUES (p1, p2, p3, p4, p5, p6);"
                
                Set qd = CurrentDb.CreateQueryDef("", strSQL)
                qd.Parameters(0) = Now()
                qd.Parameters(1) = recordid.Value
                qd.Parameters(2) = frm.RecordSource
                qd.Parameters(3) = .Name
                qd.Parameters(4) = varBefore
                qd.Parameters(5) = varAfter
                qd.Execute
                Set qd = Nothing
            End If
        End If
    End With
Next
Set ctl = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub
 
And the result of the Debug.Print strSQL is what ?

I suspect your might be missing some # # around your Now() value , but you have the debug in front of you .
 
This is a much nicer solution that on the other thread.
Thumb Up Arnelgp
 
AliG said:
Not sure if I should be posting this here or in Modules, but maybe someone can help me out.
Click to expand...

In the future, make your best guess and just post once. We can always move it if it's in the wrong place, but having it in two places risks people trying to answer on one thread when it's already been answered on the other.
 
Here is an example of what shows in my Immediate Window for the Invoice form (I have a few examples since I tried running this a few times):

Code: 
INSERT INTO tblAudit (EditDate, RecordID, SourceTable,  SourceField, BeforeValue, AfterValue) VALUES (Now(),"1", "SELECT tblInvoice.*, tblAssignment.RateOut, tblAssignment.TaskOrderID, tblAssignment.PeopleID, tblVendor.VendorName, [Firstname] & " " & [Lastname] AS FullName, tblTaskOrder.TaskOrderName FROM (((tblAssignment INNER JOIN tblInvoice ON tblAssignment.AssignmentID = tblInvoice.AssignmentID) INNER JOIN tblTaskOrder ON tblAssignment.TaskOrderID = tblTaskOrder.TaskOrderID) INNER JOIN tblPeople ON tblAssignment.PeopleID = tblPeople.PeopleID) INNER JOIN tblVendor ON tblPeople.Vendor = tblVendor.VendorID;", "InvoiceLaborAmount", "1500", "1600")

The actual error I get shows as a generic MS Access error pop up when trying to change something via the form and has code 3075 at the end after listing my full record source query.
 
pbaldy said:
In the future, make your best guess and just post once. We can always move it if it's in the wrong place, but having it in two places risks people trying to answer on one thread when it's already been answered on the other.
Click to expand...

Got it, sorry!
 
arnelgp said:
use querydef to insert the record:
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
Dim qd As DAO.QueryDef
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
                
                strSQL = "INSERT INTO " _
                & "tblAudit (EditDate, RecordID, SourceTable, " _
                & " SourceField, BeforeValue, AfterValue) " _
                & "VALUES (p1, p2, p3, p4, p5, p6);"
                
                Set qd = CurrentDb.CreateQueryDef("", strSQL)
                qd.Parameters(0) = Now()
                qd.Parameters(1) = recordid.Value
                qd.Parameters(2) = frm.RecordSource
                qd.Parameters(3) = .Name
                qd.Parameters(4) = varBefore
                qd.Parameters(5) = varAfter
                qd.Execute
                Set qd = Nothing
            End If
        End If
    End With
Next
Set ctl = Nothing
Exit Sub

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

When I try running this, it gives me Error 3271 Invalid Property Value instead . Am I missing something?
 
Threads merged since both had replies and discussion seemed to be continuing on each.
 
In your "for each ctl" loop - is any textbox on that form unbound? Because unbound textboxes do not have the .OldValue property. Your loop would trip on any unbound textbox it stumbled (literally) across.
 
The_Doc_Man said:
In your "for each ctl" loop - is any textbox on that form unbound? Because unbound textboxes do not have the .OldValue property. Your loop would trip on any unbound textbox it stumbled (literally) across.
Click to expand...

No, no unbound textboxes--just a few unbound comboboxes. But these shouldn't cause any issues since the code specifies textboxes, right?
 
So on a whim I've tried separating my record source query into subqueries to make it less complex and see if that changes anything. I linked tblPeople and tblVendor in sqy1, then link sqy1 with tblAssignment and tblTaskOrder in sqy2, then tblInvoice with sqy2 in the final query and made this the record source of the form. When I have it set up this way, I get a pop up error from Access when I change something in the form that says "Operation is not supported for this type of object 3251" even though the audits get logged accurately in tblAudit and tblInvoice. Anyone know how to get rid of the error pop up since it's not actually causing any problems that I can see?
 
You must log in or register to reply here.

Similar threads

A
Access Audittrail
Replies
5
Views
249
CJ_London
C
K
Error 13 | Audit Log
Replies
4
Views
809
The_Doc_Man
The_Doc_Man
Kayleigh
  • Solved
Solved Audit trail error
2
Replies
25
Views
3,123
gcarpenter
gcarpenter
D
Solved Syntax error in date in query expression
Replies
8
Views
285
DeWaal
D
B
Solved AuditChanges not capturing ID for NEW additions
Replies
7
Views
768
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom