runSQL statement

[CAP101]

I am trying this code to run an append query but I keep getting the error: syntax error in INSERT INTO statement. This is the statement i'm trying
DoCmd.RunSQL "INSERT INTO tblInventoryAdjustmentsArchive AdjustmentID, ProductID, MaterialsID, PackagingID, [Date], Comments, QtyOnHand, QtyCount SELECT tblInventoryAdjustment.AdjustmentID, tblInventoryAdjustment.ProductID, tblInventoryAdjustment.MaterialsID, tblInventoryAdjustment.PackagingID, tblInventoryAdjustment.Date, tblInventoryAdjustment.Comments, tblInventoryAdjustment.QtyOnHand, tblInventoryAdjustment.QtyCount FROM tblInventoryAdjustment;"

Any ideas?

 

[Rich]

Shouldn't there be brackets here
tblInventoryAdjustmentsArchive (AdjustmentID, ProductID, MaterialsID, PackagingID, [Date], Comments, QtyOnHand, QtyCount) SELECT ? and the whole statement as posted on one line.
HTH

[This message has been edited by Rich (edited 06-04-2001).]

 

[chrismcbride]

Three thoughts
1. This line...

"INSERT INTO tblInventoryAdjustmentsArchive AdjustmentID, ProductID,

should be...

"INSERT INTO tblInventoryAdjustmentsArchive.AdjustmentID, ProductID,

2. Any time I have a problem with errors in the syntax of a SQL string, I build the query in the QBD window, copy it to the module and format it as a string. If it works in the QBD and you format it properly, you should be fine

3. I don't know if it is better or worse, but I alway populate a recordset with any values that I want to insert using DoCmd.RunSQL. Then the Insert looks like...

DoCmd.RunSQL "INSERT INTO (tblInventoryAdjustmentsArchive.AdjustmentID, ProductID) VALUES ( & " rst!adjustmentID & ", " & rst!ProductID & ");"

...
You do have to be careful in the formatting of the string when you use recordset values and be sure to do adequate error handling..

HTH
Chris

 

[CAP101]

Thx very much. The parentheses were the missing ingredient.