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

Have a look at:

http://www.kamath.com/tutorials/tut007_identity.asp

Might be of some use.
Cheers,
Matt.

A stored procedure would be best in this secnario (as you could handle all data with one call), IMHO, but since you aren't you will have to make 2 calls to the database before you can work with the second table:

//INSERT Statement

Then assign this value to a variable and you can work with the second table.
SELECT max(primary_key) from table1

I agree that this is a bit clumsy but since you don't intend to use SP's I dont really see any alternative.





--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

Mattallyc - fantastic. That works. Thank you!

For the record, here is what I added:

strSQL = "SELECT @@IDENTITY AS NewID"
Set rsData = dcnDB.Execute(strSQL)
userID = rsData.Fields("NewID").value
rsData.Close
Set rsData = nothing