Retrieve latest AutoNumber from table after DAO AddNew record

shambalad
shambalad used Ask the Experts™
on
I am using DAO to write new records to a table where the primary key is an AutoNumber type field. After executing the .AddNew, .Update. I would like to retrieve the primary key for the record that was just added. What is the best way to do this? I am working in Access 2007.
Thanks,
Todd
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
After the update, do:

  rec.bookmark = rec.lastmodified
  lngLastKey = rec.<key field>

JimD.
Top Expert 2011
Commented:
Be sure to add the Primary key field to the recordset.

before the .Update use:   lngLastPrimaryKey = rs!PrimaryKeyFieldName

 

Author

Commented:
Both suggestions work, but I prefer getting the key while the record is being updated as in the second solution.
Thanks,
Todd
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Todd,

  Just be aware that the second solution will not longer work if you move the data to SQL Server.

JimD.
Top Expert 2011

Commented:
JimD

Great point about SQL Server.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial