Getting "Syntax error in INSERT INTO" for Append Query in VBA (Access 2002)

Question

I am getting a "Syntax error in INSERT INTO" when I try to run an append query.

I'm making a database to make it easier on production operators; capturing some of the data by hand-held scanners.

I've been looking around to see if anybody has had a problem like this. I've seen some that were close, but when I've made the minor changes to my code, I still get the syntax error.

I am using an unbound form for data-entry purposes, but I need to capture the data from the form and add it to two different tables. Trying to run the DoCmd.RunSQL is when the error occurs. As far as I can tell the code, and SQL looks correct.

Here is the entire code for my procedure. Code:

Private Sub cmdNext_Click()

On Error GoTo Err_cmdNext_Click
'Append table: tblScan with the Badge Number, Part #/ICS#, Lot # (if available), Press (i.e. MD01), Shift, and the current
'                   Date/Time (captured with this code).
'Append table: tblJob with Press, Part#/ICS#, and the current Date/Time (captured with this code).
Dim strBadge As String
Dim strPress As String
Dim dtmDate As Variant
Dim strSQL As String
Dim strMessage As String
Dim strTitle As String
Dim varQuestion As Variant


strMessage = "You have not entered a correct "
strTitle = "ERROR MISSING or INCORRECT INFORMATION!"
strSQL = ""
dtmDate = Now

If IsNull(Me!cboBadgeNum.Column(1)) Then
    strMessage = strMessage & "Badge Number.  Please scan the operator's badge before continuing."
    varQuestion = MsgBox(strMessage, vbOKOnly + vbCritical + vbSystemModal, strTitle)
    Me!cboBadgeNum.SetFocus
    GoTo Exit_cmdNext_Click
Else
    strBadge = Me!cboBadgeNum.Column(1)
End If


If IsNull(Me!cboPress.Column(1)) Then
    strMessage = strMessage & "Press.  Please select the press where this job was run before continuing."
    varQuestion = MsgBox(strMessage, vbOKOnly + vbCritical + vbSystemModal, strTitle)
    Me!cboPress.SetFocus
    GoTo Exit_cmdNext_Click
Else
    strPress = Me!cboPress.Column(1)
End If

If gstrICS = " " Then
    strMessage = strMessage & "ICS Number.  Please Enter a valid ICS Number before continuing."
    varQuestion = MsgBox(strMessage, vbOKOnly + vbCritical + vbSystemModal, strTitle)
    txtICS.SetFocus
    GoTo Exit_cmdNext_Click
ElseIf gstrICS = "" Then
    strMessage = strMessage & "ICS Number.  Please Enter a valid ICS Number before continuing."
    varQuestion = MsgBox(strMessage, vbOKOnly + vbCritical + vbSystemModal, strTitle)
    txtICS.SetFocus
    GoTo Exit_cmdNext_Click
End If

If gstrLot = "" Then
    strMessage = strMessage & "Lot Number.  Please Enter a valid Lot Number before continuing."
    varQuestion = MsgBox(strMessage, vbOKOnly + vbCritical + vbSystemModal, strTitle)
    txtLot.SetFocus
    GoTo Exit_cmdNext_Click
End If

'Use an Apend query to update Scan table.   
**strSQL = "INSERT INTO tblScan (BadgeNum, PartNum, LotNum, Press, Shift, ScanDate) " & vbCrLf
strSQL = strSQL & "VALUES  (" & Chr(34) & strBadge & Chr(34) & ", "
strSQL = strSQL & Chr(34) & gstrICS & Chr(34) & ", "
strSQL = strSQL & Chr(34) & gstrLot & Chr(34) & ", "
strSQL = strSQL & Chr(34) & strPress & Chr(34) & ", "
strSQL = strSQL & gShift & ", "
strSQL = strSQL & "#" & dtmDate & "#);"**


DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'Use an Apend query to update Job table.
**strSQL = "INSERT INTO Job (Press, PartNum, StartDate) "
strSQL = strSQL & "VALUES (" & Chr(34) & strPress & Chr(34) & ", "
strSQL = strSQL & Chr(34) & gstrICS & Chr(34) & ", "
strSQL = strSQL & "Job.JobDate = #" & dtmDate & "#);"**


'DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

lblFirstName.Caption = " "
lblLastName.Caption = " "
txtICS.SetFocus
txtICS.Text = " "
txtLot.SetFocus
txtLot.Text = " "
cboBadgeNum.SetFocus
cboBadgeNum.Value = 0

