Retrieve autonumber ID after INSERT

[John Smith]

Hi,
I have VB code that does
DoCmd.RunSQL
"INSERT INTO [myTable] ( fieldName) " & _
"VALUES ('" & NewData & "');"
Table myTable has an AutoNumber ID.
Is there a way I can know the ID of the new record, so that I can do
stLinkCriteria = "[ID_myTable]='" & (something like newlyCreatedID) &
"'"
DoCmd.OpenForm myFormName, , , stLinkCriteria
just afterwards?

 

[David Lloyd]

John:

Alternatives include using a SELECT @@Identity query, or using a recordset
to append to the table and then reading the autonumber value. The
following KB article provides more information these methods.

http://support.microsoft.com/kb/232144/en-us

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi,
I have VB code that does
DoCmd.RunSQL
"INSERT INTO [myTable] ( fieldName) " & _
"VALUES ('" & NewData & "');"
Table myTable has an AutoNumber ID.
Is there a way I can know the ID of the new record, so that I can do
stLinkCriteria = "[ID_myTable]='" & (something like newlyCreatedID) &
"'"
DoCmd.OpenForm myFormName, , , stLinkCriteria
just afterwards?

 

[Fjordur]

Hi,
I have VB code that does
DoCmd.RunSQL
"INSERT INTO [myTable] ( fieldName) " & _
"VALUES ('" & NewData & "');"
Table myTable has an AutoNumber ID.
Is there a way I can know the ID of the new record, so that I can do
stLinkCriteria = "[ID_myTable]='" & (something like newlyCreatedID) &
"'"
DoCmd.OpenForm myFormName, , , stLinkCriteria
just afterwards?

(added otherwise than querying max(ID), as this is a multiuser
environment; I want to be certain I retrieve the very same record I just
inserted