Numérotation personnalisée dans Access

Le logiciel Access dispose de son propre mécanisme interne pour numéroter les enregistrements des tables. On a ainsi la possibilité d'ajouter un « champ » de type NuméroAuto, c'est-à-dire un entier dont la valeur est incrémentée à chaque nouvelle insertion, avec la garantie de ne jamais être réattribuée. Ces colonnes servant généralement à identifier chaque enregistrement, on parle alors de clef auto-incrémentée.

Après avoir donné les avantages offerts par ces clefs auto-incrémentées, on décrira, en se basant sur un exemple simple, un mécanisme permettant de générer des numéros auto personnalisés à l'aide d'une table supplémentaire pour enregistrer des compteurs d'indices.

Enfin, on proposera de tester avec la base disponible en téléchargement, la génération d'un numéro personnalisé, après l'archivage des données et la création d'un nouvel enregistrement.

Auto-incrément : il s'agit ni plus ni moins que d'incrémenter, c'est-à-dire ajouter une unité, à la plus haute des valeurs déjà attribuées. Lorsque ce mécanisme est automatisé, on l'appelle, tout simplement, auto-incrément…

L'utilisation de clefs auto-incrémentées est très fréquente, car elle offre de nombreux avantages.

La valeur du numéro automatique, le plus souvent masquée, ne sert qu'à identifier de manière unique un enregistrement et ne donne aucune autre information, contrairement aux valeurs d'une clef naturelle.

Elle est donc totalement transparente à l'utilisateur et ne risque pas d'être modifiée, évitant ainsi les incohérences et conservant l'intégrité référentielle des données.

La première idée qui nous vient pour obtenir la valeur du prochain numéro serait d'ajouter 1 à la dernière valeur générée dans la table :

numero = DMax("numero","LaTable") + 1

Cependant, si par exemple, on archive le dernier enregistrement de numéro 101, avant de le supprimer de la table d'origine, la valeur maxi de la clef devient 100, on va donc ensuite attribuer une nouvelle fois le 101 pour un prochain enregistrement.

De même, en mode multi-utilisateur, supposons que l'utilisateur A commence la saisie d'un nouvel enregistrement, se voyant attribuer le numéro 110. Peu de temps après l'utilisateur B reçoit lui aussi une clef pour l'ajout d'un enregistrement. Si A n'a pas terminé la saisie, B se verra lui aussi attribuer le numéro 110.

Une clef auto-incrément offre la garantie de ne jamais être réattribuée, autrement dit : toute clef consommée est perdue.

Par conséquent, on ne peut pas compter sur la table elle-même pour connaître la valeur à attribuer à la nouvelle clef à calculer.

Il en découle une évidence : le mécanisme de calcul de la nouvelle clef doit être externe à la table.

On rencontre souvent sur les forums des questions relatives à la numérotation personnalisée.

Un utilisateur souhaite par exemple créer des numéros personnalisés pour identifier des dossiers (assurance ou autres) sous la forme :

« DO-2019-0001 » pour 1^er dossier « dommage ouvrage » de l'année 2019 ;
« DO-2019-0002 » pour 2^e dossier « dommage ouvrage » de l'année 2019 ;
…
« DO-2020-0001 » pour 1^er dossier « dommage ouvrage » de l'année 2020 ;
« DO-2020-0002 » pour 2^e dossier « dommage ouvrage » de l'année 2020.
…

On constate que les indices sont incrémentés relativement à l'année : on parle ainsi d'incrémentation relative.

La solution évidente consiste à trouver l'indice maximum déjà enregistré dans la table pour la dernière année, et d'y ajouter la valeur 1 :

indiceDossier = DMax("indiceDossier","T_Dossier","TypeDossier='DO' and Year(DateCreation)=2020") + 1

Par exemple, si le dernier dossier enregistré dans la table porte la référence « DO-2020-0002 », on cherche simplement à incrémenter la valeur d'indice maxi, à savoir 2, pour obtenir « DO-2020-0003 ».

Mais supposons qu'un dossier clôturé prématurément et de même référence ait déjà été archivé, puis supprimé de la table. On se retrouve alors avec des numéros identiques pour deux dossiers différents.

Sans compter les mêmes problèmes d'attribution de numéros identiques rencontrés en mode multi-utilisateur.

L'idée va donc consister à créer un mécanisme de génération de numéros personnalisés garantissant de ne jamais attribuer deux fois la même référence.

III-A. Tables nécessaires▲

On part d'une table T_Dossier permettant d'enregistrer les informations relatives aux dossiers :

Nom de la colonne	Type de données	Description
IdDossier	NumeroAuto	Identifiant du dossier, on choisit une clef auto-incrémentée avec un mécanisme interne.
TypeDossier	Text	Type de dossier : « DO », « MULTI »...
DateCreation	Date/Heure	Date de création du dossier
IndiceDossier	Entier long	Indice du dossier dans l'année : numéro auto-incrémenté relativement à une période
…	…	…

Exemple de données :

On garde une clef auto-incrémentée pour les liens clef primaire/clef étrangère.

Comme mentionné précédemment, le mécanisme d'auto-incrément doit être extérieur à la table contenant la clef ou l'indice. On prévoira donc une table supplémentaire T_Compteur_Indice pour enregistrer la dernière valeur attribuée à l'indice dans l'année :

Nom de la colonne	Type de données	Description
TypeDossier	Texte	Type de dossier : « DO », « MULTI »...
Periode	Texte	Période durant laquelle est incrémenté l'indice : par exemple « 2020 »
CompteurIndice	Entier long	Dernière valeur d'indice générée relativement à la période

Exemple de données :

