Syntax help needed please! (1 Viewer)

estelleh

Member
Local time
Today, 21:55
Joined
Jul 16, 2021
Messages
33
I have frmJobs with subform frmJobParts - data comes from table Jobs and JobParts.

I am trying to copy multiple records in table JobParts for a previous job (the value comes from [frmJobs]![txtPrevJobNo]) and append them to the JobParts table, but with an updated job number (value comes from[frmJobs]![txtjobNo]).

I tried to create an Append query without much success, so I thought I would try and do it as an SQL statement, but I am gettg a syntax error when I run the SQL statement.

Can anyone point me at the syntax error please?

Code:
    Dim StrSQL As String
    Dim IntJobNo As Integer
    IntJobNo = [Forms]![frmJobs]![txtJobNo]
   
    StrSQL = "INSERT INTO JobParts (JobNo, PartNo, PaperColour, PaperGSM, PaperType, FrontInk1, FrontInk2, FrontInk3, FrontInk4, RevInk1, RevInk2," & _
    "RevInk3, RevInk4 SELECT (intJobNo, JobParts.PartNo, JobParts.PaperColour, JobParts.PaperGSM, JobParts.PaperType, JobParts.FrontInk1," & _
    "JobParts.FrontInk2, JobParts.FrontInk3, JobParts.FrontInk4, JobParts.RevInk1, JobParts.RevInk2, JobParts.RevInk3, JobParts.RevInk4)" & _
    "FROM JobParts WHERE (((JobParts.JobNo)=[frmJobs]![txtPrevJobNo]))"
   
    DoCmd.RunSQL (StrSQL)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:55
Joined
Feb 28, 2001
Messages
20,831
For one thing, you are missing a closing parenthesis behind the INSERT field list (ending with RevInk3, Revink4). I will leave it to you to verify the correspondence between selected and inserted fields, but at first glance they appear to align OK.

You also don't need the parentheses around strSQL when you try to run it. In that context, DoCmd.RunSQL is not a function that would return a value so use the subroutine invocation syntax. IF that were a function, the parenthesis WOULD be required... but here, no.
 

estelleh

Member
Local time
Today, 21:55
Joined
Jul 16, 2021
Messages
33
For one thing, you are missing a closing parenthesis behind the INSERT field list (ending with RevInk3, Revink4). I will leave it to you to verify the correspondence between selected and inserted fields, but at first glance they appear to align OK.

You also don't need the parentheses around strSQL when you try to run it. In that context, DoCmd.RunSQL is not a function that would return a value so use the subroutine invocation syntax. IF that were a function, the parenthesis WOULD be required... but here, no.
Thank you! No idea how I did not see that missing parenthesis. Fixed that and feeling excited, I reran it. Now I get the following error: o_O
Syntax.JPG

The updated code looks like this:

Code:
    Dim StrSQL As String
    Dim IntJobNo As Integer
    IntJobNo = [Forms]![frmJobs]![txtJobNo]
    StrSQL = "INSERT INTO JobParts (JobNo, PartNo, PaperColour, PaperGSM, PaperType, FrontInk1, FrontInk2, FrontInk3, FrontInk4, RevInk1, RevInk2, " & _
    "RevInk3, RevInk4) SELECT (intJobNo, JobParts.PartNo, JobParts.PaperColour, JobParts.PaperGSM, JobParts.PaperType, JobParts.FrontInk1, " & _
    "JobParts.FrontInk2, JobParts.FrontInk3, JobParts.FrontInk4, JobParts.RevInk1, JobParts.RevInk2, JobParts.RevInk3, JobParts.RevInk4) " & _
    "FROM JobParts WHERE (((JobParts.JobNo)=[frmJobs]![txtPrevJobNo]))"
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:55
Joined
Feb 28, 2001
Messages
20,831
Technically, you don't want the parentheses around the field list following the SELECT. I'm going to guess (with a fair degree of confidence) that those parentheses are confusing the SQL interpreter because normally, you would use parentheses around math expressions. (Yes, you can put a formula in a SELECT clause, like SELECT (JobNo+1) As NewJobNo, .... .) But math expressions don't usually have commas in them unless they involve a function, and you aren't obviously using a function.
 

estelleh

