After table INSERT need to get the autonum key used

[David]

After table INSERT, I need to get the autonum key used
How
I'm using Docmd.RunSQL to do the INSERT

 

[Allen Browne]

Access does not expose the key value(s) after an INSERT.

If no other users or processes are adding records, it may okay to just use
DMax("ID", "MyTable")

However, the safer approach would be to use DAO to append the new record.
This kind of thing:

Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset("MyTable", _
dbOpenDynaset, dbAppendOnly)
rs.AddNew
!SomeField = somevalue
!AnotherField = anothervalue
'etc for other fields.
rs.Update
rs.Bookmark = rs.LastModified
Debug.Print "The new ID is " & rs!ID
rs.Close
Set rs = Nothing