Introduction

Je vous propose de télécharger cette base de données qui va nous servir de base pour cet exercice.

Vous y découvrirez 3 tables :

• les deux tables "de travail" :

Je prends l'exemple de deux tables seulement, mais l'historisation que je vais vous proposer fonctionnera aussi bien avec 3, 4 ou 50 tables différentes.

Dans mon exemple, j'ai une table T_Materiel, qui recense une liste de différents accessoires destinés à la vente.

L'autre table, T_Service, liste toutes les prestations possibles (pour une société de services informatique).

Tout ce que nous faisons subir à l'une ou l'autre table (ajout, suppression ou modification d'un ou plusieurs enregistrements dans n'importe laquelle de ces tables, doit être répercuté automatiquement dans la 3^ème table :

• T_HistoriqueMultiTable
  que voici :

Démonstration initiale

Afin de vous aider à comprendre la finalité de notre exercice, je vais effectuer quelques modifications dans l'une et l'autre table, pour vous montrer comment ça va se répercuter dans T_HistoriqueMultiTable.

1. Dans T_Materiel, J'efface l'article N° 3 :
2. Dans T_Materiel, J'ajoute une webcam, avec son PrixAchat et PrixVente



3. Dans T_Materiel, je modifie Clavier 102 touches en Clavier QWERTZ, et je passe son PrixVente de 47 à 52.90

4. Dans T_Materiel, j'efface le PrixVente de la souris sans fil (ce qui aura pour effet de le remettre à 0):
5. Dans T_Materiel, je passe le PrixAchat de la Webcam de 54.00 à 47.50 :
6. Dans T_Service, j'efface les prestations N°3 et 4 :

Voyons le résultat dans T_HistoriqueMultiTable :

Reprenons nos exemples un par un :

1. le 16.1.2013, à 23:04, j'effectue une suppression dans la table T_Materiel. Le premier champ (IDMatériel) contenait l'ancienne valeur 3, le Libelle contenait Webcam, le PrixAchat : 48 et PrixVente : 69.9 (Ces deux derniers chiffres sont ici stockés en format Texte)
    
2. à 23:07, j'ajoute une nouvelle ligne dans T_Materiel : ce sont ainsi des nouvelles valeurs qui sont introduites dans la base de données, c'est pour cette raison que les données dont décalées par rapport à la suppression AncienneValeur et NouvelleValeur).
    

3. Cette fois, à 23:13, j'effectue une modification : L'IDMateriel est un NuméroAuto qui ne risque donc pas de changer : il reste à 2. Dans le champ Champ2, il s'agit du Libelle, et là, on voit que j'ai changé le nom : je passe de Clavier 102 touches à Clavier QWERTZ. Comme je n'ai pas modifié le prix d'achat, ces deux cases sont laissées vides. Par contre, comme j'en ai profité du même coup pour modifier le PrixVente : j'indique logiquement l'ancien et le nouveau : de 47 à 52.9
    
4. Il s'agit à nouveau d'une modification - et pas d'une suppression - j'efface juste le contenu d'un seul champ : le prix de vente. Plus précisément, je le passe de 49 à 0. On verra par la suite comment exiger qu'il passe de rien à 0 (sinon, on ne peut pas à lui faire comprendre qu'un champ passe d'une certaine valeur à ... complètement vide).
    
5. Je m'occupe à nouveau de l'article N°6 (que j'avais déjà modifié un peu plus haut : Création de la webcam). Bien que l'enregistrement ait été créé à 23:07, on voit qu'on revient sur ce même enregistrement à 23:38 (pour descendre son prix d'achat de 54 à 47.5).
    

