How to get the AutoNumber generated by an SQL insert

I am trying to write a function to check the minimum value in an inventory table then generate and send a purchase request if the value is below the minimum level.

If the conditions are met an append sql statement like the one below is run.

DoCmd.RunSQL "INSERT INTO [table1]('field 1', 'field 2')" & _
             "VALUES ('value 1', 'value 2')"

The linked tables I am appending to use an auto number field as primary key and I need to reference its value to link the various items being requested. I do not know how to store the auto number that is being generated for the new record. I feel like I am missing something really simple, but none of my searches have brought anything up.

I would like to be able to write a second statement like the one below with the auto number value included.

DoCmd.RunSQL "INSERT INTO [table2]( 'ID' , 'field 3')" & _
             "VALUES (" & TempVars!autonumber & ", 'value 3')"

Solution

Use a DAO.Database object variable to execute your INSERT and again when you fetch the value from SELECT @@identity The key here is use the same Database variable for both. If you try to get SELECT @@identity from CurrentDb (instead of a Database object variable), the value you retrieve will always be zero.

I tested this code in Access 2010.

Dim db As DAO.Database
Dim strInsert As String

Set db = CurrentDb
strInsert = "INSERT INTO [table1]([field 1], [field 2])" & vbCrLf & _
    "VALUES ('value 1', 'value 2')"
db.Execute strInsert, dbFailOnError

TempVars.Add "itemNum", db.OpenRecordset("SELECT @@identity")(0).Value
MsgBox "TempVars!itemNum: " & TempVars!itemNum