Member
Local time
Today, 21:55
Joined
Jul 16, 2021
Messages
33
Technically, you don't want the parentheses around the field list following the SELECT. I'm going to guess (with a fair degree of confidence) that those parentheses are confusing the SQL interpreter because normally, you would use parentheses around math expressions. (Yes, you can put a formula in a SELECT clause, like SELECT (JobNo+1) As NewJobNo, .... .) But math expressions don't usually have commas in them unless they involve a function, and you aren't obviously using a function.
Thanks Doc Man.... this is a "1 step forward, 2 steps backward" problem for me, so I really appreciate your help.

I have removed the parentheses.
Code:
Dim StrSQL As String
    Dim intJobNo As Integer
    intJobNo = [Forms]![frmJobs]![txtJobNo]
   
    StrSQL = "INSERT INTO JobParts (JobNo, PartNo, PaperColour, PaperGSM, PaperType, FrontInk1, FrontInk2, FrontInk3, FrontInk4, RevInk1, RevInk2, " & _
    "RevInk3, RevInk4) SELECT intJobNo, JobParts.PartNo, JobParts.PaperColour, JobParts.PaperGSM, JobParts.PaperType, JobParts.FrontInk1, " & _
    "JobParts.FrontInk2, JobParts.FrontInk3, JobParts.FrontInk4, JobParts.RevInk1, JobParts.RevInk2, JobParts.RevInk3, JobParts.RevInk4 " & _
    "FROM JobParts WHERE (((JobParts.JobNo)=[Forms]![frmJobs]![txtPrevJobNo]))"

Now, when I run it, I get a parameter request for intJobNo as well as for [Forms]![frmJobs]![txtPrevJobNo]. Yet when I put a breakpoint on the RunSQL statement and check these two, they have the correct values!

Immediate.JPG
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:55
Joined
May 7, 2009
Messages
14,021
Code:
StrSQL = "INSERT INTO JobParts (JobNo, PartNo, PaperColour, PaperGSM, PaperType, FrontInk1, FrontInk2, FrontInk3, FrontInk4, RevInk1, RevInk2, " & _
    "RevInk3, RevInk4) SELECT intJobNo, JobParts.PartNo, JobParts.PaperColour, JobParts.PaperGSM, JobParts.PaperType, JobParts.FrontInk1, " & _
    "JobParts.FrontInk2, JobParts.FrontInk3, JobParts.FrontInk4, JobParts.RevInk1, JobParts.RevInk2, JobParts.RevInk3, JobParts.RevInk4 " & _
    "FROM JobParts WHERE (((JobParts.JobNo)=" & [Forms]![frmJobs]![txtPrevJobNo] & "))"
 

estelleh

Member
Local time
Today, 21:55
Joined
Jul 16, 2021
Messages
33
Code:
StrSQL = "INSERT INTO JobParts (JobNo, PartNo, PaperColour, PaperGSM, PaperType, FrontInk1, FrontInk2, FrontInk3, FrontInk4, RevInk1, RevInk2, " & _
    "RevInk3, RevInk4) SELECT intJobNo, JobParts.PartNo, JobParts.PaperColour, JobParts.PaperGSM, JobParts.PaperType, JobParts.FrontInk1, " & _
    "JobParts.FrontInk2, JobParts.FrontInk3, JobParts.FrontInk4, JobParts.RevInk1, JobParts.RevInk2, JobParts.RevInk3, JobParts.RevInk4 " & _
    "FROM JobParts WHERE (((JobParts.JobNo)=" & [Forms]![frmJobs]![txtPrevJobNo] & "))"
