get last autonumber entered 1

[zrzr]

Hi !!!!

I've a table called client. The primarykey of this table is an autonumber.

I need to duplicate some clients in the table.So I made an add request which add a record onto the client table using datas from other record in the table.

My query works very well. It creates a new record in the table, changing the primarykey.But is it possible to get and store this new primarykey on VBA code ?

 

[scriverb]

Create a query with this SQL and name it qryMaxPrimaryKey.
SELECT Max(A.PrimaryKeyField) AS MaxOfPKF
FROM Client as A;

Update the red code with the name of the Primary Key field. Now you can use this code to retrieve this value:

DLookup("MaxOfPKF", "qryMaxPrimaryKey&quot

Post back with any questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[swampBoogie]

You should not use select max. Access 2000 has support for

select @@identity

which should be safe in a multiuser environment.

 

[scriverb]

Actually, you don't need the query just use the following:

DMax("YourPrimaryKeyField", "Client&quot

This will work in both A97 and A2k.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[zrzr]

Thanks for replying !!

Bob > It's a good idea, but the database I use is replicated, so autonumbers are not incrementals, but random values.

swampBoogie > how does it work ?
I guess it recall the same autonumber as the last, I'm right ? What are the @ for ?

 

[zrzr]

That's right, I found in the forum lots of threads about @@identity.

But I didn't find any "easy to understand" exemple of code, so this is the code I use :

'insert data into the current database using the current connection
    Dim strSQL As String
    strSQL = [i]'INSERT query'[/i]
    CurrentProject.Connection.Execute strSQL
    
    'retrieve the autonumber
    dim id as long
    strSQL = "Select @@identity;"
    
    Dim rec As New ADODB.Recordset
    rec.Open strSQL, CurrentProject.Connection
    id=rec(0)
    rec.Close

Thanks again for leading me on the good way !!!