how to return the autoincrement number from corresponding insert

[geo__]

Hi all (and Merry Christmas).

I have an insert query. When the user clicks save, I want the insert statement to return the number from the ID field of the table which is autoincrement (and is auto completed by the database).

The problem is that the database sits on a server and many users use it. So doing a MAX(ID) after insert there is a possibility that another user has pressed saved and the wrong number returned.

Any ideas?

Thanks,
George

 

[WayneRyan]

I knew that you could get when you insert via a form, or a
recordset. But I didn't know that it was unavailable from
an Insert via query.

I have seen where the insert takes an unused field and
puts something like '%$#%' in it. Then retrieves using
that value to get the autonumber, then updates to get
rid of the '%$#%'.

Don't know if I'd reccommend that approach though.

Wayne