DoCmd.RunSQL Insert Into tbl with autonumber? 1

[SiberBob]

I have a question about doing an INSERT INTO in a table with an autonumber field in the table. Do I need to fill that field somehow or will it automatically be filled when the record is created?

I am getting an error on the following code:

strMessageOperator = Environ("UserName")
strMessageDate = Format(Date, "Short Date")
strMessageTime = Format(Now, "Long Time")
strMessageNumber = (DCount("MessageNumber", "MessageNumbers", "Month([MessageDate]) like Month(Now)") + 1)

strSqlStatement = "INSERT INTO messagenumbers (MessageOperator, MessageDate, MessageTime, MessageNumber) Value ('" & _
                strMessageOperator & "',#" & strMessageDate & "#,#" & strMessageTime & "#,'" & strMessageNumber & "')"
DoCmd.RunSQL strSqlStatement

MessageOperator, MessageDate, MessageTime, MessageNumber are all fields in the MessageNumbers table.

I have tried the same sqlstatement with only one field and one value and while testing that, I tried it with and without the ' delimiter around the variable in the values section but still keep getting the "Syntax error in INSERT INTO statement" error.

Any thoughts?

 

[RoyVidar]

It should be "filled"/incremented or what you'd like to call it, when you don't specify it (the autonumber field) - so in other words, do not specify the autonumber.

Try also using Values not just value.

The messagenumber is numeric? If so, remove the single quotes. You might have challenges with your dates - use "yyyy-mm-dd" format in stead of short date.

Roy-Vidar

 

[SiberBob]

changing from Value to Values worked

Thanks! Sometimes I just can't see the forest for the trees...