Numérotation personnalisée des enregistrements dans Access 2010

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.

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]

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 :

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

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

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 :

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.