Developpez.com

Club des développeurs et IT pro
Plus de 4 millions de visiteurs uniques par mois

Numérotation personnalisée des enregistrements dans Access 2010

Office 2010

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

2 commentaires Donner une note à l'article (5)

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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  


image


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 :

 
Sélectionnez
="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.

image

Soit en VBA :

 
Sélectionnez
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 :

image


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.

 
Sélectionnez
Format([Indice];"000")
 
Sélectionnez
String$(3-Len([Indice]);"0") & [Indice]


L'expression de calcul du champ NumeroFacture sera donc :

 
Sélectionnez
"FA" & 
Right$(Year([DateFacture]);2) & 
String$(2-Len(Month([DateFacture]));"0") & Month([DateFacture]) & String$(3-Len([Indice]);"0") & [Indice]
image
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.

image

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 :

 
Sélectionnez
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 :

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

 
Sélectionnez
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 :

 
Sélectionnez
=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 :

Image non disponible

Après la saisie de la date :

Image non disponible

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.

 
Sélectionnez
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
Image non disponible

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 :

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

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

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

  

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. Droits de diffusion permanents accordés à Developpez LLC.