Problem retrieving @@IDENTITY in Form AfterInsert-event

[jon_s]

Hello all!

After some initial attempts to use SCOPE_IDENTITY() with Access internal DB-engine, I have realized that Acess does not support this function.

So I have to use the @@IDENTITY to get that latest AutoIncrement PK of a table that is updated by a form.

Unfortunately, I dont know what object to use to get my hands on the @@IDENTITY that was generated by the forms insert. I have tried the code below (in the AfterInsert-event of the form that inserts new rows)

    Dim lpRowIdRecordSet As DAO.recordSet
    Set lpRowIdRecordSet = CurrentDb.OpenRecordset("SELECT @@IDENTITY AS row_id;")
MsgBox "After insert @@IDENTITY = " & lpRowIdRecordSet.Fields("row_id").Value

It executes without errors, but always returns zero, problably because the the object used by the form for posting the insert is not the same as CurrentDB.OpenRecordSet.

Any ideas?

 

[vbaInet]

You don't need to create a new thread for this. Just follow-up your original thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=203876

 

[jon_s]

Ok.

My motivation for starting a new thread was that the original issue was not really relevant any longer and that I now actually have a quiet different issue. Having two threads would IMHO sort these two issues apart better for people only reading the thread title or people doing searches.