docmd.runSQL code help - INSERT INTO with multiple WHERE clauses

[JeffBarker]

Hi guys,

I'm trying to run an INSERT INTO statement via docmd.RunSQL, and I'm stumped - I keep getting the following syntax error:

Syntax error (missing operator) in query expression '(((tblHistory.HistoryID) = 42848) AND ((LookUpAllocater.Publication) = AHCP) AND ((LookUpAllocater.Issue) = Summer 2015) AND ((LookUpPageSize.Size) = Special))'.

The code I'm running is:

vSQL = "INSERT INTO tblProduction_LogDate ( HistoryID, LogDate, [User], Detail )" _
    & " SELECT tblHistory.HistoryID, Now() AS LogD, tblHistory.User AS UserName, 'Artwork Status changed to ' & [ArtworkStatus] & ' on ' & [Publication] & ' - ' & [Issue] & ' - ' & [lookuppagesize].[size] AS Det" _
    & " FROM ((tblAllocation INNER JOIN LookUpAllocater ON tblAllocation.AllocaterID = LookUpAllocater.AllocaterID) INNER JOIN LookUpPageSize ON tblAllocation.Size = LookUpPageSize.SizeID) INNER JOIN tblHistory ON tblAllocation.HistoryID = tblHistory.HistoryID" _
    & " WHERE (((tblHistory.HistoryID) = " & vRef & ") AND ((LookUpAllocater.Publication) = " & vTitle & ") AND ((LookUpAllocater.Issue) = " & vIssue & ") AND ((LookUpPageSize.Size) = " & vSize & ")) "

I'm convinced that I'm literally a couple of character changes away from solving this, but I'll admit that I am totally stumped!

If anyone can tell me where I'm going wrong, and how to solve it, I would be most greatful!

Cheers,

Jeff.

 

[jdraw]

It appears that some of your variables are text/string data type and as such must be enclosed in quotes " text "
seems AHCP and Summer 2015 and Special suffer from lack of quotes

Now -includes data and time is that what you need?

A good debugging technique is to do a debug.print vSql to see what Access "understands/renders".

From which table are Artworkstatus,Publication and Issue?
You can test a SELECT query in order to get a better understanding of syntax. Select is READ ONLY so won't affect your data. Use the select until you get the syntax correct, then convert to INSERT.

SELECT tblHistory.HistoryID, Now() AS LogD, tblHistory.User AS UserName, 'Artwork Status changed to ' & [ArtworkStatus] & ' on ' & [Publication] & ' - ' & [Issue] & ' - ' & [lookuppagesize].[size] AS Det" _
    & " FROM ((tblAllocation INNER JOIN LookUpAllocater ON tblAllocation.AllocaterID = LookUpAllocater.AllocaterID) INNER JOIN LookUpPageSize ON tblAllocation.Size = LookUpPageSize.SizeID) INNER JOIN tblHistory ON tblAllocation.HistoryID = tblHistory.HistoryID" _
    & " WHERE (((tblHistory.HistoryID) = " & vRef & ") AND ((LookUpAllocater.Publication) = " & vTitle & ") AND ((LookUpAllocater.Issue) = " & vIssue & ") AND ((LookUpPageSize.Size) = " & vSize & ")) "

Made up example showing use of quotes

mySQL = "SELECT FirstName, LastName From tblPersonInfo" & _
            " WHERE City = '" & Forms!MyForm!txtCityName & "';"

 

[RainLover]

Try creating a query using the query grid.

Once you have that you can do a copy paste of the query from the SQL view.

 

[JeffBarker]

It appears that some of your variables are text/string data type and as such must be enclosed in quotes " text "
seems AHCP and Summer 2015 and Special suffer from lack of quotes

Now -includes data and time is that what you need?

A good debugging technique is to do a debug.print vSql to see what Access "understands/renders".

From which table are Artworkstatus,Publication and Issue?
You can test a SELECT query in order to get a better understanding of syntax. Select is READ ONLY so won't affect your data. Use the select until you get the syntax correct, then convert to INSERT.

SELECT tblHistory.HistoryID, Now() AS LogD, tblHistory.User AS UserName, 'Artwork Status changed to ' & [ArtworkStatus] & ' on ' & [Publication] & ' - ' & [Issue] & ' - ' & [lookuppagesize].[size] AS Det" _
    & " FROM ((tblAllocation INNER JOIN LookUpAllocater ON tblAllocation.AllocaterID = LookUpAllocater.AllocaterID) INNER JOIN LookUpPageSize ON tblAllocation.Size = LookUpPageSize.SizeID) INNER JOIN tblHistory ON tblAllocation.HistoryID = tblHistory.HistoryID" _
    & " WHERE (((tblHistory.HistoryID) = " & vRef & ") AND ((LookUpAllocater.Publication) = " & vTitle & ") AND ((LookUpAllocater.Issue) = " & vIssue & ") AND ((LookUpPageSize.Size) = " & vSize & ")) "

Made up example showing use of quotes

mySQL = "SELECT FirstName, LastName From tblPersonInfo" & _
            " WHERE City = '" & Forms!MyForm!txtCityName & "';"

Hey jdraw, thanks for the response!

I have been playing around with a similar INSERT INTO statement elsewhere in my database, and that works, so I've copied the same format and set the following variables to read fields from the form that I'm running the code from.

