Autonumber value of last inserted row - MS Access VBA

Question

I have a JET table with an auto-number as the primary key  and I would like to know how I can retrieve this number after inserting a row   I have thought of using MAX   to retrieve the row with the highest value  but am not sure how reliable this would be   Some sample code   Dim query As String Dim newRow As Integer query    INSERT INTO InvoiceNumbers  date  VALUES     amp  NOW    amp       newRow   CurrentDb Execute query    Now I know that this wouldn t work  since Execute   won t return the value of the primary key  but this is basically the kind of code I am looking for   I will need to use the primary key of the new row to update a number of rows in another table   What would be the simplest   most readable way of doing this

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

Both of the examples immediately above didn t work for me   Opening a recordset on the table and adding a record does work to add the record  except   myLong   CLng rs AutoNumberField    returns Null if put between rs AddNew and rs Update   If put after rs Update  it does return something  but it s always wrong  and always the same incorrect value   Looking at the table directly after adding the new record shows an autonumber field value different than the one returned by the above statement   myLong   DLookup  AutoNumberField   TableName   SomeCriteria     will work properly  as long as it s done after rs Update  and there are any other fields which can uniquely identify the record

User · Answer

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     amp  NOW    amp         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

[ms-access] Autonumber value of last inserted row - MS Access / VBA

Examples related to ms-access

Examples related to vba

Examples related to jet