Numérotation personnalisée des enregistrements dans Access 2010

Christophe WARIN

Mise en place d'une numérotation personnalisée à l'aide des évènements de table de Microsoft Access 2010.

Commentez

- Titre : Numérotation personnalisée des enregistrements dans Access 2010

- Auteur : Christophe WARIN

- Parution : 16 novembre 2009

- Licence : Copyright ® 2009 Warin Christophe. 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. Avant Access 2010

- III. Avec Access 2010

- III-A. DataMacro

- III-B. Mise en place dans un formulaire

- III-C. Intérêt d'une table supplémentaire

- IV. Conclusion

I - Introduction

La mise en place d'une numérotation personnalisée est un thème qui revient souvent sur les forums. Ces questions sont aussi bien posées par des utilisateurs novices souhaitant transformer un champ NumAuto afin qu'il corresponde à leur besoin ou bien de personnes plus expérimentées provenant d'une autre technologie où les procédures stockées permettent aisément ce genre de traitement (SQL Server par exemple).

A travers ce document, je vous propose de découvrir une nouvelle approche de la numérotation des enregistrements dans Microsoft Access 2010 et plus particulièrement celle des numéros de factures dans une application de gestion.

II - Avant Access 2010

Dans un premier temps, un retour aux versions précédentes va permettre de cerner les problèmes auxquels les développeurs sont confrontés, passage obligé afin de déterminer les réels plus apportés par Access 2010.

Les hypothèses sont les suivantes :

- Une table tblFacture_ac2007 regroupe les factures établies par le service commercial.

- Les factures seront numérotées selon la norme : FAYYMMXXX de telle sorte que pour chaque mois (MM) de l'année (YY) un compteur (XXX) soit incrémenté à partir de 1.

Voici la structure de la table correspondante :

Nom du champ

Type

Extra

IDFacture

NuméroAuto

Clé primaire

IDClient

Integer

DateFacture

Date

DateEcheance

Date

Indice

Integer

Et son contenu :

IDFacture

IDClient

DateFacture

DateEcheance

Indice

1

2

12/11/2009

20/11/2009

1

3

1

17/11/2009

19/11/2009

2

4

1

10/12/2009

10/12/2009

1

5

2

11/12/2009

12/12/2009

2

Comme indiqué plus haut, vous remarquerez qu'à chaque nouveau mois, la valeur du champ Indice redémarre à 1. La mise en forme du numéro de facture peut alors être obtenu à l'aide de l'expression suivante :

="FA" & Format([DateFacture];"yymm") & Format([Indice];"000")

Comme vous le savez sans doute, aucun mécanisme propre à Access ne permet de définir un tel comportement pour le champ Indice. Jusqu'à présent, la seule solution consistait à confier cette étape de calcul à du code VBA inclus dans le formulaire servant à la saisie d'une nouvelle facture. Plus précisément, il était d'usage d'utiliser l'algorithme ci-dessous avant la mise à jour des données saisies dans les contrôles du formulaire.

Soit en VBA :

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim oRst As DAO.Recordset

If Me.NewRecord Then
Set oRst = CurrentDb.OpenRecordset( _
"SELECT Max(Indice) FROM tblFacture_ac2007 WHERE Format(DateFacture,""yymm"")=" & _
Chr(34) & Format(Me.DateFacture, "yymm") & Chr(34))
With oRst
If Not .EOF Then
Me![Indice].Value = Nz(.Fields(0).Value, 0) + 1
Else
Me![Indice].Value = 1
End If
.Close
End With
End If
End Sub

Illustration :

Si cette solution peut paraître suffisante, il n'en reste pas moins qu'elle présente quelques inconvénients majeurs :

- Dans un cadre multi-utilisateurs, il se peut qu'un utilisateur A valide son insertion pendant qu'un utilisateur B calcule son indice. Conséquence : B soumet une facture d'indice 3 alors que A vient juste de la créer. Au mieux, une erreur de doublon est levée si le champ les interdits, au pire, la base de données comporte une incohérence avec deux factures possédant le même numéro légal. En bref, rien ne garantit que l'indice calculé à l'étape 3 soit toujours valide à l'étape 5.

- Le recours au langage VBA pour l'incrémentation de l'indice empêche toute saisie externe au formulaire telle qu'une écriture directe dans la table, une requête action, ou bien encore une importation d'un fichier Excel par exemple.

III - Avec Access 2010

III-A - DataMacro

Les évènements de table présents dans Access 2010 permettent de pallier aux inconvénients listés ci-dessus. En effet, en confiant la détermination du nouvel indice d'une facture au moteur de base données, nous réduisons les risques d'accès concurrents (le délai entre les opérations est diminué) et nous étendons le domaine d'action de la règle de calcul. Désormais, même une saisie directe dans la table conduira à la création d'un indice adéquat. Il en va de même pour une importation depuis un fichier Excel par exemple.

