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).
|