Finding RecordID after Insert Into

[Scott]

I have a form that collects data that then is put into a table (using
DoCmd.RunSQL and an Insert Into SQL string) if the user decides they want to
save the record. The RecordID is an autonumber. In addition to the
information on the main record, they are putting information that needs to go
into a related table. I can't do the SQL string for the records that are in
the related table until I know the recordID of the record that was just
created. Can someone suggest a good way to get that ID (the ideas I have
seem somewhat convoluted - I'm sure there are easier ways than the ways I am
considering)?

 

[Jeanette Cunningham]

Hi Scott,
here is one way to do it.

Private Sub MySub()
Dim db As DAO.Database
Dim rs As DAO.Recordset

NewID = 0
'this example is for an autonumber primary key
Set db = CurrentDb
Set rs = db.OpenRecordset("NameOfTable", dbOpenDynaset)

'Add the record
With rs
.AddNew
!ContactName = Me.ContactName
!PhoneNbr = !Me.PhoneNbr
.Update
.Bookmark = .LastModified

NewID = rs!ID
End With

rs.close
Set rs = Nothing
Set db = Nothing
End Sub



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Scott]

That's what I was looking for. Thanks.

Hi Scott,
here is one way to do it.

Private Sub MySub()
Dim db As DAO.Database
Dim rs As DAO.Recordset

NewID = 0
'this example is for an autonumber primary key
Set db = CurrentDb
Set rs = db.OpenRecordset("NameOfTable", dbOpenDynaset)

'Add the record
With rs
.AddNew
!ContactName = Me.ContactName
!PhoneNbr = !Me.PhoneNbr
.Update
.Bookmark = .LastModified

NewID = rs!ID
End With

rs.close
Set rs = Nothing
Set db = Nothing
End Sub



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia