return primary key for record after append query

  • Thread starter Thread starter CuriousMark
  • Start date Start date
C

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?
 
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.
 
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"?
 
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.
 
Back
Top