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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:OS****************@TK2MSFTNGP05.phx.gbl...

>
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?

[John]

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

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:OS****************@TK2MSFTNGP05.phx.gbl...

>>
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?

[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
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:OS****************@TK2MSFTNGP05.phx.gbl...

>>
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?

[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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:LZ*********************@pipex.net...

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

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:OS****************@TK2MSFTNGP05.phx.gbl...

>>>
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?