En mode multi-utilisateurmultiutilisateurs, ces tables doivent être dans le fichier de données, côté serveur.

III-B. Requête source du formulaire▲

Elle permet d'afficher en plus des données contenues dans la table T_Dossier, la référence de chaque dossier.

Pour composer le numéro de référence du dossier à partir du type de dossier, de la date de création et de sa valeur d'indice, on utilisera l'expression suivante :

 

Sélectionnez

ReferenceDossier : [TypeDossier] & "-" & Format(DateCreation;"yyyy") & "-" & Format(IndiceDossier,"0000")

Affichage des données de la requête source :

III-C. Formulaire de saisie des dossiers▲

Puis, on insère du code sur l'événement BeforeUpdate du formulaire. Cette procédure permet de mettre à jour la valeur d'indice du dossier.

Déroulé de la procédure exécutée avant mise à jour d'un enregistrement dans le formulaire F_Dossier :

1. Ouverture d'un Recordset sur l'enregistrement contenant la valeur du compteur d'indice ;
2. Copie de la prochaine valeur d'indice dans une variable ;
3. Mise à jour du champ IndiceDossier avec la nouvelle valeur d'indice ;
4. Mise à jour de l'enregistrement de la table des compteurs avec la nouvelle valeur.

Procédure sur BeforeUpdate

Cacher/Afficher le codeSélectionnez

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' procédure exécutée sur l'événement BeforeUpdate du formulaire F_Dossier
    Dim cpt As Long ' compteur des numéros automatiques, valeur enregistrée dans la table T_Compteur_Indice
    Dim dbs As DAO.Database ' variable objet pour faire référence à la base de données
    Dim rstCpt As DAO.Recordset ' variable objet de type recordset basée sur la table T_Compteur_Indice
    Dim periode As String ' variable contenant la période
    
    If Me.NewRecord Then ' si on est en mode ajout
        
        periode = Format(Me.DateCreation, "yyyy") ' copie l'année correspondant à la date de création du dossier
        
        Set dbs = CurrentDb ' référence à la base de données courante
        ' ouverture du recordset basé sur la table T_Compteur_Indice filtrée en fonction du type de dossier et de la période
        Set rstCpt = dbs.OpenRecordset("select compteurIndice, TypeDossier, periode from T_Compteur_Indice where TypeDossier like """ & Me.TypeDossier & """ and Periode like """ & periode & """", dbOpenDynaset)
            
        If Not rstCpt.EOF Then ' si un compteur existe pour le dossier et la période
            cpt = rstCpt!CompteurIndice + 1 ' on évalue le prochain numéro à attribuer à l'enregistrement
            rstCpt.Edit
            rstCpt!CompteurIndice = cpt ' on met à jour le compteur d'indice avec le numéro
            rstCpt.Update
        Else ' sinon
            ' on ajoute la nouvelle période et le dossier à la table des compteurs d'indices et on met le compteur à 1
            cpt = 1 ' on repart de 1 pour une autre année
            rstCpt.AddNew
            rstCpt!TypeDossier = Me.TypeDossier
            rstCpt!periode = periode
            rstCpt!CompteurIndice = cpt
            rstCpt.Update
        End If
        
        Me.IndiceDossier = cpt ' on enregistre la valeur du nouvel indice du dossier dans le champ du même nom.
        
        ' libération des variables objet
        rstCpt.Close
        Set rstCpt = Nothing
        
        Set dbs = Nothing
    End If
    
End Sub

Pour l'année 2020, cela revient à mettre à jour l'indice d'un dossier « DO » avec cette formule :

 

Sélectionnez

indiceDossier = Dlookup("CompteurIndice","T_Compteur_Indice","[TypeDossier]=""DO"" and [Periode]=""2020""") + 1

Si vous souhaitez avoir plus d'information sur la fonction de domaine DLookup ou RechDom en français, vous pouvez consulter ce tutorielfonction RechDom.

La période choisie ici est l'année, mais vous pouvez facilement adapter le code pour une incrémentation relative au mois ou au semestre par exemple.

Aperçu du formulaire quand l'utilisateur sauvegarde le nouvel enregistrement :

On constate que la référence du dossier « DO-2020-0004 » se met automatiquement à jour en fonction de la date de création.

Selon le Code de Commerce, les numéros de factures doivent être : continus et non interrompus : on ne doit pas sauter un numéro de facture, autrement dit, il ne doit pas y avoir de « trou » dans la suite de numéros, chronologique : incrémentation croissante et unique : deux factures ne peuvent pas avoir le même numéro.

Il n'est donc pas question de pouvoir « perdre » un numéro, par exemple, lors de la création d'une facture, puis de sa suppression et avant impression et envoi.

C'est pourquoi, le mécanisme décrit précédemment ne doit pas être utilisé pour générer des numéros de factures.

L'application comporte également du code et des requêtes pour archiver les données.

Exemple de scénario pour tester l'application

1. Ouvrir le formulaire F_Dossier.
2. Aller sur le dernier dossier de référence « DO-2020-0004 ».
3. Saisir une date de clôture et cocher la case « Archivé ».
4. Appuyer sur le bouton « Archiver ».
5. Ajouter un nouveau dossier avec une date comprise dans l'année 2020.
6. Appuyer sur enregistrer : vous devriez voir apparaître la référence « DO-2020-0005 ».

Une requête mise à jour permet également de réinitialiser les compteurs, comme lors du compactage de la base pour les colonnes de type NuméroAuto.

La base jointegenerer-numeros-auto.zip est au format accdb.

Je tiens à remercier Pierre Fauconnier de m'avoir conseillé pour la réalisation de cet article, ainsi que Claude Leloup pour sa relecture.