Access VBA: Find max number in column and add 1

Question

In my Access database, I have a table called "Demande". I want to read all of the records in the column "Numero de Commande" and read the largest number in the column so that I can use that number for the next new record.

So here is my table:

The Key Index is for the Numero de Commande.

and here is my code currently:

 Dim highestInt as Integer
 Dim newNumeroCommande as Integer

 Set currentDatabase = CurrentDb
 Set rstDemande = currentDatabase.OpenRecordset("Demande")

 ' Find the highest integer in the column "Numero de Commande"

 newNumeroCommande = highestInt + 1
 rstDemande.AddNew
 rstDemande("Numero de Commande").Value = newNumeroCommande 
 rstDemande.Update

Thank you for all help.

Answer 1

As long as the column only contains numeric values with no alpha's then you can use:

NewNumeroCommande = Dmax("[Numero de Commande]", "Demande") + 1

Note: This is not my recommended method but just an in-built option of Access, if possible then you can save yourself the hassle by using Autonumbering, or alternatively you could have a seperate "counter" table which records the highest record number, when you wish to create another, you could lock this table, increment the value by one (and then use this) and then release the lock, this would be more effective in a multi-user environment.