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

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开发者_如何学Python 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.

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.

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

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 ...