Getting new record's identity (ID)

[John]

Hi

What is the correct syntax of an access insert query that also return the ID
(via @@Identity) of the new record?

Thanks

Regards

 

[Arvin Meyer [MVP]]

This should work:

Function GetID() As Variant
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'Whatever' AS Expr1;"

Set rst = db.OpenRecordset("SELECT @@IDENTITY AS LatestID;")
GetID = rst!LatestID

rst.Close
Set rst = Nothing
Set db = Nothing
End Function