retrieve autonumber

[clive2002]

I'm using an SQL to insert a record into a table in VBA, how can i then get the autonumber for the the record just created.

No other fields are unique so i can't use a lookup.

Any help?

 

[dcx693]

You can't get the autonumber from a record as you're inserting it via SQL, so you can only obtain it afterwards.

You could do a DMax lookup on the table for the autonumber, but that would only work if the autonumbers are set to increment, not if they're randomly assigned.

If your autonumber is set to random, then you could do it using a kludge. You can copy the existing autonums to a temp table, insert your record, then do a query to find out the newest autonum.

 

[WayneRyan]

You could also use a DateTime field, set it to Now() on the
BeforeInsert event and use the DMax on that field.

Wayne

 

[dcx693]

You could also use a DateTime field, set it to Now() on the
BeforeInsert event and use the DMax on that field.

Wayne

I thought about that also, but it will only work if the code ran from within a form and the form's events are triggered. Since clive2002 is using SQL to insert a record into a table, I assumed he was using INSERT INTO or UPDATE. Those would not trigger form-level events.

 

[WayneRyan]

You could use a DateTime field, set it to Now() in the
Insert statement and use the DMax on that field. The Update
statement will not produce a new Autonumber.

Missed you DCX!

Wayne

 

[dcx693]

I was actually in Yosemite for a few days. How far is that from Camarillo?

Anyway, using that Date/Time field would be great if he/she had one in the table. If creating one to solve this problem works for the user, then problem solved.

By the way, you can actually add new records (as well as update) to a table using an UPDATE query, but you're right because it won't work if there is an autonumber field.

 

[Pat Hartman]

Rather than running an append query, add the new record with DAO or ADO. The autonumber of the inserted row will be available until another access is done to that table.

....
rst.AddNew
rst.SomeField = SomeValue
rst.Update

TheNewAutonumber = rst.YourAutoNumField

Every other method suggested has the potential to fail in a multi-user environment when two people insert a row at approximately the same time.

 

[WayneRyan]

Nice Pat,

How about if you did a:

dbs.execute "Insert into ..."

Wouldn't you have to resort to one of the other methods?

Wayne

 

[SforSoftware]

Hi,

Instead of DMax, you can use DLast. DLast returns the latest entered record in the table (that not is deleted) and not the last one based on PK or sorting order.
See also the KB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208190

 

[Pat Hartman]

WayneRyan,
Your example is executing a query. You will have no way to obtain the autonumber. Some RDBMS' supply a special function that can be run as a pass-through query to obtain the autonumber as long as the insert is not committed, but Jet does not.

SforSoftware,
As I said, these methods are not reliable in a multi-user enviornment. Computers are very fast and a second user can insert a row between the time the code inserts a row and then asks for the last record.

 

[pbaldy]

Is @@Identity an option here?

http://support.microsoft.com/default.aspx?scid=KB;en-us;q232144