retrieve id of row just created

[Phil Hellmuth]

Using the AddNew Method, I'm adding a row into a table (linked via
SQLServer). I want to capture the ID of the row I just added, but I'm
actually getting the ID from the first row in the table. Here's a
simulation of what I'm doing:

set rs = db.OpenRecordset("Students", dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs("StudentName") = "Joe Smith"
rs.Update
lngID = rs("StudentID")
rs.Close

lngID is the ID of the first row in the table, not the ID of the row I
just added.

I could have sworn I've done this successfully in the past. Is the
dbOpenDynaset option throwing me off? I added it because it seemed
dbSeeChanges required it, and the existence of an Identity column forced
me to use the dbSeeChanges option.

Thanks in advance.

 

[Allen Browne]

After the rs.Update, tell the Recordset to make the newly added row the
current one:
rs.Bookmark = rs.LastModified

 

[Phil Hellmuth]

After the rs.Update, tell the Recordset to make the newly added row the
current one:
rs.Bookmark = rs.LastModified

Worked like a charm. Thanks!

 

[Tim Ferguson]

set rs = db.OpenRecordset("Students", dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs("StudentName") = "Joe Smith"
rs.Update
lngID = rs("StudentID")
rs.Close

or else move the retreive statement to before the update:

rs.AddNew
rs("StudentName") = "Joe Smith"
lngID = rs("StudentID")
rs.Update

Hope that helps


Tim F

 

[Allen Browne]

Tim, I don't believe that will work for SQL Server tables.

It works in Access (JET) tables, because the AutoNumber is assigned as soon
as you start to add a new record, but the timing is different in SQL Server.