Syntax error in INSERT INTO statement

[mrtn]

Hi All

I am having a problem with below and getting a run-time error 3134

LastOrderNumber = DMax("Order", "Model_types")
NewOrderNumber = CLng(LastOrderNumber + 1)

CurrentDb.Execute "INSERT INTO Model_types (Order) " _
            & "VALUES (" & NewOrderNumber & ")"

The field 'Order' in Model_types is a Long Integer.

Any help would be appreciated.

 

[pr2-eugin]

What does the error description say?

Show the Dim declaration for NewOrderNumber and LastOrderNumber.

Remember DMax can return a Null value. So CLng(Null) will come up with error. To avoid this use Nz function.

 

[mrtn]

I am getting the following

Run-time error '3134':

Syntax error in INSERT INTO statement


and my Dim declarations are as follows:

Dim LastOrderNumber As Long
Dim NewOrderNumber As Long

To be fair it's not working only with this particular field - Order, when I tried to insert into different columns it works fine...

 

[pr2-eugin]

Is order by any chance a AutoNumber type? If yes, that is the answer to your problem. You cannot add a number to an AutoNumber field. Access assigns a value to that. If it is not a AutoNumber but is a Primary Key make sure the number does not exist in the table.

 

[mrtn]

It appears that the word 'Order' is reserved and I have had to use brackets...

Thanks for your help!