Trouble with INSERT INTO Syntax

[Joe Y]

I created a button, when clicked, supposed to create a new record on a different form which is bound to table ‘TProduct’.

Values from these text boxes are to be inserted in to new record in Tproduct.

[Txt_ID]
[txt_Version]
[txt_Total_Cost] – a calculated field
[txt_RecipeState ment] – a calculated field

In addition to these fields update, Vendor ID and Vendor Location are fixed as 10000 and NYC if new record is created this way.

I am having trouble with INSERT INTO statement (Syntax Error). When debugged, Access highlighted entire INSERT INTO phrase.

Can someone help me to correct the syntax? Thanks in advance.

Below is my code.

 Dim dbs As Database
Set dbs = CurrentDb
 
Dim strRID As String
Dim strRver As String
Dim strCost As Currency
Dim strStatement As String
 
 
strRID = Me.[Txt_ID]
strRver = Me.[txt_Version]
strCost = Me.[txt_Total_Cost]
strStatement = Me.[txt_RecipeStatement]
 
MsgBox [strRID]
MsgBox [strCost]
MsgBox [strRver]
MsgBox [strStatement]
  
dbs.Execute " INSERT INTO TProduct " _
& "(productID, ProductVersion, unit$, spec, vendorID, VendorLocation) VALUES " _
& "('" & strRID & "' , '" & strRver & "', '" & strCost & "', '" & strStatement & "', '10000', 'NYC');"
                 
dbs.Close

[Rabbit]

If I had to guess, it's probably the unit$ field. I would rename it if I were you.

[Joe Y]

Yes, you are right. I removed the unit$ from the INSERT INTO phrase and the code worked just fine. Thanks for the hint.

Now I have to rename the field and update all related links.

Joe