Numérotation personnalisée dans Access

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 : 2 commentaires Donner une note  l'article (5)

Article lu   fois.

L'auteur

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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
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 :

 
Sélectionnez
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 demande 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éeclef auto incrémentées de SQLProSQLPro.

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 :

 
Sélectionnez
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
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
Affichage des 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.

Requête source
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 :

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

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

Affichage des données
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
é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
Cacher/Afficher le codeSélectionnez

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 :

Formulaire de saisie d'un dossier
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 tutorieltutoriel.

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 jointegenerer-numeros-auto.zip 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.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2020 Denis Hulo. Aucune reproduction, même partielle, ne peut être faite de ce site ni 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. Droits de diffusion permanents accordés à Developpez LLC.