After table INSERT need to get the autonum key used

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

After table INSERT, I need to get the autonum key used
How
I'm using Docmd.RunSQL to do the INSERT
 
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
 
Back
Top