Selecting autonumber after inserting

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

Guest

Hi

I'm inserting into a database using stored queries and the
primary is an autonumber therefore I do not set this value.
I want to do one query which allows me to retrieve the
primary key directly after inserting a new record
otherwise I cannot identify this record once it's inserted.

Can anyone help?
Thanks in advance.
 
Dear Rona:

Your insert must add only one record for this to work.

You will need to insert the row using a recordset object rather than a
query.

rst.AddNew
rst!ColumnName1 = value
rst!ColumnName2 = value
rst.Update

LastModified is a bookmark to the new row. Move the recordset to that
bookmark and retrieve the value from the autonumber column.

rst.Bookmark = rst.LastModified
AutoNumberValue = rst!AutoNumberColumnName

You first must declare the recordset and open it to your table or
query:

Dim rst As Recordset
CurrentDB.OpenRecordset("TableName, SavedQueryName, or SQL")

You may need some study to get these features working.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top