Obtain ID from inserted record?

[news.microsoft.com]

Hi there,

I have a simple need. I need a way of obtaining the Autonumber that is
generated when a new row is added to a table.

I am using an "Event Procedure" to write a row to a table, and then I need
to obtain the AutoNumber that is generated, and use it to write addtional
rows to some more tables.

How best can I do this?

 

[Dirk Goldgar]

Hi there,

I have a simple need. I need a way of obtaining the Autonumber that is
generated when a new row is added to a table.

I am using an "Event Procedure" to write a row to a table, and then I need
to obtain the AutoNumber that is generated, and use it to write addtional
rows to some more tables.

How best can I do this?

That depends on how you're adding the new row. If you're using DAO, you can
do it like this:

Dim rs As DAO.Recordset
Dim varNewID As Variant

Set rs = CurrentDB.OpenRecordset( _
"SELECT * FROM YourTable WHERE 1=0")

With rs
.AddNew
!SomeField = SomeValue
!SomeOtherField = SomeOtherValue
.Update
.Bookmark = .LastModified
varNewID = !IDField
.Close
End With

 

[Lance]

use the dmax function on the autonumber field of that table. Will return the
highest value, which should be the last value added.

DMax("[Autonumber Field", "[Autonumber Table]")

 

[Dirk Goldgar]

use the dmax function on the autonumber field of that table. Will return
the
highest value, which should be the last value added.

DMax("[Autonumber Field", "[Autonumber Table]")

This is not a safe approach in general, as not all autonumbers are
consecutive. Random autonumbers may be used, or GUIDs. Also, in a
multiuser environment, even with consecutive autonumbers, the maximum
autonumber pulled by DMax may not be the one you added a moment ago.