How to get the generated id of previous insert query

[Jeboy]

Can somebody help me how to get the generated id from previous insert query? Thank you so much in advance

 

[wazz]

a simple way would be to:
Dim newID As Long
newID = DLookup("Max(YourID)", "tblYourTable")

 

[Jeboy]

That works! thanks! but, what if there are several users who make insert at the same time?

 

[The_Doc_Man]

Ah, an old programmer's rule case!

If you want to know the most recently generated ID entered by user X, you must keep user X's user ID as part of the table to show who entered the record.

The rule: Access won't tell you anything you didn't tell it first.

It means: If you are going to want to link ID to users in any way, you have to store user info with that ID. If you didn't tell Access you were going to ask that question (by having it store that kind of data), you won't get it to answer you.

 

[Pat Hartman]

If you need to know the ID, you can't use a query to add the record, you need to use DAO or ADO. The autonumber will be available to you between the .addnew and .update if the database is Jet. If the database is SQL Server, you will need to use a special variable to get the value but the name of the variable escapes me at the moment. It might be something like $Identity.

 

[RoyVidar]

Followup on what Pat Hartman mentions.

If you were to execute the query on an ADO connection, you could fetch the last identity (autonumber) using the same connection, something along the following lines:

' dynamic SQL
myconn.execute "<TheSql>", , adCmdText + adExecuteNoRecords
' query/SP
myconn.execute "QueryName", , adCmdStoredProc + adExecuteNoRecords

' for Jet
Set rs = myconn.Execute("SELECT @@Identity", , adCmdText)

' for SQL Server
Set rs = myconn.Execute("SELECT ScopeIdentity()", , adCmdText)

Debug.print "Last identity " & rs.fields(0).value

Or, for SQL server, you might "do it all in one" - have the SP return the result of the SELECT ScopeIdentity(), and execute like this

set rs = myconn.execute("SPName", , adCmdStoredProc)

Identity is safe to use in Jet settings. For SQL server, you'd need to ensure you get the last identity in this scope - i e the identity of this process, not whatever new record caused by a trigger executed based on your operation (or by triggers created by SQL server).