Syntax error in Docmd.runsql command

[itsmystie]

Hi to all!

I've written the following SQL statement to save the values from an array into a table.

When I run the program I get syntax error.

I've tried using brackets for Values, but then it gave me some other error message like invalid use of '.', '!', '()' in query statement.

I've spent hours on it trying several possibilities but I simply can't figure out what's wrong.

DoCmd.RunSQL "INSERT INTO TempTable (CId,CName,CRoom,Amount) VALUES Clients(Element).CustomerID, Clients(Element).ClientSurname, Clients(Element).RoomNo, Clients(Element).Amount"

I also tried this:
DoCmd.RunSQL "INSERT INTO TempTable (CId,CName,CRoom,Amount) VALUES(" & Clients(Element).CustomerID & ",""" & Clients(Element).ClientSurname & """,""" & Clients(Element).RoomNo & """,""" & Clients(Element).Amount & """)"""

Using Access 03 VBA. Wrote the whole statement in one single line.

 

[CBrighton]

Try this:

DoCmd.RunSQL "INSERT INTO TempTable (CId,CName,CRoom,Amount) VALUES (" & Clients(Element).CustomerID & ", '" & Clients(Element).ClientSurname & "', " & Clients(Element).RoomNo & ", " & Clients(Element).Amount & ")"

The above assumes that ClientSurname is a text field, Amount is currency / numeric and CustomerID & RoomNo are numeric (note, the text field has 's, numeric /currency fields do not)

 

[itsmystie]

Great! Thanks a lot. It works perfect.

Just one more thing. The size of the array is 100.

I've used a For loop to insert the values. And it keeps giving me the message "You are about to append 1 row".

Any way not to get the message?

 

[CBrighton]

DoCmd.SetWarnings False
'Code to run without watrnings
DoCmd.SetWarnings True

Note the lack of = in the code.

Also, ensure you turn them back on again afterwards as this also disables things like the save dialog when closing an edited object and will assume you want to save changes, etc.