La structure principale de la table tblFacture sera semblable à celle de tblFacture_ac2007.

Nom du champ

Type

Extra

IDFacture

NuméroAuto

Clé primaire

IDClient

Integer

DateFacture

Date

DateEcheance

Date

Indice

Integer

Pour plus de facilité, un champ calculé NumeroFacture peut y être ajouté de telle sorte à centraliser la formule de calcul dans la table. A la date de publication de ce tutoriel, la fonction Format n'est pas disponible dans les évènements de table. Impossible de savoir ce qu'il en sera dans la version finale prévue au printemps 2010. Nous sommes donc obligés de recourir à une expression équivalente à base de String$ et Right$.

Les deux appels suivants retournent le même résultat, pour des valeurs du champ [Indice] ne dépassant pas 999.

Format([Indice];"000")

String$(3-Len([Indice]);"0") & [Indice]

L'expression de calcul du champ NumeroFacture sera donc :

"FA" &
Right$(Year([DateFacture]);2) &
String$(2-Len(Month([DateFacture]));"0") & Month([DateFacture]) & String$(3-Len([Indice]);"0") & [Indice]

IDFacture

IDClient

DateFacture

DateEcheance

Indice

1

2

12/11/2009

20/11/2009

1

3

1

17/11/2009

19/11/2009

2

4

1

10/12/2009

10/12/2009

1

5

2

11/12/2009

12/12/2009

2

Juste avant l'insertion effective dans la table, l'évènement ValidateChange est levé. C'est dans cet évènement que nous allons préparer l'indice de la facture sur le point d'être créée. L'algorithme sera assez proche de celui utilisé dans l'évènement BeforeUpdate du formulaire vu plus haut.

L'évènement de table ValidateChange offrant très peu de possibilité en terme de programmation, nous allons confier le calcul du nouvel indice à la requête qryNouvelIndice dont le code SQL est le suivant :

SELECT Max(Indice)+1 AS NOUVELINDICE, Year(DateFacture) AS ANNEE, Month(DateFacture) AS MOIS
FROM TblFacture
GROUP BY Year(DateFacture), Month(DateFacture);

Info : nous n'utiliserons pas de requêtes paramétrées, pour l'instant incompatibles avec l'évènement ValidateChange.

Celle-ci propose une nouvelle valeur d'indice pour chaque mois déjà référencé dans la table. En l'occurrence pour notre jeu d'essai, le retour sera :

NOUVELINDICE

ANNEE

MOIS

3

2009

11

3

2009

12

L'action LookUpRecord permettra ainsi aisément de récupérer cette valeur et de la stocker dans une variable locale. Dans le cas où aucun enregistrement n'est trouvé, en théorie, le bloc LookUpRecord ne devrait pas être exécuté. Cependant, dans la version actuelle, celui-ci se poursuit et les champs de l'enregistrement trouvé possède la valeur NULL. Dans le doute et en attendant une éventuelle fonction NoMacth, je vous conseille de procéder ainsi :

SetLocalVar (VIndice;1)
LookUp A Record In qryNouvelIndice
WHERE  [ANNEE]=Year([tblFacture].[DateFacture]) AND [MOIS]=Month([tblFacture].[DateFacture])
ALIAS  record

SetLocalVar(vIndice;Nz(NouvelIndice,1))
SetField ([Indice];[vIndice])

Ainsi, si aucun enregistrement n'est trouvé et que le bloc LookUpRecord n'est pas exécuté, le champ Indice prendra la valeur de la variable locale vIndice initialisée en début de macro, dans le cas contraire, et si la valeur NULL est retournée, la fonction NZ la convertira en 1.

L'appel à la propriété IsInsert avant toute action permettra de s'assurer que le traitement interviendra uniquement lors de la création d'une nouvelle facture. En effet, il serait dommageable qu'un simple changement de l'échéance provoque la modification du numéro de la facture courante.

If [IsInsert] Then
SetLocalVar (VIndice;1)
LookUp A Record In qryNouvelIndice
WHERE  [ANNEE]=Year([tblFacture].[DateFacture]) AND [MOIS]=Month([tblFacture].[DateFacture])
ALIAS  record

SetLocalVar(vIndice;Nz(NouvelIndice,1))
SetField ([Indice];[vIndice])
End If

III-B - Mise en place dans un formulaire

L'interface de saisie d'une nouvelle facture sera identique à celle décrite pour Access 2007. Nous vous proposons de nous attarder à la définition d'une stratégie permettant à l'utilisateur de visualiser le numéro du document en cours de création. En effet, la tâche est plus complexe qu'il n'y parait. L'évènement de table étant déclenché après la validation des données dans le formulaire, il est impossible d'afficher en temps réel l'indice calculé dans la DataMacro vue plus haut. La mission va donc consister à présenter un numéro de facture probable et de le comparer en fin de traitement avec celui effectivement inscrit dans la base de données.

