Last AutoNumber Value

[eimroda]

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?

[MarkusJ_NZ]

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

SELECT @@identity FROM tablename

This returns the last autonumber/ identity value...

I am not too sure about Access..

regards
MarkusJ

[eimroda]

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...

[kirankumar]

Hope this may work :

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

[shahid]

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')

[eimroda]

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!

[shahid]

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.