DoCmd.Close


Exit_cmdNext_Click:
Exit Sub

Err_cmdNext_Click:
MsgBox Err.Description
Resume Exit_cmdNext_Click

End Sub

I've temporarily disabled the "DoCmd.SetWarnings" until I get the query to update the tables.

Thank you in advance for any assistance/help. It will be extremely helpful.

I want to thank everybody who posted on this problem. As @cularis and @HansUp pointed out my SQL was incorrect. I didn't quite get what @cularis was getting at, and @HansUp clear up my confusion. So thank you again. Posted below is the "cleaned up" code for my two update queries: — Talavar
– Talavar, Commented Jun 1, 2011 at 11:52

HansUp · Accepted Answer · 2011-05-31 18:45:15Z

2

Trying to run the DoCmd.RunSQL is when the error occurs. As far as I can tell the code, and SQL looks correct.

Your code calls DoCmd.RunSQL twice. Do you get errors from both, or only the second time?

I suggest you ditch DoCmd.RunSQL in favor of:

Dim db As DAO.Database
Set db = Currentdb
db.Execute strSQL, dbFailonerror

That way you won't have to fiddle with SetWarnings. Your error handler doesn't turn SetWarnings back on, which could leave Access in a state where important information is unavailable. So, if you feel you absolutely positively must twiddle SetWarnings, make sure however your sub ends (error or not), SetWarnings gets turned back on. But better IMO not to mess with SetWarnings in the first place.

@cularis tried to help you see you're constructing the second INSERT statement like this example with made-up names.

INSERT INTO tblFoo (fieldA, fieldB) VALUES (fieldA = 7, fieldB = 2)

As he said, you can't include = signs in the VALUES list. It has to be like this:

INSERT INTO tblFoo (fieldA, fieldB) VALUES (7, 2)

The point I want to add to his suggestion is to use Debug.Print so you can display strSQL to the Immediate Window before you execute it. If there is a problem, you can view the statement you're asking the database engine to execute ... instead of trying to imagine what your code has constructed. If you can't spot the problem straight away, copy the statement from the Immediate Window and paste it into the SQL View of a new query. If that still doesn't allow you to spot the problem, you can paste the statement into a question on stackoverflow.

answered May 31, 2011 at 18:45

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Talavar Over a year ago

I have to call the DoCmd.RunSQL twice, as I need to update two separate tables. (tblScan and tblJob). Thank you for the insight. I will remove the fields and equal signs and see if that works. I appreciate ALL of the help!

David-W-Fenton Over a year ago

If you need to insert into two different tables and the records are interdependent, you really ought to wrap the whole thing in a transaction, as doing it without that means one could succeed and one could fail, leaving you without all the data the user put in. What you really want is for EVERYTHING to fail, and then tell the user that.

Jacob · Accepted Answer · 2011-05-31 18:08:44Z

2

Your SQL is not valid. It should be:

 INSERT INTO tblJob  (Press, PartNum, StartDate) VALUES (value1, value2, value3)

Yours seems to be a mixture of INSERT INTO, UPDATE ... SET and SELECT ... FROM

answered May 31, 2011 at 18:08

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

3 Comments

Talavar Over a year ago

Thank you for your prompt response. I've changed the two SQL statements to remove the "& VbCrLf" with no change. Still getting the "Syntax error in the INSERT INTO statement".

Talavar Over a year ago

cularis: Sorry, I forgot to add your name. :-(

Talavar Over a year ago

Can you explain? I started a new parameter query, then looked at the SQL, and placed that into my code. I'm not understanding what you are saying that it (the SQL) is a mixture.

Philippe Grondier · Accepted Answer · 2011-05-31 20:55:00Z

0

Just a debugging trick, more than a real solution:

debug.print the strSQL string
copy\paste it to an Access Query (SQL View, pass-through query) and try to execute it (in fact you can do this in any query designer that can connect to your database)
you can then do some trials and errors on your text string until you get it working
you can for example try to insert one field with its value, then add the other fields, and so on ...

answered May 31, 2011 at 20:55

Philippe Grondier

11.1k3 gold badges37 silver badges75 bronze badges

1 Comment

Talavar Over a year ago

thank you. Found out I was using "=" and that was the syntax error.

Collectives™ on Stack Overflow

Getting "Syntax error in INSERT INTO" for Append Query in VBA (Access 2002)

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related