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

AliG

Registered User.
Local time
Today, 08:22
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!!
 

AliG

Registered User.
Local time
Today, 08:22
Joined
May 3, 2016
Messages
21
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!!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:22
Joined
May 7, 2009
Messages
19,175
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
 

Minty

AWF VIP
Local time
Today, 12:22
Joined
Jul 26, 2013
Messages
10,355
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 .
 

Minty

AWF VIP
Local time
Today, 12:22
Joined
Jul 26, 2013
Messages
10,355
This is a much nicer solution that on the other thread.
Thumb Up Arnelgp
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:22
Joined
Aug 30, 2003
Messages
36,118
Not sure if I should be posting this here or in Modules, but maybe someone can help me out.

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.
 

AliG

Registered User.
Local time
Today, 08:22
Joined
May 3, 2016
Messages
21
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.
 

AliG

Registered User.
Local time
Today, 08:22
Joined
May 3, 2016
Messages
21
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.

Got it, sorry!
 

AliG

Registered User.
Local time
Today, 08:22
Joined
May 3, 2016
Messages
21
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

When I try running this, it gives me Error 3271 Invalid Property Value instead . Am I missing something?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:22
Joined
Aug 30, 2003
Messages
36,118
Threads merged since both had replies and discussion seemed to be continuing on each.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:22
Joined
Feb 28, 2001
Messages
27,001
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.
 

AliG

Registered User.
Local time
Today, 08:22
Joined
May 3, 2016
Messages
21
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.

No, no unbound textboxes--just a few unbound comboboxes. But these shouldn't cause any issues since the code specifies textboxes, right?
 

AliG

Registered User.
Local time
Today, 08:22
Joined
May 3, 2016
Messages
21
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?
 

Users who are viewing this thread

Top Bottom