3

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:

Table Picture

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.

Paolo Bernasconi
  • 2,010
  • 11
  • 35
  • 54

2 Answers2

4

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.

Matt Donnan
  • 4,933
  • 3
  • 20
  • 32
  • 1
    This is extremely dangerous in anything but a single user environment. – Fionnuala Aug 14 '12 at 09:51
  • I didn't know it was so easy. Works perfectly, and only 1 line of code. Thank you – Paolo Bernasconi Aug 14 '12 at 09:52
  • 1
    @PaoloBernasconi You will not be the only reader of this post. – Fionnuala Aug 14 '12 at 09:55
  • @Remou Fair enough, this is not something I would ever use myself for the reasons we are all too familiar but this time I thought I would allow the user to see how it worked for them and let them investigate further if error's came their way, lazy on my part to be honest. I shall add some explanation. – Matt Donnan Aug 14 '12 at 09:56
  • @Remou Very true - Is there a more error-proof way of executing this? – Paolo Bernasconi Aug 14 '12 at 09:56
  • 2
    @PaoloBernasconi The ideal way is to not have a field that means anything, then the next number can be provided by autonumber. If you must have a field where the sequence is important, you need a bit more code and a small table to hold the seed. – Fionnuala Aug 14 '12 at 10:13
4

Some notes on getting a sequence number. This requires a reference to the Microsoft ActiveX Data Objects x.x Library

Sequential Numbers

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim lngResult As Long
Dim strCon As String

    lngResult = 0  'assume fail

    strCon = "Provider=" ''Connection to back-end
    cn.Open strCon

    rs.CursorType = adOpenKeyset
    rs.LockType = adLockPessimistic
    rs.CursorLocation = adUseServer

    ''Where BEInfo is a single line table
    strSQL = "SELECT ASeqNumber FROM BEInfo"

    rs.Open strSQL, cn, , , adCmdText

    'Note this is ADO, so no rs.Edit
    rs!ASeqNumber = rs!ASeqNumber + 1
    rs.Update

    lngResult = rs!ASeqNumber

    ''This should not happen, but just to be sure
    If DCount("ASeqNumber", "Table", "ASeqNumber=" & lngResult) > 0 Then
        lngResult = 0
    End If
Fionnuala
  • 90,370
  • 7
  • 114
  • 152