Finding Autonumber ID for new record in Access 2007

0
votes

The question I asked over here was actually a two-part question. I accepted an answer that was perfect for the first part, but I'm still stumped on the second.

I've got a form tied to a table. The user (still just me, in develop mode, but still...), enters a new record in the form. How do I get the (Autonumber) ID of that record? Even after forcing a save, it doesn't seem to be anywhere I know how to find it.

The ID is not on the form anywhere, though it is in the underlying recordset. For that matter, the recordset seems to be pointing to the first record, not the newly-added one (I do know that this behavior is documented for pure-code DAO recordsets, but I'd expect the current record of form.recordset to be the one that's represented on the screen).

2
ms-accessms-access-2007vba

2 Answers

3
votes

You can use the After Insert event:

Private Sub Form_AfterInsert()
MsgBox Me.ID  ''Name of autonumber field
End Sub
0
votes

You can also use the .movelast command

Set rstTemp etc < rstTemp.addnew

populate fields

rstTemp.update rsttemp.movelast

The value of rstTemp!ID where ID is the autonumber field will be the autonumber value