Grabbing Autonumber before update

[thisisntwally]

according to wayne

Stuart,

    With rs
        .Open "Contracting Agency List", cn, adOpenStatic, adLockPessimistic 'open it
        .AddNew
        .Fields("Vendor") = CompanyName
'
' Right here .Fields("YourAutoNumberField") has the proper value
' after the .Update, its value will be wrong.
'
        .Update 'update the table
        .Close 'close the recordset connection
    End With

Wayne

This was exactly what I was looking for. So i did it like so:

Dim db As DAO.Database
Dim tblSubProj As DAO.Recordset
Set db = CurrentDb
Set tblSubProj = db.OpenRecordset("tblSubProj", dbOpenDynaset)
    With tblSubProj

       .AddNew
        !Date = Now
        !ABRID = Forms!frmMaster!frmBNR!txtABRID
        !ProjectTitle = Me.txtPT
        Me.txtSPID = !SPID   'this is where I grab the PK except...
       
       .Update

Lo it grabs a PK value from the table, but the value has been randomly selected from the middle records. Each time I try it, the record increases by one. Additionally, it seems that this number is being used to make the new record, which is then rejected as it would create a duplicate PK value.

Whats going on here? Maybe this is no longer supported?

 

[thisisntwally]

Well I just kept clicking submit until the number reached new numbers. No idea why it chose to randomly start with record 15, but now that we are caught up, this works.


So I say solved! YOU CAN GRAB AUTONUMBERS!