INSERT into table with Auto Number?

[intern42]

I'm trying to run an INSERT statement on a form and it doesn't work...

I've done this same thing on several other forms with no problem, the only difference is with this INSERT, I'm inserting into a table who's primary key is an auto number.

So for the INSERT, I only listed the other columns.

I get no message that the INSERT didn't work, but I put a message box both before and after the doCmd RunSQL command and the first box pops up, but not the second.

My guess is that it's something to do with the auto number column, but I don't get any kind of a warning or error message.

Any ideas?

 

[jrbarnett]

Something to check:

with an SQL statement in which you aren't writing to every record, you must specify the columns, thus:

Insert into Table (Field1, Field2, Field3)
Values (1, 2, 3)

If the field is an autonumber you don't have to put it in the list in the top row.

John

 

[intern42]

I checked that, and unfortunately, I matched everything up.

Here's the SQL command:

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

The only field I didn't include is the auto number field.

For some reason this hangs up the program.
I don't get an error message, I don't go into the debugger,
it just doesn't get past that statement.

As I said, I bookended that statement with message boxes and only the first message box pops up.

Something in that statement screws up the system.

No error, no warning, no debugger, but the 2nd message box never pops up.

I assumed that it had something to do with INSERTing a row into a table with an auto number as the primary key.

Should Access/VBA automatically assign the auto-number PK when I run this statement, or am I missing something.

Any help is greatly appreciated.

 

[jrbarnett]

Try

Dim strSQL as string

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

and see what you get

 

[intern42]

Same thing.

It displays all message boxes BEFORE the DoCmd.RunSQL command, but nothing AFTER that command.

Weird.

 

[intern42]

OK, I forgot that I set an error trap... that's why I didn't get an error message.

It's a syntax error in the INSERT INTO statement:

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

I broke it up so that it's easier to read, but the entire sting is on one line in the code.

The table I'm inserting into is called "CoursesDates"
and has the following fields:

coursesDatesID: auto number
courseID: Number
dateOffered: date/time Input mask 99/99/0000;0;_
time: date/time Input mask 00:00;0;_
hours: number
seats: number



I'm thinking now that it has something to do with inserting the time... is there some special formatting I need to put around it so that Access knows it's a time?

Thanks