How to carture the newly autonumber in the primary field?

[Paul]

In a one to many table relationship, an "Insert" statement to the "one"
table would craete a new autonumber for the primary key on a row for the
"one" table and this autonumber will then be used as secondary key on the
"many" table. What I need is to retrieve that autonumber right after the
"Insert" statement and show it to a textbox. Say I have the following codes:

Docmd.RunSql "Insert Into Table1...bha bha bha"
Me.Autonumber = newly created autonumber

Should I use the DMAX function to capture the max. Autonumber or something I
can embedded in the "Insert" statement to capture that autonumber number and
save it as a variable to the cache?
Thanks

 

[Allen Browne]

Here's an example of how to retrieve the newly assigned autonumber value
from JET tables after executing an INSERT query:

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