Retrieve value after insert

[Brian]

Access 2003
Front end mde
Back end mdb

Other than @@Identity, is there any other way to retrieve the AutoNumber PK
of a newly-inserted record. Outside a multi-user environment, this is
probably just a DMax call, but it seems wise to avoid that approach in a
multi-user environment.

My usual method of running SQL statements via VBA goes like this:

Dim strSQL as String
strSQL = "INSERT INTO MyTable (MyField1) SELECT MyData"
CurrentDb.Execute strSQL, dbFailOnError

Do I have to replace this with code that opens the DAO connection so I can
use the @@IDENTITY operator, or is there some other way? Of course, I could
use another field where I insert a timestamp, assign it to a variable, and
include it in the WHERE clause of the DMAX call, but that also seems like
extra work.

 

[Douglas J. Steele]

Are you really just inserting a single field? You could try using

Dim rsCurr As DAO.Recordset
Dim lngNewAutonumber As Long

Set rsCurr = CurrentDb.OpenRecordset("SELECT MyField1 FROM MyTable WHERE
False")
rsCurr.AddNew
rsCurr!MyField1 = MyData
lngNewAutonumber = rsCurr!ID
rsCurr.Update
rsCurr.Close
Set rsCurr = Nothing


That assumes that your Autonumber field is named ID

 

[Brian]

As you suspected, my example was over-simplified. I actually populate several
fields of a header table with one SQL statement, then need to get the
newly-created header PK to insert as the FK into related detail records using
a subsequent SQL statement.

If I read your post correctly, I just need to structure the insert to occur
via a DAO recordset instead of building the SQL query and then executing it.
To do the additional fields, would it look like something like this?

Set rsCurr = CurrentDb.OpenRecordset("SELECT MyField1, MyField2, MyField3,
MyField4 FROM MyTable WHERE
False")
rsCurr.AddNew
rsCurr!MyField1 = MyData1
rsCurr!MyField2 = MyData2
rsCurr!MyField3 = MyData3
rsCurr!MyField4 = MyData4
lngNewAutonumber = rsCurr!ID
rsCurr.Update
rsCurr.Close
Set rsCurr = Nothing

 

[Jac Tremblay]

Hi Douglas,
I think that your select should be:
Set rsCurr = CurrentDb.OpenRecordset("SELECT ID, MyField1 FROM MyTable WHERE
False")
Otherwise, the line:
lngNewAutonumber = rsCurr!ID
will bug.
Thanks for your precious comments.