469,306 Members | 2,121 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

Retrieving auto number ID from insert query

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
Apr 11 '07 #1
4 15268
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?
Apr 11 '07 #2
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?

Apr 11 '07 #3
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?

Apr 11 '07 #4
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?

Apr 12 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by dave | last post: by
5 posts views Thread by Geoff Cayzer | last post: by
4 posts views Thread by Shahar | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.