Last AutoNumber Value

My script inserts data to two tables. Table1 has an autonumber field, which is the primary key. The algo is the ff:

1. Insert record to table1. (thus creating a new value in the autonumber field).
2. Get the new value from the autonumber field of table1 and inserts it to table2.

My problem now is, how can i get the new value in the autonumber field from table1?

If your using SQL server use the following to retrieve the identity number...

Code:

---

SELECT @@identity FROM tablename

---

This returns the last autonumber/ identity value...

I am not too sure about Access..

regards
MarkusJ

thank you very much. anybody here who knows it in access? I am planning to use the MAX function of SQL, but considering it is in a networked environment, it will not be safe, because maybe somebody has just inserted a record before i invoke my SQL request against the db, therefore getting the wrong value. pls help...

Hope this may work :

Insert into table2 values ((select max(autonumberfield) from table1),......)

if u using SQL Statment then kiran says right..

it not then it's work

rs.open "select * from abc",2,2
rs.addnew
rs.name = "name"
rs.add = "add"
myid = rs.id 'auto generated it


'now second table u can use sql here

insert into 2ndtable values(myid,'abc','bcd')

thank you again! I'll give shahid's code a try. For Kirankumar, that would not still be safe since other user might added another record before the sql statement is invoked. Anyway, thanks guys!

but i think that the both code are safe

why bcoz whenever user send any request IIS create an thread id

when one thread compelte then 2nd one applay.