• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Returning the Autonumber Value when adding records

Returning the Autonumber Value when adding records

When using Autonumber fields in a database for a UID, you might need this value after you add the record, for that record. This is my example of how to get that value back from the database after it is added using DAO or ADO.

Assumes
This is not a complete application, just code snippets. I expect that anyone using this knows that they have to set references to DAO and ADO, and know how to connect to a database using these objects.

Side Effects
none.

Rate Returning the Autonumber Value when adding records

• 1
• 2
• 3
• 4
• 5

(7(7 Vote))

'DAO Example  
'First Open a updateable recordset
Set rs = db.OpenRecordset("SomeTable")
  With rs
    'Start a New Record
    .AddNew
      !Field2 = "Add your data for this new record" 
    'Add the record to the database
    .Update
  
    'Set the bookmark to Last modified
    .Bookmark = .LastModified
    
    lngResult = rs!AutoNumberUID
  End With
  
  rs.Close
'Ado Example
  Set mrsMDB = New ADODB.Recordset
  
  mrsMDB.CursorType = adOpenKeyset
  mrsMDB.LockType = adLockOptimistic
  mrsMDB.Open "SomeTable", mcnnMDB, , , adCmdTable
      
  With mrsMDB
    .AddNew
    !Field2 = "Add your Data for this record"
    .Update
    varBkMark = .Bookmark
    .Requery
    .Bookmark = varBkMark
    lngNewUID = !AutoNumberUID
    
  End With