AutoNumber field in a form Access 2007.

[BMF]

Howdy,

I am mainly a MySQL user but I have been playing with Access 07 a little bit recently. I created a database with 3 tables.

Table ID ---
userID -- AutoNumber
UserInfo - userID (linked to ID.userID
fname -- text
lname -- text
age -- number
Usermoreinfo - userID (linked to ID.userID)
college -- text

So i made a form using the 'form wizard' and i assumed that because userID is an autonumber field it would've just automagically inserted a row in that table/field first because everything else relies upon that. I was wrong, so i created a second form with all of the above fields.

So now i have a form with:

userID
fname lname
age
college

it wont let me do inserts because it tells me userID is an autonumber field if i enter anything in that text box, if i dont enter anything in that text box it tells me the primary key cant be NULL... kind of a catch22 huh??

can anyone explain this to me? i could do this with MySQL in two queries by just getting the last insert id but i'm trying to do something different/new.

thanks for any advice.
BMF

[hjozinovic]

hi BMF!

You seem to have a problem with ID table. Basically it's not needed! You should put the info about one entity into only one table. (User is an entity for itself).
Try to change your tables as I did in Test file attached to this reply.
I hope this design will be acceptable for you.

I'll be here for future questions you might have.
H.