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.

 

[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

 

[pbaldy]

Is @@Identity an option here?

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