Dans cette optique, un champ nommé txtNumFacture possédant l'expression suivante comme source peut suffire :

=IIf(
[NewRecord];
"FA" &
Format(Nz([DateFacture]);"yymm") &
Format(
Nz
(
DMax(
"Indice";
"tblFacture";
"Format([DateFacture],""yymm"")=" & Chr(34) & Format(Nz([txtDateFacture]);"yymm") & Chr(34)
)+1;
1
);
"000"
);
[NumeroFacture])

Il s'agit en fait de retourner :

- Le numéro d'une facture existante en mode consultation.

- Un numéro calculé qui sera théoriquement alloué à une nouvelle facture.

En début de saisie :

Après la saisie de la date :

Enfin, la comparaison à l'aide du code VBA ci-dessous des valeurs avant et après update permet de générer l'alerte utilisateur.

Option Compare Database
Option Explicit

Dim vNumFacture As String

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Stocke le numéro de facture avant enregistrement
vNumFacture = Me.txtNumFacture
End Sub
Private Sub form_AfterUpdate()
'Compare le numéro actuel avec celui sauvegardé avant l'update
If vNumFacture <> Me.txtNumFacture Then
MsgBox "Une autre saisie a été traitée avant la vôtre, le numéro de votre facture a été modifié : " & _
Me.txtNumFacture
End If
End Sub

Private Sub cmdValider_Click()
Me.Refresh
End Sub

III-C - Intérêt d'une table supplémentaire

Si ce titre n'est pas très parlant, il suffit d'un simple exemple pour en comprendre toute la portée. Reprenons le jeu d'essai de départ avec 2 factures pour le mois de décembre 2009 : FA0912001 et FA0912002. L'utilisateur valide une troisième facture FA0912003 avant de se rétracter. Quel sera le numéro de la prochaine ? Dans le modèle actuel, ce sera FA0912003 (l'indice 3 ayant été supprimé, max(Indice)+1=2+1=3). Pourtant, il serait préférable qu'elle porte le numéro FA0912004 et que l'utilisateur justifie de l'absence de la précédente plutôt que de prendre le risque d'avoir un doublon. En effet, ici, il s'agit d'une simple rétractation de l'opérateur de saisie, mais que se passera-t-il si la dernière facture venait à être supprimée à cause d'une défaillance ?

Pour pallier à ce problème, une solution consiste à créer une nouvelle table qui hébergera le dernier indice créé pour chaque période. Cet indice servira de référence et le calcul se fera en toute indépendance du contenu de la table tblFacture.

Contenu de la table tblIndiceFacture :

Mois

Annee

Indice

11

2009

2

12

2009

2

Le code de l'évènement ValidateChange devient alors :

If [IsInsert] Then
SetLocalVar (VIndice;1)
LookUp A Record In tblIndiceFacture
WHERE  [ANNEE]=Year([tblFacture].[DateFacture]) AND [MOIS]=Month([tblFacture].[DateFacture])
ALIAS  record

If (IsNUll([Indice]) Then
SetLocalVar([vIndice];1)
Else
SetLocalVar([vIndice];[Indice]+1)
End If

SetField ([Indice];[vIndice])
End If

Enfin, l'évènement de table AfterInsert se chargera de modifier le contenu de la table tblIndiceFacture en créant un nouvel enregistrement si besoin est.

Look Up A Record In tblIndiceFacture
Where [ANNE]=Year([tblFacture].[DateFacture])
AND [Mois]=Month([tblFacture].[DateFacture])
Alias record

If IsNull([Indice]) Then
Create a Record In tblIndiceFacture
Alias  rec_NouvelIndice
SetField(Annee;Year([tblFacture].[DateFacture]))
SetField(Mois;Month([tblFacture].[DateFacture]))
SetField(Indice;1)
Else
Edit Record
Alias record
SetField(Indice;Indice+1)
End If

Nous avons volontairement séparé les deux étapes dans deux évènements différents bien que le résultat aurait été le même si tout était rassemblé dans l'évènement AfterInsert. Cependant, préparer les données dans un évènement Before* tel que ValidateChange en vue de leur insertion nous paraît plus proche de la réalité que de les modifier en aval.

IV - Conclusion

Comme promis, Access révolutionne certaines techniques utilisées depuis plus de 15 ans. En confiant certaines tâches touchant de près à la cohérence et à l'intégrité des données au moteur, le développeur Access met toutes les chances de son côté afin de rendre son application la plus robuste possible.

Dommage que cela ne soit pas intervenu plus tôt... Le recours systématique au langage VBA pour gérer certaines règles de gestion a participé à l'entretien de la réputation « SGBD bas de gamme» de Microsoft Access. Nul doute qu'Access 2010 saura renverser la vapeur.