Returning AutoNumber Values 2

[mattygriff]

When I create a new entry into a table with an AutoNumber field, is there a way of simultaneously returning the value of the AutoNumber without doing a separate SELECT?

Thanks.

 

[RoyVidar]

Let's say you're using ADO, then the following insert and retrieval of identity should work (note - Jet 4.0) - the identity will retrieve the last inserted autonumber on this connection:

[tt]dim rs as adodb.recordset
dim strSsql as string
strSsql="insert into mytable (field1, field2) values ('test1', 'test2')"
currentproject.connection.execute strSql
set rs=currentproject.connection.execute("select @@identity")
debug.print rs.fields(0).value[/tt]

Roy-Vidar

 

[mattygriff]

Excellent - thanks RoyVidar - have a star!!!