Getting auto-number after record insertion 1

[minli98]

Hi,

What would be the easiest way to obtain the auto-number of a record that you just inserted? Would it be to do a select query for that record? I need the auto-number to put it into another table.

Thank you.

Min

 

[willir]

You can reference the number via code...

Me.NameOfAutoNumber

You can either use an insert or update SQL statement or a recordset approach.

Richard

 

[r937]

i'm told you can use @@IDENTITY in Microsoft Access

or you can just query back the row just inserted using the values of the other (unique) columns

r937.com | rudy.ca

 

[minli98]

Thanks for the tip. Using the sql statement below does work.

select @@IDENTITY as new_value

But in a multi-user environment, how do I make sure that there is no other insertion before I run the statement above? Is that something to do with locking the database?

Thanks again,

Min

 

[willir]

Cool Rudy -- Never saw that one before. Thanks.

 

[LittleSmudge]

How are you achieving the INSERT ?

If it is via an ADO.Recordset rst.AddNew and then populate the fields then after rst.Update the value rst!AutoNumFieldName will contain the new value you need.

And that is robust in a multiuser environment.


'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[minli98]

How are you achieving the INSERT ?

I am doing it through coldfusion's sql query. I have found out that I can specify the two queries (one to insert and the other to obtain the record number) as a transaction. This way prevents other queries from messing up with my request.

Thanks to all.

Min