How to: Get the Autonumber ID before update/save record

[Witchcraftz]

I wanted to get the autonumber ID before a record was saved to the table. My fields are on a form that is linked to the table. Maybe my solution is not the most elegant but it seems to work.

I messed around and came up with this solution: it creates the next record and captures the autoID then increments it and creates the record we will actually use.

Since we know the current autoID we know 100% the next will be the current+1

' Code by Witchcraftz
' Button event to add new record
Private Sub cmdAddRecord_Click()
    Dim strID As String
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
' Open database in this way to temporarily grab the next autonumber
    Set db = CurrentDb
    Set rs = db.OpenRecordset("myTable", , dbSeeChanges) 
    
' Create next record
    rs.AddNew
'We know the next record after this one will be the current record +1
    strID = CLng(rs("ID")) + 1
    
' Cleanup
    rs.Close
    db.Close
    
    Set rs = Nothing
    Set db = Nothing

' Create the current record in normal mode
    DoCmd.GoToRecord , , acNewRec
'Assign my autonumber value to my text field
    txtEmployeeNumber.Value = strID
    
End Sub

 

[KenHigg]

Just curious why you would need this?

 

[CJ_London]

Since we know the current autoID we know 100% the next will be the current+1

The answer to that is not 100% but - probably.

Just as an experiment - on a (new) test table enter a couple of rows - the autonumber field is probably 1 and 2. now for the third row enter some information - the autonumber is probably populated with 3, but before going to enter another record hit esc and the record entry is cancelled. So now add a new record - what is the autonumber?

in addition take a look at these links

http://vb123.blogspot.co.uk/2013/01/the-autonumber-goes-crazy-fix.html

http://allenbrowne.com/ser-40.html

http://access.mvps.org/access/general/gen0025.htm