Retrieving auto number ID from insert query

[John]

Hi



I am using a query to insert a record into a table that has a auto number ID
field. I need to retrieve this ID so I can link the child records with it.
How can I achieve that?



Thanks



Regards

 

[Allen Browne]

Assuming Access 2000 or later, the function below will do it:

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

Alternatively, you could AddNew to a recordset and get the number.

 

[John]

Hi

Thanks for that.

I get a 'Syntax error : IDENTITY' on the line; Set rs =
db.OpenRecordset("SELECT @@IDENTITY AS LastID;")

Is @@IDENTITY a reserved word or should I use my id field 'ID' instead?

Thanks

Regards

 

[John]

PS: Sorry I am using Access 97. Just read the line that says 'Access 2000 or
later'.

 

[Allen Browne]

Right: That functionality was added in JET 4 (Access 2000), so won't work in
A97.

For Access 97, you will need to use the other approach:

Function ShowID() As Long
Dim rs As DAO.Recordset
Dim lngID As Long
Set rs = dbEngine(0)(0).OpenRecordset("MyTable", _
dbOpenRecordset, dbAppendOnly)
rs.AddNew
rs![SomeField] = "Some text"
rs![SomeNumber] = 99
rs![SomeDate] = Now()
lngID = rs![YourPrimaryKeyFieldHere]
rs.Update
rs.Close
ShowID = lngID
End Function

Do replace the names in this one, i.e. MyTable, SomeField, etc.