How to retrieve id of last inserted record in Access without using @@identity?

[J.S.]

In SQL server one can use @@Identity to return the id of the last inserted
record (although there are situations when it is not appropriate. What is
the best way to insert the id of the last inserted record in the last 2-3
versions of Access?

Thanks,
J.S.

--

 

[J.S.]

It looks like I mistated my question earlier. I would like to retrieve the
id of the last inserted record in an Access database table. I'd appreciate
any pointers in this regard.

 

[PC Datasheet]

Be Careful!

This solution gives you the last record in the recordset. It may or may not
be the last record entered depending on if the table was filtered and saved.
A safer solution is to use Max rather than Last. With Max you will always
get the last record entered because the last record entered always has the
highest ID if you are using autonumbers.

 

[tina]

With Max you will always

get the last record entered because the last record entered always has the
highest ID if you are using autonumbers.

that should hold true if the Autonumber field's NewValues property is set to
Increment, but not necessarily if the NewValues property is set to Random.

hth

 

[Peter R. Fletcher]

In fact, it is guaranteed _not_ to work with a Random AutoNumber
primary key (the more records in the Table, the lower the probability
that a random new key will be greater than the highest previous one)!
I think that the best way to handle the OP's problem is to have an
otherwise unreferenced date field in the Table with a Default value of
Now(). The most recently entered record will always be the one with
the maximum value in this field.

that should hold true if the Autonumber field's NewValues property is set to
Increment, but not necessarily if the NewValues property is set to Random.

hth

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Eric D via AccessMonster.com]

This is true... but I would think that a user who doesn't yet know how to
find last record entered would not know how to set Autonumbering to Random.

With what little information was provided, my solution addressed the issue.

With Max you will always
get the last record entered because the last record entered always has the
highest ID if you are using autonumbers.

that should hold true if the Autonumber field's NewValues property is set to
Increment, but not necessarily if the NewValues property is set to Random.

hth

Be Careful!

[quoted text clipped - 22 lines]

 

[Marshall Barton]

This is true... but I would think that a user who doesn't yet know how to
find last record entered would not know how to set Autonumbering to Random.

With what little information was provided, my solution addressed the issue.

Come on folks, an AutoNumber PK field can not be relied on
to be anything other than unique. The AutoNumber value can
go negative if enough records are added, and there are
times(?) when Access might decide(?) to change it from
increment to random.

The date/time field is clearly the easiest way to reliably
identify the order that records were entered. Given that,
one way to get the latest record is:

SELECT TOP 1 <field list>
FROM <thetable>
ORDER BY <datefield> DESC

A way to lookup the PK field of the latest record:

DLookup("PK", "thetable", "datefield = " &
Format(DMax("datefield", "thetable"), "\#m\/d\/yyyy\#"))

but that is pretty ugly and will be slow if done frequently.

 

[tina]

I think that the best way to handle the OP's problem is to have an

otherwise unreferenced date field in the Table with a Default value of
Now(). The most recently entered record will always be the one with
the maximum value in this field.

i agree with you, Peter, i would use a date field with a programmatically
assigned value, too. but i probably wouldn't use the DefaultValue to assign
it. in a data entry form, as soon as the user saves a record and the cursor
goes to the next "new" record, the default value is set. but the user may
not actually begin to enter data in the new record for minutes, or hours. in
the meantime, any number of additional new records may be entered and saved
by other users.

i usually add date/time stamps to records on the form's BeforeUpdate event,
using an If statement to test for NewRecord. that's not 100% foolproof
either, of course; this is essentially the same issue we face when creating
an incremental unique value (like a purchase order number) for a record at
point-of-entry, in a multi-user environment. the issue has been debated in
these newsgroups numerous times, but i've never seen anyone post a foolproof
solution. <g>

In fact, it is guaranteed _not_ to work with a Random AutoNumber
primary key (the more records in the Table, the lower the probability
that a random new key will be greater than the highest previous one)!
I think that the best way to handle the OP's problem is to have an
otherwise unreferenced date field in the Table with a Default value of
Now(). The most recently entered record will always be the one with
the maximum value in this field.

 

[Peter R. Fletcher]

i agree with you, Peter, i would use a date field with a programmatically
assigned value, too. but i probably wouldn't use the DefaultValue to assign
it. in a data entry form, as soon as the user saves a record and the cursor
goes to the next "new" record, the default value is set. but the user may
not actually begin to enter data in the new record for minutes, or hours. in
the meantime, any number of additional new records may be entered and saved
by other users.

This may be true in theory, but I am not sure how important it usually
is in practice, If you have a multi-user application in which order of
entries/events is of critical importance, you will need to address the
issue in the light of the specific requirements of the application.
The counter-example to your approach in the general case is a user who
fills in a form and then goes away without saving it or navigating
away - only coming back later to save. Having the CreationDate field
defaulted to Now() in the Table structure ensures that, however the
record is created, its creation date is correctly (or at least
predictably) recorded.

i usually add date/time stamps to records on the form's BeforeUpdate event,
using an If statement to test for NewRecord. that's not 100% foolproof
either, of course; this is essentially the same issue we face when creating
an incremental unique value (like a purchase order number) for a record at
point-of-entry, in a multi-user environment. the issue has been debated in
these newsgroups numerous times, but i've never seen anyone post a foolproof
solution. <g>

I generally have two date-recording fields in my major Tables one for
creation date, set as I indicated, and one for last modification date,
set in the controlling Form's BeforeUpdate Event.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[J.S.]

Thanks, Eric! I appreciate your help.

Best,
J.S.

 

[J.S.]

Thanks for your response, Marshall!

Best,
J.S.

 

[J.S.]

Thanks!

J.S.

 

[J.S.]

Tina,

I am using Increment and not Random.

Thanks,
J.S.

--