These variables are HisID, ArtStat, Tit, Ish and Size, all as strings.

HisID is the record unique identifier, ArtStat is based on a combobox (which runs the INSERT INTO statement on its AfterUpdate Event), Tit, Ish and Size are all text boxes.

Here's that new code:

Private Sub cboArtworkStatus_AfterUpdate()
On Error GoTo Err_cboArtworkStatus_AfterUpdate

    Dim HisID, ArtStat, Tit, Ish, Size, vSQL As String
    
    HisID = Me.txtOrderRef
    ArtStat = Me.cboArtworkStatus
    Tit = Me.txtTitle
    Ish = Me.txtIssue
    Size = Me.txtSize

    vSQL = " INSERT INTO tblProduction_LogDate ( HistoryID, LogDate, [User], Detail )" _
    & " SELECT tblAllocation.HistoryID, Now() AS [Date], fGetUserName() AS UN, 'Artwork Status changed to ' & ArtStat & ' on ' & Tit & ' - ' & Ish & ' - ' & Size AS Det " _
    & " FROM tblAllocation INNER JOIN LookUpAllocater ON tblAllocation.AllocaterID = LookUpAllocater.AllocaterID " _
    & " WHERE (((tblAllocation.HistoryID)= " & HisID & "))"

    DoCmd.SetWarnings False

    DoCmd.RunSQL vSQL

    DoCmd.SetWarnings True

Exit_cboArtworkStatus_AfterUpdate:
    Exit Sub
    
Err_cboArtworkStatus_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_cboArtworkStatus_AfterUpdate

End Sub

When I update cboArtworkStatus, I get the Enter Parameter Value box for ArtStat, then Tit, then Ish, then Size - even though (when I step through the code), these variables are reading the values of their respective combo/text boxes from the form.

Do you have any suggestions on what could be going wrong, and how to fix this, please?

 

[JeffBarker]

Try creating a query using the query grid.

Once you have that you can do a copy paste of the query from the SQL view.

Hi RainLover, thanks for the response - this is what I'd done originally, and was having trouble with. Probably should have mentioned that to start with!

 

[RainLover]

Hi RainLover, thanks for the response - this is what I'd done originally, and was having trouble with. Probably should have mentioned that to start with!

Have you gone back and found the fault?

Hint. Start small get that working before adding something else. One step at a time.

BTW Turning Warnings off. Why have you done that. If there is an error you need to see what it is, not hide it.

 

[jdraw]

I agree with Rainlover. Get something basic working, then add additional functionality, slowly.
Suppressing the warning is basically telling Access not to help you resolve the error???

I would also include a debugging statement before trying to execcute/run that sql.

Debug.Print vSQL

And, to repeat, I would work with a SELECT query to get the SQL syntax correct and to get a working query. (You can use the query grid to do a SELECT, then convert to an INSERT for final sql).

 

[RainLover]

Jeff

What's happening. Did you solve the problem.

Don't forget that we are here to help

 

[JeffBarker]

Jeff

What's happening. Did you solve the problem.

Don't forget that we are here to help

Hi Rain,

Thanks for checking up - we've been moving offices for the past week, and today is the first day I've sat back down at my desk!

I have a ton of reports to catch up on, but this project is my next priority so once I've caught up on everything else I'll pick this back up again and feed back!

Cheers,

Jeff.

 

[JeffBarker]

Jeff

What's happening. Did you solve the problem.

Don't forget that we are here to help

Hi guys, I'm finally back on this project again, and I have to say that I'm still stumped!!!

 

[marlan]

Hi,
I your SELECT and FROM section you use JET SQL parameters and field names, in the case the JET interpeter knows what data type is each parameter.
In the WHERE section, parameters are VBA, for the JET interpeter, it makes no diference if the text is hard coded or a variable - it is sent for interpetation. And expects to receive text in quotes.
you can see in the error message: ((LookUpAllocater.Publication) = AHCP) AND ((LookUpAllocater.Issue) = Summer 2015)
'AHCP' and 'Summer 2015' are Text values, and should be in quotes:

" WHERE (((tblHistory.HistoryID) = " & vRef & ") AND ((LookUpAllocater.Publication) = '" & vTitle & "') AND ((LookUpAllocater.Issue) = '" & vIssue & "') AND ((LookUpPageSize.Size) = '" & vSize & "')) "

ATB

 

[marlan]

Hi Jeff,

Was I of any help?
Was I understood?...

 

[winshent]

you fixed this yet bloke ?

 

[JeffBarker]

Hi guys - thanks again for all your suggestions and assistance, and apologies for the very late reply...been kinda busy here again, so dropped off this project!

Finally manage to solve the problem, by using the following:

    vSQL = " INSERT INTO tblProduction_LogDate ( HistoryID, LogDate, [User], Detail )" _
    & " SELECT tblAllocation.HistoryID, Now(), fGetUserName(), 'Artwork Status changed to " & vArtStat & " on " & vTitle & " - " & vIssue & " - " & vSize & "'" _
    & " FROM tblAllocation INNER JOIN LookUpAllocater ON tblAllocation.AllocaterID = LookUpAllocater.AllocaterID " _
    & " WHERE tblAllocation.HistoryID = " & vHisID & " AND tblAllocation.AllocaterID = " & vAllocID

So thanks again for all your help!!