retrieve id of row just created

  • Thread starter Thread starter Phil Hellmuth
  • Start date Start date
P

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.
 
After the rs.Update, tell the Recordset to make the newly added row the
current one:
rs.Bookmark = rs.LastModified
 
Allen said:
After the rs.Update, tell the Recordset to make the newly added row the
current one:
rs.Bookmark = rs.LastModified
Click to expand...

Worked like a charm. Thanks!
 
set rs = db.OpenRecordset("Students", dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs("StudentName") = "Joe Smith"
rs.Update
lngID = rs("StudentID")
rs.Close
Click to expand...

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
 
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.
 
Back
Top