INSERT into table with Auto Number

[intern42]

I'm relatively new to Access/VBA and am trying to do an INSERT command into a table that has an auto number for a primary key.

As this value is an auto number, I'd like Access to chose it for me.
However, as this is the PK, and it's a required field, I believe Access won't let me do an INSERT unless I specify it...

So here's my INSERT statement:

DoCmd.RunSQL _
"INSERT INTO CoursesDates
(courseID, dateOffered, time, hours, seats)
VALUES (" & Me!cboCourse & ", #" & Me!txtDateOffered & "#,
" & Me!txtTime & ", " & Me!txtHours & ", " & Me!txtSeats & ")"

(I broke this up for readability, but in the code the entire sting is on one line)

The table I'm inserting into has only ONE other field: CoursesDatesID, which is an auto number.

Running this code generates a syntax error.
I've run other INSERT queries that worked, the only difference being that for the working queries, I was able to specify ALL fields, whereas for this query I need Access to provide the number automatically.

Any thoughts?

Thanks in advance!

 

[RV]

>Me!cboCourse <

Are you referring to a combobox here?

RV

 

[Pat Hartman]

Normally you would not want to specify an autonumber so you should omit it from the append query. Access will assign it automatically.

DoCmd.RunSQL _
"INSERT INTO CoursesDates
(dateOffered, time, hours, seats)
VALUES (#" & Me!txtDateOffered & "#,
" & Me!txtTime & ", " & Me!txtHours & ", " & Me!txtSeats & ")"

The only time you would want to specify a value for an autonumber is when you are converting data from another source and you have related records so you need to maintain the RI between the two tables.

 

[Jon K]

I think the problem is caused by the fact that you have used the function name Time for a field name. So Access is confused. Try putting the field name inside a pair of square brackets in your code, i.e. [time]


Note: You have correctly surrounded the inputted date with the # signs.

If any of the courseID, time, hours, seats fields happens to be a text field, the value need to be surrounded with the single quotes as well.

e.g. if courseID is a text field, use:-
VALUES ('" & Me!cboCourse & "', #" & Me!txtDateOffered & "#,

 

[intern42]

Thanks guys!

It was the "time" field that did it.

The brackets worked, but I also changed the name.

Damn those pesky reserve words!!

 

[Pat Hartman]

Did you also remove the autonumber?

 

[intern42]

I actually never had the auto number included; it was the only field I left out (coursesDatesID)

I erroneously thought that leaving it out was the cause of the error, as I read that when using an INSERT statement in Access, you cannot leave out any field labeled as "required."

Guess an auto number field is the exception to that rule.

Thanks again for the help... I have no idea how long it would have taken me to figure out that "time" was a reserved word.