Silly SQL End of statement prob

[fergcu]

Hey everyone - am trying to get a simple (i think!) update query to work between two tables. I am aiming to do this by selecting the source and destination tables from combo boxes, and then select the fields that will have the reference and obsolete data with further combo boxes. I have generated what i believe is the correctish SQL, but i am getting a problem with the syntax. It was originally to do with the end not being correct, but i have now been (stupidly) moving bits around so much that i have got lost in the code.

I would be extremely grateful (as ever!) if someone can point out where i am going wrong with this. The code for the query is listed below:

Private Sub cmdUpdateQuery_Click()

'variables
    Dim MyDB As DAO.Database
    Dim qDef As DAO.QueryDef
    Dim strSQL As String
    Dim strUPDATE As String
    Dim strINNER As String
    Dim strON As String
    Dim strSET As String
           
    Set MyDB = CurrentDb()
    
     
'strSQL = "UPDATE " & Me.cbotblDestination & " INNER JOIN " & Me.cbotblReference & " ON " & Me.cbotblDestinationFieldCheck & " = " & Me.cbotblReferenceFieldCheck & " SET " & Me.cbotblDestinationFieldData & " = " & Me.cbotblReferenceFieldData;

strUPDATE = "UPDATE '" & (Me.cbotblDestination) & "'"
strINNER = "INNER JOIN  '" & (Me.cbotblReference) & "'"
strON = "ON '" & (Me.cbotblDestinationFieldCheck) & "' = '" & (Me.cbotblReferenceFieldCheck) & "'"
strSET = " SET '" & (Me.cbotblDestinationFieldData) & "' = '" & (Me.cbotblReferenceFieldData)"'"



strSQL = "strUPDATE & strINNER & strON & strSET"
 
    ' save query with new SQL statement
    Set qDef = CurrentDb.QueryDefs("UpdateQuery")
    qDef.SQL = strSQL
    Set qDef = Nothing
    

    DoCmd.OpenQuery "UpdateQuery", acViewNormal, acReadOnly


Exit_cmdUpdateQuery_Click:
    Exit Sub
    
End Sub

Many thanks,

Fergcu

 

[pbaldy]

These things are easiest to debug if you add:

Debug.Print strSQL

after the string is built and examine the resulting string in the immediate window. If you don't see the problem, post the SQL here (and the actual error message would be nice).

 

[tehNellie]

strSQL = "strUPDATE & strINNER & strON & strSET"

Would appear to be your problem, by wrapping in quotes you are setting strSQL to be
strUPDATE & strINNER & strON & strSET
rather than the contents of those variables.

pbaldy's suggestion is always worth trying though.

 

[fergcu]

sorry for the delay - other bits of work suddenly took priority

Thanks for the thoughts so far...

Ok, the problem is in the syntax:

strUPDATE = "UPDATE '" & (Me.cbotblDestination) & "'"
strINNER = "INNER JOIN  '" & (Me.cbotblReference) & "'"
strON = "ON '" & (Me.cbotblDestinationFieldCheck) & "' = '" & (Me.cbotblReferenceFieldCheck) & "'"
strSET = " SET '" & (Me.cbotblDestinationFieldData) & "' = '" & (Me.cbotblReferenceFieldData)"'"

it all types in fine, but when i press enter at the end of the final line, the final line is highlighted and i get the error message "Compile error: Expected: End of Statement". I am assuming it is to do with how my single and double quotes are placed, but i havent worked out where it is going wrong.

I therefore cant run a debug.print strsql, because the code wont even compile.

Any thoughts?

(oh, and thanks for picking up on the quotes around strSQL, tehNellie).

 

[pbaldy]

No & between the last value and the last string literal.

 

[fergcu]

ah, so simple - thank you!

Now, the real problems start, haha...

it is now informing me that the "query must contain at least one table or query".

(as some background, i am create an update query by selecting the tables and fields from combo boxes in a form - the (me.cbotblreference) etc should be pulling the data from there).

From this, i am assuming that i have the syntax for getting the data incorrect. Any thoughts? Does anyone know of a good example of this?

Thanks,

Fergus.

 

[pbaldy]

Well, it would be nice if you used the technique I recommended earlier. Either you'll see the problem yourself or you can at least post the SQL for someone here to look at. For starters, you wouldn't want single quotes around the table name(s).

 

[fergcu]

Paul,

apologies, i have been trying it, but its not getting far enough to generate the sql. I will keep trying with it. If i manage to get it to generate any form of SQL, i will let you know, but im hoping that if i can get it to that point, i can get it to work (as i believe that i have worked out what teh sql will need to be).

Kind regards,

fergus.

 

[pbaldy]

Based on the latest error, it sounds like it's trying to execute the SQL, so the Debug.Print should work. One way to sort out the proper structure of the query is to create one that works using the QBE, then duplicate the structure in VBA.

 

[fergcu]

ok, huzzah, it's working - thanks Paul. The problem i was having with printsql was a basic understanding of what printsql was actually doing - i didn't know about the 'immediate' window at the time, and so wasn't actually seeing what was being printed.

with the aid of printsql, i worked out the issue, and corrected the syntax.

So thank you all for your help again.

kind regards,


fergus.

 

[pbaldy]

No problem; the Debug.Print is a valuable tool that you will use a lot.