Finding ID of record added to recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I used this code under Access 97 to get the id (autonumber) of an added record:
rsC.AddNew
rsC("LastName") = rs.Fields("Lastname")
rsC("FirstName") = rs.Fields("Firstname")
rsC.Update
rsC.Bookmark = rsC.LastModified <=== ???
CreateClient = rsC.Fields("ID")

But under Access 2000, it does not like the .LastModified property
How do I do this ???
 
Hi bugs:

We all know that the new record is placed at the end of a recordset, no?
Then why not simply do this:
rsC.AddNew
rsC("LastName") = rs.Fields("Lastname")
rsC("FirstName") = rs.Fields("Firstname")
rsC.Update
rsC.MoveLast
Click to expand...

Now you have more time to fix the other little bugs...

Regards,
Al.
 
In DAO, the new autonumber value is created and assigned to the field the
moment the AddNew method is executed, so you don't need to use the bookmark:

rsC.AddNew
CreateClient = rsC.Fields("ID")
...
rsC.Update

Besides that, LastModified should be available in Access 2000. Are you sure
that you are not inadvertently using an ADO recordset instead of DAO? Try
explicitly declaring rsC as DAO.Recordset and/or check your project
references.
 
There's no need to move to the last record, the Primary Key is available as
soon as you execute the AddNew method.

rsC.AddNew
Debug.Print rsC!PersonID '*********
rsC("LastName") = rs!Lastname
rsC("FirstName") = rs!Firstname
rsC.Update

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Great! Thanks. Also, since this is a multi-user system, the last record may not be the one I added.
- David
 
But since you have a dedicated connection to the record you're about to
enter, the PK value will be correct, regardless of what any other user is
doing. Certainly if you were to requery the table, you might get someone
else's PK.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top