Largest number in field (unsorted) (1 Viewer)

[simongallop]

I think that there is an easy solution to this problem.

Table keeping details of contacts. As details can never be deleted, just flagged as old, whenever they change a new record is created. This means that if I have 10 contacts, in theory, the last record in the table could be the details of contact number 1. How do I find the last issued contact number, so that I can create a new contact?

I don't want to have to loop through the table finding the largest number as after time it will take too long. Is there a Max command (and how to use it)?

Thanks in advance

Simon

 

[MHM]

Do this:

Set DefaultValue for this field to:

=DMax("ContactNumber";"TableContacts")+1

But in fact this doesn't do anything else but looping through all the records - and it's not as fast as Recordset.FindLast(...) (by factor 10 or above...).

If you really want to have it fast, you need some code (requires Field [ContactNumber] to be indexed):
(Access97-Example, in Acc2000: use DAO3.5-library or adapt code)


Dim dbCurr as DAO.Database
Dim rs as DAO.Recordset

Set dbCurr=CurrentDB
Set rs=dbCurr.OpenRecordset("TableContacts", dbOpenTable)
rs.Index="ContactNumber"
rs.MoveLast ' Index has sorted recordset already, last record=Max

MsgBox rs!ContactNumber+1 & " is next ContactNumber"

 

[simongallop]

Thanks for the help but can't get rs.Index="ContactNumber" to work.

The code that I am using at present is:

ContactRef = 0
ContactRS.MoveFirst
Do While Not ContactRS.EOF
ContactRefTemp = ContactRS("Contact_Ref")
If ContactRefTemp > ContactRef Then
ContactRef = ContactRefTemp
End If
ContactRS.MoveNext
Loop
ContactRef = ContactRef + 1

How can it be improved as the larger the table, the longer it takes.