autonumber field and inserting using sql statement

[Guest]

When I use an sql statement to insert a record into a table, is there a way
to know what autonumber value was assigned so that I can update related
tables with that value in code?

 

[David Lloyd]

Suzy:

Depending on your version of Access, your alternatives could include using
the SELECT @@Identity query, or reading the value from a recordset. The
following KB article gives more information.

http://support.microsoft.com/kb/232144/en-us

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


When I use an sql statement to insert a record into a table, is there a way
to know what autonumber value was assigned so that I can update related
tables with that value in code?

 

[Guest]

is there a way to do this with DAO... I found some code previously posted
that was like this...

strsql = "select * from mytable where falso"
set rs = currentdb.openrecordset(strsql,dbopendynaset)

rs.addnew
'all required fields here
rs.update
NewID = rs!id
rs.close
......

but I'm getting an error at the statement newid = rs!id that there is no
current record (when the table starts out empty) even though it has added the
new record.

I looked at the article that you gave a link to but when I tried it I got a
reference library error, when I went to tools to find the ADODB Library I
could not find it. I'm new to access I've only use DAO recordsets.

 

[Douglas J. Steele]

Try reversing the two lines NewID = rs!id and rs.update.

 

[Guest]

That worked, thanks

Try reversing the two lines NewID = rs!id and rs.update.