Finding the highest number in the database?

Hi again all.

My database has a primary key called ID, which is an autonumber.

In VB new users are created and added to the database and automatically assigned a new number (not necessary sequential to the previous ID - but higher).

As soon as the new entry is created, is there a way I can instantly determine the new ID number?

I thought about looping through the database and looking at each ID number & comparing them, and the highest number will be the new ID, but I dont know how to do this in VB.

The database is access, and I'm using ado.....can anyone help me here.

I hope I have explained myself well enough.

You can get the autonumber id of the latest record added right after the update:

rs.update
lngAutonumber= rs!UserIDNumber

OR

You could just move the cursor to the end:

rs.movelast
lngAutoNumber=rs!UserIDNumber

this is the code I am using:

--------------------------------------

Set con = New ADODB.Connection
Dim strSELECT As String
Dim strInsert As String
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\huskiesvip\database.mdb;Persist Security Info=False"

strSELECT = "SELECT * FROM people"

With rs
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseClient

'This line is Important

.Open strSELECT, con, , , adCmdText

End With

strInsert = "INSERT INTO [people] (FirstName, Surname, DateOfDeparture, ExpiryDate, Comments) VALUES ('" & Text2.Text & "', '" & Text3.Text & "', '" & deptdate & "', '" & expdate & "', '" & Text7.Text & "') "

con.Execute strInsert
con.Close
MsgBox "V.I.P successfully entered!", vbInformation
Set rs = Nothing
Set con = Nothing

-----------------------------------------------

I want the msgbox to say "VIP ENTERED SUCCESSFULLY, their ID Number is : & IDnumber"

I am not using rs.update, please could you explain a bit more in lamens terms......

p.s. on my datagrid, I want the entries sorted by ID number, but they are not, why is this? and how can I sort them in ascending order?

Thanks again

With rs
.ActiveConnection = con
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Source="people", adCmdTableDirect
.Open strSELECT, con, , , adCmdText
.Addnew
!FirstName=Text2.Text & ""
!SurName=Text3.Text & ""
!DateOfDeparture=Deptdate
!ExpiryDate=expdate
!Comments=Text7.Text & ""
.Update
lngAutoNumber=!thenameofyourautonumberfield
End With

MsgBox "V.I.P successfully entered!", vbInformation
Set rs = Nothing
Set con = Nothing