Retrieve value after insert

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.

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Brian" <Brian@discussions.microsoft.com> wrote in message
news:49D5126A-9227-4C5F-857E-8583136FA0D3@microsoft.com...
> 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.

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

"Douglas J. Steele" wrote:

> 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
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Brian" <Brian@discussions.microsoft.com> wrote in message
> news:49D5126A-9227-4C5F-857E-8583136FA0D3@microsoft.com...
> > 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.
>
>
>

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.
--
Jac Tremblay


"Douglas J. Steele" wrote:

> 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
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Brian" <Brian@discussions.microsoft.com> wrote in message
> news:49D5126A-9227-4C5F-857E-8583136FA0D3@microsoft.com...
> > 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.
>
>
>