Just-added Record Key

[Guest]

Hi -

I have an Access table with an Autonumber primary key field. How can I
get the value of that primary key for a just-added record?

(I opened the table as a dynaset, then used rs.AddNew -> rs.Update to
add a new record. After the update, the just-added record is not the
current record.)

Thanks for your help.

- Jeff

 

[Allen Browne]

Access assigns the value before the record is saved, so before the rs.Update
line, you can read the ID field:
Debug.Print rs!ID

After the save, you could:
rs.Bookmark = rs.LastModified
Debug.Print rs!ID

If you add a record to a JET4 table (Access 2000 and later) using an Append
query, you can get the primary key value like this:

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

 

[Graham R Seach]

Jeff,

Since you added the record using a recordset, you can get at the PK just
before you get to the Update method:
rs.AddNew
rs!xyz=123
MsgBox rs!PrimaryKey
rs.Update

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia