ADDING RECORD USING 'INSERT INTO' AND RETRIEVE NEW RECORD AFTER... 1

[franksirvent]

HI, I am using INSERT INTO to insert a new record into a maintable...Works fine.

What I need is to get the Auto Number just given to the new record created so I can use further on another INSERT INTO query. Anyone could tell me how to do that ?

I am trying to get the AutoNumber because I then duplicate also the lines on the subform, which are linked to the maintable using the AutoNumber, and then I run another query which also copies all subform lines, giving me an exact copy of the original record...

Obviously this might be a stupid way of doing it...if someone can advise a better way I will very much appreciated...

thanks in advance

 

[Trendsetter]

Once you have inserted the record run another query which will be just the Autonumber field of the table in Descending order. That way the first record in the list will be the number most recently inserted.
When you now interrogate this query you will be given only the first record available which is the one you want.

 

[franksirvent]

thanks, I´ll try that...

 

[guitboxgeek]

To ensure that you have the very highest record id, you could also do something like this:

select MAX(field_here)
from yourTable

 

[swampBoogie]

Use the query

select @@identity

after the insert. Using select max or using order by id desc is not reliable.

 

[guitboxgeek]

Great Tip swamp!

I'm curious why you think MAX() would be less reliable?

 

[swampBoogie]

After your insert and before the select max there can be another using doing an insert on the same table. Which means that the id's will get mixed up if select max is used.