Help with Syntax error

[rreddy]

Hi Can anyone see whats going wrong with my syntax below. Is there some sort of software that can help drill down to the error area or something?

DoCmd.RunSQL ("INSERT INTO Transactions" & _
"([Date]," & _
"[OrderNo]," & _
"[ItemType]," & _
"[Type]," & _
"[Lot]," & _
"[Product ID]," & _
"[Qty]," & _
"[UOM]," & _
"[SD]," & _
"[Supplier]," & _
"[TransBy])" & _
"VALUES(Now()," _
"Forms![ProductionIssue].Form![TextOrderNo]," & _
"'Meat'," & _
"'Consumed'," & _
"Forms![ProductionIssue].Form![Combox1]," & _
"Labelx1.Caption," & _
"Forms![ProductionIssue].Form![Textx1]," & _
"Lc1.Caption," & _
"le1.Caption," & _
"lf1.Caption," & _
"CuserF());")

 

[way2bord]

I think you need a space before VALUES.

Try dumping the SQL into a string and debug.print it.

 

[JHB]

The problem is you are missing one "&" after "VALUES(Now()," _
correct is "VALUES(Now()," & _

And you are not getting the values from the different controls/fields in the form:
Your syntax is so:

"VALUES(Now()," _
"Forms![ProductionIssue].Form![TextOrderNo]," & _

To get the value from the control/field:

If number value then:
"VALUES(Now()," & _
"" & Forms![ProductionIssue].Form![TextOrderNo] & "" & "," & _

If text value then:
"VALUES(Now()," & _
"'" & Forms![ProductionIssue].Form![TextOrderNo] & "'" & "," & _

And the same for the other controls/fields: