return primary key for record after append query

[CuriousMark]

Is there a way to find the primary key for a new record added to a table
using an append query in VBA? I need to assign that to a variable so that I
can use it an a second append query to a different table. For example:

tblCust: CustID (pk), CustName, CustAddress
tblOrder: OrderID (pk), CustID (fk), ProdID

If I use append query to add CustName and CustAddress to the tblCust table,
how do I get the CustID for that record to use in an append query to add
records to the tblOrder table?

 

[Allen Browne]

It is possible to query the last key value like this:

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

In general, I find it better to OpenRecordset, AddNew and Update, and then
set its Bookmark to LastModified to get the value that way.

 

[CuriousMark]

Thanks very much. I've looked at your online example from "Duplicate the
record in form and subform" but must confess I have trouble following it. Is
that what you refer to when you describe AddNew, Update and setting the
Bookmark to LastModified?

In this response, what is the "@@IDENTITY" - is that a special name or did
you just choose it as a variable name? I should know this, but I haven't been
able to develop an innate understanding of using Recordsets the way I would
like.

P.S. I think your "Duplicate the record...." examply has a typo: in the
comment ahead of the If Me.Dirty then...line, it reads "Save and edits
first". Did you mean "Save any edits first"?

 

[Allen Browne]

Yes: you can find an example of the AddNew, Update, and setting bookmark
here in the code here:
http://allenbrowne.com/ser-57.html

@@IDENTITY is a special name that JET 4 provides to give you this info. SQL
Server uses the name as well, but it provides more granular info than the
generic JET example.

Thanks for the typo info: Yes, "and" in the comment should have been "any."
Fixed.