Damn! I keep doing that :-( - thanks. It still asks me for a value for intJobNo?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:55
Joined
May 7, 2009
Messages
14,021
sorry, my mistake also:
Code:
StrSQL = "INSERT INTO JobParts (JobNo, PartNo, PaperColour, PaperGSM, PaperType, FrontInk1, FrontInk2, FrontInk3, FrontInk4, RevInk1, RevInk2, " & _
    "RevInk3, RevInk4) SELECT " & [intJobNo] & ", JobParts.PartNo, JobParts.PaperColour, JobParts.PaperGSM, JobParts.PaperType, JobParts.FrontInk1, " & _
    "JobParts.FrontInk2, JobParts.FrontInk3, JobParts.FrontInk4, JobParts.RevInk1, JobParts.RevInk2, JobParts.RevInk3, JobParts.RevInk4 " & _
    "FROM JobParts WHERE (((JobParts.JobNo)=" & [Forms]![frmJobs]![txtPrevJobNo] & "))"
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:55
Joined
Feb 28, 2001
Messages
20,831
arnelgp showed you WHAT to do, but perhaps it would help if I told you WHY to do that.

When you are working in Access that involves explicit SQL statements such as you are now using, there are TWO environments. When you deal with forms and VBA, you are in the GUI environment. VBA can "see" the forms and reach out to grab stuff from them easily. Eventually, you build your SQL statement with the substitutions as suggested by arnelgp. But Access doesn't do SQL execution. It passes SQL strings to the ACE database engine, which understands SQL completely. The catch is that ACE is a child process of Access. Being a child process means it has its own private memory and does not share memory with other processes. This includes the Access GUI. If you like, you can prove this separation for yourself by opening Task Manager, finding a running copy of MSACCESS.EXE, and noting that you have the ">" symbol next to it, meaning it can be expanded. And when you click on the ">" you see TWO processes - MSACCESS.EXE and your application name as a separate process.

OK, what does this mean in practical terms? Well, arnelgp just showed you how to include values in the SQL string so that when you finally DO pass it to ACE, it has everything it needs and DOESN'T have to ask you for parameters because they are included in the SQL string. The split memory setup sort of "cheats" on the memory rules of Access by putting ACE's code, data area, stack, heap, and other memory related structures in a private area that it doesn't have to share with Access. And Access doesn't have to share with ACE. That means your system can dedicate MORE MEMORY to your task by doing, essentially, a "divide and conquer" on it. As you have seen, the one "down" side to this is that you must assure that your SQL is complete (or can be completed via substitution/concatenaton) before you pass it to ACE for final processing.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:55
Joined
Feb 19, 2013
Messages
13,336
always helps to debug.print your sql string before running it - you should then see what doesn't make sense. You can also copy it to a temporary new query and try to run it from there - can often provide a more informative error.

and if your code is actually in frmJobs

you don't need the [Forms]![frmJobs]! part, just the name of the control
 

estelleh

Member
Local time
Today, 21:55
Joined
Jul 16, 2021
Messages
33
(y)
@The_Doc_Man Thank you for the explanation - it makes total sense :) My son actually asked me last night whether Access runs SQL directly and I looked at him blankly!
 

estelleh

