Solved Access VB DoCmd.RunSQL "Insert into..." getting syntax error

Amlitnod

New member
Local time
Today, 02:43
Joined
Aug 26, 2021
Messages
5
Need another set of eyes here. I get error 3134, syntax can insert into.... on first "DoCmd.RunSQL..." at end of code.
Table has many fields, I only need these 3 populated when 'insert into' on the record.


'-=-=-
Private Sub ItemBinLocationMove_Click()

Dim vOldBinLoc, vNewBinLoc, vUPCLabelId, vMsgTitle, vMsg, vBinMoveQty As Integer

' get Item/Tag/UPC number
vMsgTitle = "Please Scan Item Barcode" ' Set title.
vMsg = "Scan Barcode"
vUPCLabelId = InputBox(vMsgTitle, vMsg, vUPCLabelId)

' get OLD bin location
vMsgTitle = "Please Scan OLD bin Location" ' Set title.
vMsg = "Enter OLD Bin Location"
vOldBinLoc = InputBox(vMsgTitle, vMsg, vOldBinLoc)

' get NEW bin location
vMsgTitle = "Please Scan NEW bin Location" ' Set title.
vMsg = "Enter New Bin Location"
vNewBinLoc = InputBox(vMsgTitle, vMsg, vNewBinLoc)

' get qty to Move, can set default starting qty here.
vBinMoveQty = InputBox("How many Boxes", "Box Quantity to Move to New Bin Location", 1)


' add 2 rows to item details table. one to decrease total of old bin and one to increase total on new bin
' add bin qty record
DoCmd.RunSQL "INSERT INTO InvLinDtl(BinLoc, UPC-LabelId, Qty ) Values ('" & vNewBinLoc & "', '" & vUPCLabelId & "', '" & vBinMoveQty & "')"
' Subtract bin qty record
vBinMoveQty = vBinMoveQty * -1
DoCmd.RunSQL "INSERT INTO InvLinDtl(BinLoc, UPC-LabelId, Qty ) Values ('" & vOldBinLoc & "', '" & vUPCLabelId & "', '" & vBinMoveQty & "')"

End Sub

'-=-=-
 
You can use a variable and this to see/check the finished SQL:


Due to the inadvisable symbol in the field name, it would need to be bracketed.
 
By the way, if Qty is a numeric field as I would expect, you don't want the single quotes surrounding the value.
 
It was the 'dash' thank you. Amazing what the brain thinks the eyes see over and over again... lol

thanks a bunch!!!
 
Last edited:
Happy to help! I've had the same issue with my eyes; once you see it "wrong" one time, you'll never see it "right".
 

Users who are viewing this thread

Back
Top Bottom