get return on autonumber

  • Thread starter Thread starter Stanley
  • Start date Start date
S

Stanley

I am using DAO in Access 97
I want to insert a record in a table and get back its key
(autonumber)
I need that number to put into some other records that
will have that key as a foreign key.

I intend to wrap all this in a transaction.

My problem is I don't know how to retrieve the new
assigned autonumber.
Thanks,
Stan
 
Access/Jet doesn't return variables (like SQL Server and others). You can do
this by opening a recordset, adding your
record, then setting a variable to the newly created record:

Dim lngID as Long

With rst
.AddNew
rst("strLastName") = "Levy"
rst("strFirstName") = "Stanley"
.Update

lngID = rst("lngID")
End With

Note that this syntax is probably wrong, as I've been working with ADO for
the last bit and have most likely got my libraries backwards!!! However, I
believe you can understand the concept.
 
I am using DAO in Access 97
I want to insert a record in a table and get back its key
(autonumber)
I need that number to put into some other records that
will have that key as a foreign key.

I intend to wrap all this in a transaction.

My problem is I don't know how to retrieve the new
assigned autonumber.
Click to expand...

Simply retrieve the autonumber value after invoking the ".AddNew" method:

rs.AddNew
'Retrieve your autonumber value here
lngNewID = rs!ID
'Insert more values
rs.Update
 
Back
Top