get id of autonumber field after inserting

[jlynch1]

i am inserting data into 2 tables.
in one of the tables there is an autonumber field called id.
i need the value of the id in order to put data in the other table.

how do i get that value for the autonumer field id so i can insert data into the other table.

 

[jkl]

This may not be the best way, or the cleanest, but it works:

===================
INSERT INTO someTable VALUES (x,y,z)

newID = SELECT TOP 1 autoNumber FROM someTable ORDER BY autoNumber Desc

INSERT INTO table2 VALUES (newID,p,q,r)

===================