Autonumber value of last inserted row - MS Access / VBA

If DAO use

RS.Move 0, RS.LastModified
lngID = RS!AutoNumberFieldName

If ADO use

cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value

cn being a valid ADO connection, @@Identity will return the last Identity (Autonumber) inserted on this connection.

Note that @@Identity might be troublesome because the last generated value may not be the one you are interested in. For the Access database engine, consider a VIEW that joins two tables, both of which have the IDENTITY property, and you INSERT INTO the VIEW. For SQL Server, consider if there are triggers that in turn insert records into another table that also has the IDENTITY property.

BTW DMax would not work as if someone else inserts a record just after you've inserted one but before your Dmax function finishes excecuting, then you would get their record.

Private Function addInsert(Media As String, pagesOut As Integer) As Long


    Set rst = db.OpenRecordset("tblenccomponent")
    With rst
        .AddNew
        !LeafletCode = LeafletCode
        !LeafletName = LeafletName
        !UNCPath = "somePath\" + LeafletCode + ".xml"
        !Media = Media
        !CustomerID = cboCustomerID.Column(0)
        !PagesIn = PagesIn
        !pagesOut = pagesOut
        addInsert = CLng(rst!enclosureID) 'ID is passed back to calling routine
        .Update
    End With
    rst.Close

End Function

In your example, because you use CurrentDB to execute your INSERT you've made it harder for yourself. Instead, this will work:

  Dim query As String
  Dim newRow As Long  ' note change of data type
  Dim db As DAO.Database

  query = "INSERT INTO InvoiceNumbers (date) VALUES (" & NOW() & ");"
  Set db = CurrentDB
  db.Execute(query)
  newRow = db.OpenRecordset("SELECT @@IDENTITY")(0)
  Set db = Nothing

I used to do INSERTs by opening an AddOnly recordset and picking up the ID from there, but this here is a lot more efficient. And note that it doesn't require ADO.

This is an adaptation from my code for you. I was inspired from developpez.com (Look in the page for : "Pour insérer des données, vaut-il mieux passer par un RecordSet ou par une requête de type INSERT ?"). They explain (with a little French). This way is much faster than the one upper. In the example, this way was 37 times faster. Try it.

Const tableName As String = "InvoiceNumbers"
Const columnIdName As String = "??"
Const columnDateName As String = "date"

Dim rsTable As DAO.recordSet
Dim recordId as long

Set rsTable = CurrentDb.OpenRecordset(tableName)
Call rsTable .AddNew
recordId = CLng(rsTable (columnIdName)) ' Save your Id in a variable
rsTable (columnDateName) = Now()        ' Store your data
rsTable .Update

recordSet.Close

LeCygne