saving data from form to table in access

Question

I have a form, and I want to fill it, and then save some of the fields into an existing table called Order. I'm trying to do this with this line of code:

CurrentDb.Execute "INSERT INTO Order (OrderNumber)" & " VALUES (' " & Me.order & " ')"

I have also tried it like this

CurrentDb.Execute "INSERT INTO Order (OrderNumber)" & " VALUES ( " & Me.order & " )"

but it doesn't seem to make a difference. I keep getting the following error:

run-time error '3134': syntax error in INSERT INTO statement.

what am I doing wrong?

Answer 1

Order is a reserved word. If you must keep that as the table name, bracket it to avoid confusing the db engine.

Dim strInsert As String
strInsert = "INSERT INTO [Order] (OrderNumber) VALUES ('" & Me.order & "')"
Debug.Print strInsert
CurrentDb.Execute strInsert, dbFailOnError

If OrderNumber is numeric data type instead of text, discard those single quotes from the INSERT statement.

Store your statement in a string variable. Then use Debug.Print to examine the completed statement you're asking the engine to execute. You can view the Debug.Print output in the Immediate window. Go there with Ctrl+g Copy the statement and paste it into SQL View of a new Access query for troubleshooting.