How to Best Create a New Record and Capture Autonumber?

[Guest]

Good morning, everyone.
I've converted my Acc2K db to use CurrentDB.Execute so far as possible
for new records and updates, rather than opening a recordset and using
..AddNew or .Edit. However, I still use .AddNew when I'm creating a new
record and I want to capture it's autonumber ID. Something like this:

***********************
Dim NewCompanyID as Integer
Companies.AddNew
Companies.Name = "New Company"
NewCompanyID = Companies.Company
Companies.Update

CurrentDB.Execute("INSERT INTO CompanyAddresses VALUES (" & NewCompanyID &
", " & AddressID & ")")
***********************

Is there a more elegant way to handle this?
Thank you!

 

[RuralGuy]

Look back on 9/3/05 with the subject: Retrieving the [autonumber]

MVP's Ken Snell and Allen Browne offer their excellent advice.

Good morning, everyone.
I've converted my Acc2K db to use CurrentDB.Execute so far as possible
for new records and updates, rather than opening a recordset and using
.AddNew or .Edit. However, I still use .AddNew when I'm creating a new
record and I want to capture it's autonumber ID. Something like this:

***********************
Dim NewCompanyID as Integer
Companies.AddNew
Companies.Name = "New Company"
NewCompanyID = Companies.Company
Companies.Update

CurrentDB.Execute("INSERT INTO CompanyAddresses VALUES (" & NewCompanyID &
", " & AddressID & ")")
***********************

Is there a more elegant way to handle this?
Thank you!

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.