Insert Into Query Syntax Error

[Pato-chan]

I can't seem to weed out the syntax error in this SQL query, even after
pasting the syntax from the immediate window into Access's SQL Window. I
keep getting the "Enter Parameter Value" box with MCCR14D06 as the parameter
name, which is the value in lst.Column(1, varItem). Any ideas?

Private Sub cmdAddToBePrinted_Click()
Set lst = lstSelectParts
For Each varItem In lst.ItemsSelected
'Insert each of the selected items into the ToBePrinted table from
the tblKLOCLocations
strSQL = "INSERT INTO ToBePrinted ( Item_Number, KLOC_Location )
SELECT tblKLOCLocations.[Item Number], tblKLOCLocations.[KLOC LOCATION] FROM
tblKLOCLocations WHERE (((tblKLOCLocations.[Item Number])=" &
lst.ItemData(varItem) & ") AND ((tblKLOCLocations.[KLOC LOCATION])=" &
lst.Column(1, varItem) & "));"
Debug.Print strSQL
DoCmd.RunSQL strSQL
Next

End Sub

 

[Allen Browne]

Assuming [KLOC LOCATION] is a Text type field, you need quote marks as the
delimiter around the literal string value:

& ") AND ((tblKLOCLocations.[KLOC LOCATION])=""" & _
lst.Column(1, varItem) & """));"

 

[Duane Hookom]

You must add text/string delimiters around string values:

Private Sub cmdAddToBePrinted_Click()
Set lst = lstSelectParts
For Each varItem In lst.ItemsSelected
'Insert each of the selected items into the _
ToBePrinted table from the tblKLOCLocations
strSQL = "INSERT INTO ToBePrinted " & _
"( Item_Number, KLOC_Location ) " & _
"SELECT [Item Number], [KLOC LOCATION] " & _
"FROM tblKLOCLocations " & _
"WHERE [Item Number]=" & lst.ItemData(varItem) & _
" AND [KLOC LOCATION]=""" & lst.Column(1, varItem) & """"
Debug.Print strSQL
DoCmd.RunSQL strSQL
Next
End Sub
This code assumes KLOC LOCATION is text and Item Number is numeric.