Numérotation personnalisée dans Access

Denis Hulo

Objectif : mise en place d'une numérotation personnalisée des enregistrements à l'aide d'une table de compteurs d'indices.

Niveau requis : débutant.

Commentez cet article : Commentez

- Titre : Numérotation personnalisée dans Access

- Auteur : Denis Hulo

- Parution : 12 novembre 2020

- Mise à jour : 15 février 2022

- Public visé :

- Licence : Copyright ® 2020 Denis Hulo. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. clef auto-incrémentée

- III. Mise en place d'une numérotation personnalisée

- III-A. Tables nécessaires

- III-B. Requête source du formulaire

- III-C. Formulaire de saisie des dossiers

- IV. À propos des numéros de factures

- V. Base de données à télécharger

- VI. Remerciements

I - Introduction

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.

II - clef auto-incrémentée

clef

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.

Si vous souhaitez avoir plus d'information sur le sujet, je vous invite à consulter le cours clef auto-incrémentée de SQLPro.

III - Mise en place d'une numérotation personnalisée

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 1er dossier « dommage ouvrage » de l'année 2019 ;
« DO-2019-0002 » pour 2e dossier « dommage ouvrage » de l'année 2019 ;
…
« DO-2020-0001 » pour 1er dossier « dommage ouvrage » de l'année 2020 ;
« DO-2020-0002 » pour 2e 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.

Affichage des données

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 :

Affichage des données

En mode multi-utilisateur, 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.

Requête source

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 :

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

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

Affichage des données

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.

événement BeforeUpdate du formulaire

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

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 :

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 tutoriel.

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 :

Formulaire de saisie d'un dossier

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

IV - À propos des numéros de factures

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.

Concernant la mise en place d'une numérotation personnalisée des factures, je vous renvoie à ce tutoriel.

V - Base de données à télécharger

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 jointe est au format accdb.

VI - Remerciements

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