View Single Post
 
Old October 2nd, 2006, 10:52 AM
Steve777 Steve777 is offline
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to retrieve ID field of newly-created record

I'm adding a new user to my database as follows:

--------------------------------

strSQL = "SELECT * FROM Users"
Set rsData = GetRecordset(strSQL, adOpenStatic, adLockPessimistic)

rsData.AddNew

rsData("UserLogin") = userLogin
rsData("UserPassword") = userPassword

rsData.Update

--------------------------------

However, the form which submitted the user data includes data that is to be written to a *second* table for this new user.

My problem: How can I write to this other table since I do not (yet) know the UserID (which is the primary key, and auto-generated by the database) of the newly-created user in the Users table?

I want to do this all in one step (on the same page) but it seems impossible since in order to create the new user, I must update the recordset, but as soon as I do that, I have no way of knowing what UserID the database has assigned to it.

In the past, I have got round this by manually assigning the next highest UserID to the new user (i.e. UserID not auto-generated). But this seems clumsy.

Is there a smarter way?

(Note: I am not using stored procedures at all and do not intend to do so for this project).