Member
Local time
Today, 21:55
Joined
Jul 16, 2021
Messages
33
always helps to debug.print your sql string before running it - you should then see what doesn't make sense. You can also copy it to a temporary new query and try to run it from there - can often provide a more informative error.
I actually tried that last night (bear in mind I'm brand new to Access, so every solution requires another learning curve!) I was getting a nonsensical result from the debug.Print strSQL - after reading The_Doc_Man's explanation, I figured out I am debug.print'ing the SQL statement before assigning the values to the variables. Having fixed that, I now get this result from the Debug.Print:
Code:
INSERT INTO JobParts
(JobNo, PartNo, PaperColour, PaperGSM, PaperType, FrontInk1, FrontInk2, FrontInk3, FrontInk4, RevInk1, RevInk2, RevInk3, RevInk4)
SELECT 76, JobParts.PartNo, JobParts.PaperColour, JobParts.PaperGSM, JobParts.PaperType, JobParts.FrontInk1, JobParts.FrontInk2, JobParts.FrontInk3, JobParts.FrontInk4, JobParts.RevInk1, JobParts.RevInk2, JobParts.RevInk3, JobParts.RevInk4
FROM JobParts WHERE JobParts.JobNo = 1

which to me looks like exactly what I'm trying to do. The old Job number (the records I want to duplicate) is 1 and the new Job number is 76 (the records I want to insert) and yet I am still getting the key violation message. I suspect this may have something to do with the field JobNo being the PK of the Jobs table - although that doesn't make sense as I am not referencing the Jobs table anywhere in that SQL string. The PK of the Job Parts table is ID (autonumber) which I am not inserting.

@CJ_London: I don't understand the 2nd part of your answer

"You can also copy it to a temporary new query and try to run it from there - can often provide a more informative error."

Can you explain it to me, please?

Thanks to everyone for the help so far 🤗
 

estelleh

Member
Local time
Today, 21:55
Joined
Jul 16, 2021
Messages
33
Got it!! 💃

Turns out the key violations is because the record had not yet been saved in the Jobs table, so I was trying to insert a record into the JobParts table for which there was no master record (yet).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:55
Joined
Feb 28, 2001
Messages
20,831
That is a different rule, simple to remember and understand. The "cross-table key dependency" rule is "Access doesn't like to create orphans." Or, "can't make a child without having parents first." Good for you to have spotted that. Congrats!

The "copy to a temporary query and run it from there" suggestion is... there is your query string all debug.printed out neatly in the Immediate Window. You can do a cursor-based copy of the string by highlighting that string and hitting CTRL/C, then go do a query design. Switch to SQL view and do a CTRL/V (paste) of what you copied. Now try to switch back to the query grid view. If you have syntax errors, that switchback might show them up. But you can also SAVE that query temporarily and then hit the Execute (red !) to see what it tries to do. Doing an Execute from that context usually gives you more detailed error messages. Obviously, you might wish to do this on a test copy of your DB or allow yourself a way to "back out" whatever that test execution might do.
 
Last edited:

estelleh

Member
Local time
Today, 21:55
Joined
Jul 16, 2021
Messages
33
That is a different rule, simple to remember and understand. The "cross-table key dependency" rule is "Access doesn't like to create orphans." Or, "can't make a child without having parents first." Good for you to have spotted that. Congrats!
Thank you ☺️

Going to try your suggestion on the temp query tomorrow when my brain doesn't hurt so much!
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 20:55
Joined
Feb 19, 2013
Messages
13,336
I don't understand the 2nd part of your answer

simply copy your generated code - e.g.

INSERT INTO JobParts
(JobNo, PartNo, PaperColour, PaperGSM, PaperType, FrontInk1, FrontInk2, FrontInk3, FrontInk4, RevInk1, RevInk2, RevInk3, RevInk4)
SELECT 76, JobParts.PartNo, JobParts.PaperColour, JobParts.PaperGSM, JobParts.PaperType, JobParts.FrontInk1, JobParts.FrontInk2, JobParts.FrontInk3, JobParts.FrontInk4, JobParts.RevInk1, JobParts.RevInk2, JobParts.RevInk3, JobParts.RevInk4
FROM JobParts WHERE JobParts.JobNo = 1

into a new query -

1. Create>Query Design
2. close the show table window
3. Select SQL view (top left of ribbon or bottom right of access window)
4. highlight any text showing (usually SELECT and usually already highlighted) and paste to overwrite it

you can then try to run it or go to the query builder view to see 'what it looks like'

As a separate tip for getting the required sql. you can also construct your query in the query builder, then go to the sql view to see what the sql looks like - and copy/paste to vba if you wish (will need the addition of double quotes etc.)

As a further tip, when building your query and you need to use quotation marks around text, get in the habit of using single quotes rather than double quotes - in vba you would use single quotes
 

estelleh

Member
Local time
Today, 21:55
Joined
Jul 16, 2021
Messages
33
simply copy your generated code - e.g.

INSERT INTO JobParts
(JobNo, PartNo, PaperColour, PaperGSM, PaperType, FrontInk1, FrontInk2, FrontInk3, FrontInk4, RevInk1, RevInk2, RevInk3, RevInk4)
SELECT 76, JobParts.PartNo, JobParts.PaperColour, JobParts.PaperGSM, JobParts.PaperType, JobParts.FrontInk1, JobParts.FrontInk2, JobParts.FrontInk3, JobParts.FrontInk4, JobParts.RevInk1, JobParts.RevInk2, JobParts.RevInk3, JobParts.RevInk4
FROM JobParts WHERE JobParts.JobNo = 1

into a new query -

1. Create>Query Design
2. close the show table window
3. Select SQL view (top left of ribbon or bottom right of access window)
4. highlight any text showing (usually SELECT and usually already highlighted) and paste to overwrite it

you can then try to run it or go to the query builder view to see 'what it looks like'

As a separate tip for getting the required sql. you can also construct your query in the query builder, then go to the sql view to see what the sql looks like - and copy/paste to vba if you wish (will need the addition of double quotes etc.)

As a further tip, when building your query and you need to use quotation marks around text, get in the habit of using single quotes rather than double quotes - in vba you would use single quotes
Thank you so much - going to try this tomorrow when my brain isn't hurting so much!
 

Users who are viewing this thread

Top Bottom