6. (6 et 7 plus exactement - puisqu'on a sélectionné deux enregistrements en même temps dans T_Service). Voici tous les détails de ces deux suppressions qui ont donc été effectuées exactement en même temps à 23:44 et 33 secondes.
   Les trois dernières colonnes (qui concernent le champ 4) ne sont pas remplies ici, pusiqu'il n'y a pas 4 colonnes dans cette table, mais seulement 3 (C'est dans T_Materiel qu'il y a 4 colonnes - ou 4 champs).

Avez-vous compris pourquoi tous les champs de T_HistoriqueMultiTable sont définis en Texte (qui est le format de données le plus universel) ?

Parce que, selon les tables, le contenu des différents champs pourra être en Texte, en Numérique, en Date-Heure, ... Il faut que nous puissions stocker de toute façon les contenus de tous les champs des différentes tables dans T_HistoriqueMultiTable.

Dans notre cas, le Champ3 contient soit PrixAchat, soit PrixForfaitaire, qui sont tout deux des numériques, certes, mais il peut très bien arriver que le 3^ème champ d'une autre table soit du texte, une case à cocher Oui/Non, ou une Date !

Ainsi, si vous avez compris le principe, le nombre de champs de T_HistoriqueMultiTable se monte à 4 fois 3 (ChampX, AncienneValeurX, NouvelleValeurX) parce que le plus grand nombre de champs de toutes mes (deux) tables est de 4 (il s'agit ici de T_materiel avec les 4 champs suivants : IDmateriel, Libelle, PrixAchat et PrixVente).

Si vous désirez mettre en oeuvre ce même système d'historique sur votre base de données, qui comprend certaines tables qui possèdent peut-être plus de 20 champs, il faudra le même nombre de champs (20 fois 3, donc 60) dans T_HistoriqueMultiTable - A moins que vous ne décidiez que certains champs sont inutiles à historiser, évidemment.

Sous le 6, il n'y a pas d'enregistrement, on voit juste la valeur par défaut du moment, mais ne vous inquiétez pas, ce moment sera parfaitement mis à jour lorsque vous effectuerez d'autres changements dans les deux autres tables.

Historisation des ajouts

Nous allons commencer par historiser les ajouts de nouveau matériel.

Allez dans T_Materiel, dans la macro , et recopiez la macro suivante (Le XML a copier-coller est sous l'image - Cliquez ici pour vous rappeler) :

Ensuite, essayez d'ajouter le Scanner dans T_Materiel, puis fermez T_Materiel, et ouvrez T_HistoriqueMultiTable :

Macros nommées

Cette macro fonctionne parfaitement. Toutefois, si nous voulons que l'historique se génère également lorsqu'on ajoute un nouveau service, il faudrait qu'on se retape toute cette macro dans de T_Service, et ce n'est pas très rationnel !

L'idéal serait de créer une macro générale qui récupère le nom de la table (T_Materiel ou T_Service), le genre de changement (Ajout, modification ou suppression), et les valeurs des champs.

Laissez-moi vous montrer par l'exemple, vous allez comprendre progressivement pourquoi c'est mieux.

Commençons par créer une nouvelle Macro de données. Nous avons vu dans la leçon précédente que les macros de données peuvent indifféremment se stocker dans n'importe quelle table, mais je vous propose de créer celle-ci dans la table qui fait l'objet de notre attention T_HistoriqueMultiTable :

Ajoutez un paramètre, comme ceci :

Paramètres des macros

Je vous propose de commencer les noms de nos paramètres par PAR pour éviter les confusions (Ce n'est pas obligatoire, c'est juste un conseil). Un texte explicatif permettra de s'y retrouver plus facilement par la suite :

Ajoutez ensuite tous les paramètres nécessaires, comme ceci :

Vous pouvez également copier coller ce code XML pour éviter la recopie fastidieuse :

Fermez ensuite votre macro, et enregistrez la sous MDHistoriqueMultitable
(MD = Macros de Données - Mais ces deux lettres ne sont pas obligatoires) :

Vous pourrez y retourner quand bon vous semble de cette manière :

Pour l'instant, notre macro nommée ne fait pas grand chose d'autre que d'accueillir des paramètres. Nous la compléterons tout à l'heure. Pour l'heure, retournez dans T_Materiel, et corrigez votre macro de cette manière :

Commencez par effacer tout le contenu de votre macro (oui, c'est triste, mais c'est comme ça), et demandez l'action ExécuterMacrosDonnées :

Dès que vous choisissez votre macro de données, tous les paramètres apparaissent. Oui, c'est triste, on l'a déjà dit...

Remplissez ces paramètres à la main. Je vous rappelle que ce qui est entre "guillemets", représente du texte littéral ("IDMateriel" = le mot IDMateriel), tandis que ce qui est entre [Crochet] représente le contenu des champs ([IDmateriel] = 3
(par exemple, si on est sur le N° 3) :

Ainsi, PARChamp1 contiendra littéralement IDMateriel, tandis que PARNouvelleValeur1 contiendra 3.

Comme d'habitude, je vous fournis le code XML, si ça vous ennuie de tout recopier :

Fermez cette macro et la table T_Materiel. Retournez dans MDHistoriqueMultiTable (qui se trouve donc dans T_HistoriqueMultiTable).

Historisation des nouveaux enregistrements

Créons maintenant un nouvel enregistrement dans T_HistoriqueMultiTable, et répartissons-y tous les paramètres que nous allons recevoir (en fait, ça va drôlement ressembler à ce que nous avons fait au début de cette leçon) :

Voici le code XML de la partie encadrée de rouge prête à être copiée-collée :

Lorsque vous avez terminé, fermez T_HistoriqueMultiTable, et retournez dans T_materiel afin d'ajouter un lecteur de code barre :

Fermez T_Materiel, et rouvrez T_HistoriqueMultiTable. Youpi : la création de ce nouvel article a bien été historisée :

Zuuuut ! Nous avons omis de préciser qu'il s'agit d'un changement de type Ajout !

Qu'à cela ne tienne ! Il suffit de l'ajouter dans MDHistoriqueMultiTable, et d'ajouter le paramètre et son traitement :

Et modifier T_materiel, :

Bien.

A ce point, on se demande l'intérêt d'être passé par la création d'une macro nommée MDHistoriqueMultiTable !

Réutilisabilité des macros nommées

On va le comprendre maintenant :

Sélectionnez le contenu de votre macro de T_Materiel, et copiez-le :

Allez dans l'autre table (T_Service), dans , et collez :

Maintenant, il s'agit d'ajuster les paramètres, comme ceci :

Il est indispensable d'écrire des guillemets vides "" dans PARChamp4 et PARNouvellevaleur4, sinon, une erreur serait générée car ces paramètres seraient considérés comme manquants.

Testons ! Ajoutez un enregistrement dans T_Materiel et un autre dans T_Service. Fermez les deux tables, et ouvrez T_HistoriqueMultiTable :

Qu'est ce que c'est que ça Une lessiveuse USB, et un Détartrage de l'écran ? ... Ca m'amusait, c'est tout. Quoi ? Qu'est-ce qu'il y a ? Ca ne fait pas sérieux, c'est ça ? Eh ben voilà, c'était mon petit plaisir du jour.

Traque des erreurs à l'aide de USYSApplicationLog

Plus sérieusement, la lessiveuse a bien été historisée, mais ... Rien du côté du détartrage de l'écran !

C'est tout bête : nous avons commis une petite erreur. mais laquelle ? Regardez en bas à droite de votre écran Access : Cliquez sur Nouvelles erreurs de l'application :

Ca ouvre USYSApplicationLog dont je vous ai parlé dans la partie 1 sur les macros de données :

J'ai surligné en jaune les informations qui peuvent nous aider à traquer l'erreur : il s'agit bien de T_Service, dans AfterInsert. Ensuite, dans la Description et le Context, il semble nous dire que PrixForfaitaire est invalide. Ah bon ? Mais pourquoi ?

je me rends donc dans T_Service et je constate que je suis distrait ! ce n'est pas PrixForfaitaire, le nom du champ, mais TarifForfaitaire !

Un petit coup de bistouri suffit à tout faire rentrer dans l'ordre :

Testons : Ajoutez encore un autre service, fermez T_Service. Cette fois, l'historisation fonctionne aussi bien pour cette table que pour T_Materiel :

Mise à jour des paramètres

Bonne nouvelle : nous allons pouvoir réutiliser MDHistoriqueMultitable pour la suppression également. C'est ça qui est intéressant !

Par contre, nous allons devoir faire un peu de chirurgie interne : en effet, lors de l'ajout de nouveaux enregistrements, nous remplissons les champs NouvelleValeurX. Maintenant, lors de la suppression, nous allons remplir les AncienneValeurX.

Et pour tout vous dire, lorsque nous nous occuperons de la modification, nous remplirons les anciennes et les nouvelles valeurs.

Ainsi, nous allons devoir doubler tous les paramètres de MDHistoriqueMultitable de manière à ce qu'il reçoive les nouvelles et les anciennes valeurs de tous les champs (Constatez que j'ai changé l'ordre des paramètres pour plus de clarté, nous devrons faire des modifs par la suite dans les macros de T_Materiel et T_Service par la suite ().

Deux solutions s'offrent à vous : tout recopier à la main  - ou  - télécharger la version de la base de données qui contient toutes les modifications qui suivent (le lien sera proposé à la fin de l'explication).

Ensuite, il s'agit de compléter la mise à jour de T_HistoriqueMultiTable :

Maintenant, fermez T_HistoriqueMultiTable et allez dans T_Materiel 

Cliquez sur Mettre à jour les paramètres :

Et maintenant, il s'agit d'envoyer les anciennes valeurs. Mais pourquoi ? On s'en fiche puisque nous sommes dans ! Oui, mais on doit au moins préciser qu'on envoie des paramètres vides (""), sinon, il va générer dicrètement une erreur dans USYSApplicationLog (et du coup, il ne va pas mettre T_HistoriqueMultiTableà jour):

Voyez : il ne génère qu'une ligne d'erreur sur PARAncienneValeur1, alors qu'en fait, aucune des 4 anciennes valeurs n'est renseignée.

Complétez de cette manière :

Faisons subir le même traitement à T_Service :

Je vous laisse faire :

Testez : , et allez vérifier que l'historisation a bien eu lieu.

Historisation des suppressions

Maintenant, nous allons quand même recueillir les fruits de notre labeur ! Grâce au fait que nous avons toutes les variables de tous les champs, en ancienne et nouvelle valeur, nous allons pouvoir utiliser la même macro MDHistoriqueMultiTable aussi pour les suppressions.

Afin d'éviter de devoir tout recopier à la main, copiez le contenu de T_Materiel et collez-le dans T_Materiel, , puis opérez à ces modifications :

Et voilà ! Testons. Effacez le clavier 102 touches : , et vérifiez que l'historisation de la suppression a bien eu lieu :

Je pense que vous devinez quoi faire à présent ? Oui, nous allons copier le contenu de la macro T_Materiel dans T_Service .

Faites-le et testez... Supprimez un service, puis vérifiez que cette suppression a été correctement journalisée.

Historisation des modifications

Il ne reste plus qu'à s'occuper de la modification.

• Pour les ajouts, nous n'avons journalisé que les nouvelles valeurs entrées
• Pour la suppression, nous ne nous sommes concentrés que sur les anciennes valeurs
• Pour la modification, nous allons donc journalsier les anciennes et les nouvelles valeurs.

Allez dans T_Materiel. Afin de ne pas tout recommencer depuis zéro, copiez le contenu de dans , puis complétez comme suit :

Ensuite, comme d'hab, testez :

Modifiez une ligne de T_Materiel (Juste le libellé, pas les prix):

et contrôlez ensuite votre T_HistoriqueMultiTable :

Ca marche parfaitement ! Il s'agit bien d'une Modification dans T_Materiel, et on a bien transformé Imprimante laser en laser printer.

La seule chose qui me chagrine, c'est que même les valeurs qui n'ont pas changé sont également répertoriées !

Bon, l'IDMateriel (AncienneValeur1 et NouvelleValeur1), il faut bien la répertorier pour qu'on puisse savoir de quel enregistrement il s'agit (On ne peut de toute façon pas la changer), et donc, j'aurais pu choisir de la transférer indifféremment dans AncienneValeur1 ou NouvelleValeur1, mais comme j'ai pris le parti de transférer cette donnée dans AncienneValeur1 dans le cas d'une suppression, et dans NouvelleValeur1 dans le cas d'un ajout, je me dis que dans le cas d'une modification, je la transfère dans les deux...

Par contre, il n'y a vraiment aucun intérêt à transférer le PrixAchat (qui est resté à 290), ni le PrixVente (qui est resté à 389) : dans le cas d'une recherche dans T_HistoriqueMultiTable sur les modifications, ça va juste nous embrouiller !

Ne pas historiser les données inchangées

Ce qui serait bien, ce serait qu'il ne transfère que les champs qui ont été réellement modifiés !

Eh bien, c'est possible !

Par contre, ça va nous obliger à complexifier quelque peu la macro MDHistoriqueMultiTable.

Voici le principe : dans le cas d'une mise à jour ou d'une suppression, pas de souci : on passe les paramètres depuis T_Materiel ou T_Service, et MDHistoriqueMultiTable se contente de dispatcher les différents paramètres (qui contiennent, selon les cas, la valeur des champs ou rien ("")).

Par contre, si nous sommes dans le cas d'une modification, il faut être attentif à chaque champ :

Si PARAncienneValeur2 est égale à PARNouvelleValeur2, On n'inscrit rien. Mais si elle est différente, alors, il faut les inscrire dans l'historique. Même chose pour PARAncienneValeur3 et PARAncienneValeur4.

PARAncienneValeur1, par contre, peu importe : de toute façon, nous les inscrirons à la fois PARAncienneValeur1 et PARNouvelleValeur1 (bien que ce sera chaque fois les mêmes).

Après la théorie, la pratique. Voici comment il faut modifier MDHistoriqueMultiTable :

Afin de vous éviter la recopie fastidieuse, voici le code XML complet de la nouvelle version  de MDHistoriqueMultiTable :

Bon, il ne reste plus qu'à tester, encore et toujours :

Ca marche formidablement bien !

Problèmes inhérents aux paramètres vides

Seul petit bémol : dans le cas où l'utilisateur efface purement et simplement un PrixAchat ou de PrixVente (ou le PrixForfaitaire dans T_Service), les paramètres de MDHistoriqueMultiTable ne supportent pas de recevoir une valeur vide en paramètre (Ben non, vous aimeriez, vous, qu'on vous fasse un cadeau vide ?).

C'est assez vicieux, parce que MDHistoriqueMultiTable va fonctionner sans donner d'erreur, ni même remplir une ligne de USYSApplicationLog, mais elle va laisser un champ vide, illégitimement :

Afin de pallier à ce cas de figure, allons respectivement dans T_Materiel et T_Service, dans leur macro de table et demandons-leur qu'en cas de valeurs vides dans leurs champs, il faut les remplacer par 0, comme ceci :

Et voilà !

Il re reste plus qu'à tester :

Voilà ! Ca fonctionne fort bien !

Par contre, si on remplace le Libelle - qui est un champ texte  - par rien (qu'on l'efface, donc), le même problème surviendra ! Vous allez me dire que ça n'a pas grand sens d'effacer un libellé sans effacer carrément tout l'enregistrement. Certes ! On peut d'ailleurs même interdire que le champ Libelle soit vide dans les deux tables :

Mais... Ce n'est qu'à moitié satisfaisant ! On peut très bien imaginer des tables pourvues de champs texte facultatifs, qu'on peut effacer tout à fait logiquement...

Du coup, que faire ?

Dans le cas de numériques, on remplace les vides par 0, mais dans le cas d'un texte, on ne va quand même pas écrire "0" dans un champ qu'on efface, ça fait un peu amateur ! ... On ne peut pas écrire Guillemets-Guillemets"", ça ne marche pas. A la rigueur, on pourrait inscrire Guillemets Espace Guillemets " "... Ca fonctionnerait mais ça nous impose un espace dans notre champ.

Je vais être franc : je n'ai pas de solution miracle à vous proposer pour ce cas de figure.

Limites du champ d'action des macros de table

Dans ce dernier paragraphe, autant vous prévenir : nous n'arriverons pas à nos fins élégamment : nous atteignons la limite des macros de table. De plus, cette section exige que vous connaissiez un minimum de VBA (Mais je vais vous épauler, vous me connaissez, je suis bon comme le pain)

Dans le cas d'une base de données multi-utilisateurs, cette historisation ne serait définitivement complète que si l'on inscrit le nom de l'utilisateur qui a effectué l'ajout, la suppression ou la modification.

Or, pour ce faire, nous devons créer une fonction personnalisée VBA qui va nous renvoyer le nom d'utilisateur utilisé lors du login Windows. 

Récupération du nom d'utilisateur

La fonction VBA personnalisée qui renvoie le nom de l'utilisateur courant se présente comme ceci :

Function Utilisateur()
    Utilisateur = Environ("username")
End Function

Afin de la tester, faites ceci :

Recopiez à la main ce qui est encadré en bleu, comme ceci :

Testez en cliquant sur TesterLaFonction, et en appuyant sur F5.

Fermez. Assez bizarrement, il ne vous est pas proposé d'enregistrer votre module VBA. Forçons-lui la main : fermez la base de données !

Access vous propose (enfin !) d'enregistrer votre module. Appelez-le P_Perso
(P comme Programmation).

Création d'un formulaire pour l'utilisation d'une fonction

Maintenant, avant de vous montrer comment ça ne marche pas, je vais déjà vous montrer comment ça marche !

Ajoutez un champ UtilisateurCourant dans T_Materiel :

Fermez cette table, et créez un nouveau formulaire, comme ceci :

Ecrivez =Utilisateur() dans la propriété Valeur par défaut du champ UtilisateurCourant, comme ceci :

Lancez le formulaire en mode saisie de données, et ajoutez un nouveau matériel :

Interaction Formulaires/Tables/Macros de tables

Constatez qu'il ajoute automatiquement le nom d'utilisateur dans UtilisateurCourant. Fermez le formulaire, et profitez-en pour l'enregistrer sous F_Materiel. Bien que vous ayez entré un nouveau matériel via un formulaire, et non via la table T_Materiel directement, la macro de T_Materiel s'est exécutée, car la table a vraiment été mise à jour en arrière-plan (ce qui est très logique, puisque les formulaire ne contiennent pas de données, ils ne sont que des filtres esthétiques basés sur les tables).

Regardez T_HistoriqueMultiTable :

Il nous manque évidemment l'utilisateur courant dans cet historique. Ajoutez-le :

1. Ajoutez un champ UtilisateurCourant dans T_HistoriqueMultiTable
2. Modifiez MDHistoriqueMultiTable afin de considérer ce nouveau champ
3. Modifiez de T_Materiel, pour prendre aussi en compte ce nouveau champ

Comme ceci :

Pour tester

• vous devez retourner dans le formulaire F_Materiel
• ajouter encore un autre matériel
• fermer le formulaire
• contrôler T_HistoriqueMultiTable :

Ca fonctionne très bien !

Formulaires plus performants

Mais pourquoi diable est-on passé par un formulaire ???

L'astuce consistant à écrire =Utilisateur() dans la valeur par défaut du champ UtilisateurCourant de F_Materiel, pourquoi n'a-t-on pas écrit =Utilisateur() directement dans la valeur par défaut de la table T_Materiel ?

Tout simplement parce que ce n'est pas possible !

Bien qu'il s'agisse de la même propriété, les options des valeurs par défaut proposées dans les formulaires sont beaucoup plus nombreuses que dans les tables.

Regardez ce comparatif :

Comparaison Macros de table/Utilisation de formulaires

C'est vraiment gênant, parce que si vous ajoutez un nouveau matériel directement dans la table (sans passer par le formulaire, donc), la macro ne va pas compléter le champ UtilisateurCourant.

Même commentaire si vous modifiez ou si vous supprimez un enregistrement depuis la table T_Materiel : il ne sera pas possible d'envoyer l'UtilisateurCourant dans l'historique.

Il faudra absolument penser à faire toutes les modifications, ajouts et suppressions depuis des formulaires.

Et encore : nous n'avons pas fini l'exercice ! Nous avons effectivement défini la valeur par défaut pour UtilisateurCourant, ce qui règle la mise à jour de l'historique dans le cas de nouveaux services, mais il faudra maintenant, par programmation VBA, remplir le champ UtilisateurCourant de F_Materiel dans le cas d'une suppression d'enregistrement, ainsi que lors d'un changement !

Et refaire toute la même manipulation pour le formulaire que vous devrez alors créer pour les services !

Nous allons donc nous retrouver avec une moitié de travail effectué dans les macros de table, et, juste à cause du fait qu'on ne peut pas appeler de fonction peronnalisée depuis les valeurs par défaut des tables (ni, d'ailleurs, en aucune manière depuis les macros de table), on va se retrouver avec du code VBA dans les formulaires.

On finit par se demander si on ne devrait pas carrément gérer l'ensemble de tous les ajouts de lignes dans T_HistoriqueMultiTable avec de la programmation VBA dans les formulaires ?

Mais ceci est un autre débat, et nous allons nous arrêter là, car le but était ici de vous montrer comment établir un historique des modifications de vos tables à l'aide de macros de table.

Voici la base de données complètement terminée.