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.
Original Author: James N. Wink
Assumptions
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.
Code
'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