Finding record ID of record just added

[Neil]

Hello All,

When adding a record (either through code or using an append query), is
there a way to find out the Primary Key field (Autonumber) that is being
added without having to do a search once the record has been added?

TIA,

Neil.

 

[Ken Snell [MVP]]

In code, assuming that you have a recordset variable (rstRecordset in my
example) set to the table/query to which the record is being appended), you
can use the .LastModified property to move the recordset to that record and
then read its primary key value:

rstRecordset.Bookmark = rstRecordset.LastModified
JustAddedPrimaryKeyValue = rstRecordset.PrimaryKeyFieldName.Value

 

[Neil]

Thanks Ken,

So if i am adding a record in the same sub, it would look something like:


rstRecordset.AddNew
' rstFields to add data too go here
rstRecordset.Update
' Find the ID of the record just added
rstRecordset.Bookmark = rstRecordset.LastModified
JustAddedPrimaryKeyValue = rstRecordset.PrimaryKeyFieldName.Value

I am assuming that all this would happen pritty quick, so the chances of 2
people adding a record at the same time and then the LastModified method
returning the wrong value would be pritty small?

Neil.

 

[Brendan Reynolds]

I believe the LastModified property returns the last record modified *via
that recordset*. I'm relying on memory here, because the help file is not
very specific, but it does say "A record must be added or modified in the
Recordset object itself in order for the LastModified property to have a
value", which seems to tie in with what I remember.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Ken Snell [MVP]]

I concur with Brendan. You should be ok with the code that you posted.
--

Ken Snell
<MS ACCESS MVP>

 

[PC Datasheet]

Since your primary key is autonumber, the last record added will have the
highest value for the primary key field.

MyRecordID = DMax("MyPrimaryKeyField","MyTable")

 

[Neil]

I looked into the .LastModified property in the help files just after I
posted the question and read about it just being the recordset object
itself.. At least this has confirmed it.

Thanks for your help,

Neil.

 

[Neil]

Thanks PC,

I was going to do it that way originally, but I needed the ID to use as a
foreign key value when adding child records in the same sub. I was trying to
find a way to ensure that 2 users couldn't do an insert at the same time
(which *may* of happened if i searched for the maximum ID). The
..LastModified way seems to do the trick.

Neil.