I. Prérequis▲
Maîtriser la conception des formulaires et des états.
Être capable de « sentir le code VBA » : pas nécessairement savoir l'écrire, mais comprendre ce qui s'y passe (ce qui constitue une étape importante dans votre apprentissage de ce langage qui décuplera vos possibilités).
 |
Pour un problème de code dans un module, placez le curseur n'importe où dans l'instruction et pressez <F1>. L'aide Access s'ouvre alors à la bonne page. |
II. Le but de l'exercice▲
On dispose d'une table qui contient les différents plats :
À l'ouverture du formulaire : compléter la date
Il vient
On voudrait alors construire les menus de la semaine pour les éléments de la carte des trois services journaliers :
avec une liste déroulante limitée aux plats pertinents pour chaque cas, pour aboutir à ceci :
En un clic, produire un état bilingue du menu d'un jour :
En un clic, produire un état synoptique des sept jours, au choix en français et en anglais :
III. L'idée▲
Utiliser un formulaire « principal » qui contiendra 21 sous-formulaires (un pour chacune des trois cartes de chacun des sept jours de la semaine) et récupérer ces données pour produire les états.
IV. Les difficultés▲
1° Adapter la source de chaque sous-formulaire pour qu'il affiche les données de cette carte. Nous devrons écrire quelques lignes de code VBA.
2° Adapter le contenu des zones de liste pour les limiter aux choix pertinents pour un item de ce type (soupe, entrée, plat…).
Le problème vient du fait que lorsqu'on modifie de manière dynamique le contenu de la zone de liste pour en restreindre les choix dans l'enregistrement actif, cette restriction entre automatiquement en vigueur pour tous les autres enregistrements du formulaire.
Cela n'altère pas la valeur des choix antérieurs : l'item qui avait été choisi subsiste tel quel, mais si sa valeur ne figure pas dans la liste de celles ponctuellement permises, Access ne peut plus l'afficher.
Au paragraphe VIII, nous décrirons une astuce pour contourner le problème.
V. La table tMenus▲
Cette table pour stocker les données du plat choisi, pour chaque item des trois services de chaque jour (30 enregistrements par jour), par exemple pour le 8/10/2015 :
On évite les doublons comme ceci :
| On pourrait considérer comme une redondance le fait que tMenus contienne les intitulés (français/anglais) puisque ceux-ci sont dans tPlats. On s'attendrait à une relation comme celle-ci pour obtenir le nom du plat : |
|
 |
 |
| Cependant, nous n'avons pas pu opter pour une telle solution, car dans un premier temps, lorsque les enregistrements sont créés, on ne connaît pas encore le plat qui sera choisi par l'utilisateur… et tPlatsFK vaut alors 0, par défaut. |
VI. Le formulaire fMenuSemaine▲
VI-A. Les pièces du puzzle▲
À l'ouverture, tous les contrôles sont invisibles sauf txtDateDepart.
Sa mise à jour fera passer la propriété Visible des autres contrôles à Oui.
La section Entête du formulaire contient sept contrôles avec une date qui correspondra à chaque jour de la semaine qui débute à la date de départ saisie par l'utilisateur.
Dans la section Détail, on trouve :
- 3 fois le sous-formulaire sfLignes destiné à afficher les 10 items d'une carte ;
- 21 fois le sous-formulaire sfMenuJour qui affichera le plat choisi pour chacun des 10 items des 21 cartes de la semaine (3 services fois 7 jours).
VI-B. Le code déclenché par la mise à jour de txtDateDepart▲
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
Private Sub txtDateDepart_AfterUpdate()
Dim ctl As Control
Dim sSqlDebut As String
Dim sSql As String
Dim i As Integer
'Rendre les contrôles visibles
For Each ctl In Me.Controls
ctl.Visible = True
Next ctl
'Aménager les dates
DoCmd.SetWarnings False
For Each ctl In Me.Controls
If ctl.Name Like "txtJour#" Then
'Garnir les contrôles
ctl = Me.txtDateDepart + Right(ctl.Name, 1)
'Créer (éventuellement) les enregistrements vierges avec ces dates dans la table tMenus
DoCmd.RunSQL "INSERT INTO tMenus ( MenuDate, MenuService, MenuLigne ) " _
& "SELECT #" & Format(ctl, "mm/dd/yyyy") & "# AS Expr1, 1 AS Expr2, tMatrice.MenuLigne FROM tMatrice;"
DoCmd.RunSQL "INSERT INTO tMenus ( MenuDate, MenuService, MenuLigne ) " _
& "SELECT #" & Format(ctl, "mm/dd/yyyy") & "# AS Expr1, 2 AS Expr2, tMatrice.MenuLigne FROM tMatrice;"
DoCmd.RunSQL "INSERT INTO tMenus ( MenuDate, MenuService, MenuLigne ) " _
& "SELECT #" & Format(ctl, "mm/dd/yyyy") & "# AS Expr1, 3 AS Expr2, tMatrice.MenuLigne FROM tMatrice;"
End If
Next ctl
DoCmd.SetWarnings True
'Construire la source de chaque sous-formulaire et le contenu des listes
For Each ctl In Me.Controls
If ctl.Name Like "CTNR*" Then
'source
ctl.Form.RecordSource = "SELECT tMenus.* FROM tMenus " _
& "WHERE MenuDate=[Formulaires]![fMenuSemaine]![txtJour" & Mid(ctl.Name, 5, 1) & "] " _
& "AND MenuService=" & Right(ctl.Name, 1) & " ORDER BY tMenus.MenuLigne;"
'Liste
ctl.Form!cboPlat.RowSource = "SELECT tPlatsPK, PlatNomF,PlatNomE FROM tPlats " _
& "WHERE Ligne=Replace([Forms]![fMenuSemaine]!" & ctl.Name & ".[form]![txtMenuLigne],6,7);"
End If
Next ctl
End Sub
Explication du code
7-9 : on lance une boucle sur tous les contrôles du formulaire pour les rendre visibles.
10-25 : modification des propriétés des contrôles en fonction des dates de la semaine.
15 : les contrôles nommés txtDatei où « i » varie de 0 à 6
Pour calculer la date qui convient, il suffit d'ajouter à la date txtDateDepart un nombre de jours égal au chiffre qui termine le nom du contrôle.
16-22 : ici, pour chacune des sept dates, on va compléter (éventuellement) la table tMenus pour qu'elle contienne 3 (les trois services) x 10 (les dix items de la carte) soit 30 enregistrements pour chaque journée.
Les requêtes construites à la volée sont du type de celle-ci par exemple pour le lunch (code service = 1) du 4/10/2015
De deux choses l'une :
- les enregistrements existent déjà et l'ajout sera rejeté (voir définition de tMenus) ;
- des enregistrements seront ajoutés pour les dix items de la carte de ce service de cette date, prêts à accueillir le plat qui sera choisi par l'utilisateur. (À ce stade, tPlatsFk vaut 0.)
27-38 : adaptation des 21 sous-formulaires.
30-32 : on crée la source de chacun. En l'occurrence, la portion de tMenus qui le concerne.
Par exemple pour le lunch du jour0 (CTNR01, où « 0 » indique le jour et « 1 » le lunch), cette requête créée à la volée :
34-35 : dans la foulée, on aménage le contenu des zones de liste. Voici un exemple des 21 requêtes construites à la volée, il se rapporte à la liste des items pour le lunch du jour0 :
N.B. Les lignes 6 et 7 de la carte concernent les légumes. Dans tPlats ceux-ci ont la valeur 7 en colonne Ligne.
VII. Le sous-formulaire sfLignes▲
Il est destiné à afficher les items d'une carte dans le formulaire principal :
Sa construction :
Sa source :
VIII. La problématique des zones de liste à contenu variable dans un formulaire continu▲
Le problème vient du fait que lorsqu'on modifie de manière dynamique le contenu de la zone de liste pour en restreindre les choix dans l'enregistrement actif, cette restriction entre automatiquement en vigueur pour tous les autres enregistrements du formulaire.
Cela n'altère pas la valeur des choix qui ont été faits dans les autres enregistrements : l'item qui avait été choisi subsiste tel quel, mais si sa valeur ne figure pas dans la liste de celles ponctuellement permises, Access ne peut plus l'afficher.
VIII-A. Un exemple pour bien se comprendre▲
Supposons que dans cette table :
on veut garnir la colonne idPlatChoisi en picorant dans une liste dont le choix serait limité aux plats qui correspondent à la ligne indiquée.
On pense d'abord à un formulaire comme celui-ci :
Avec ce code sur l'événement « Sur activation » pour actualiser le contenu de la liste à chaque enregistrement :
Option Compare Database
Option Explicit
Private Sub Form_Current()
Me.cboPlat.Requery
End SubCe qui donnerait ceci :
MAIS pour la ligne suivante :
la liste est bien limitée aux bons choix, mais le choix qui avait été opéré pour « Entrée » n'est plus affiché !
De même :
Bref, seul l'enregistrement actif laisse apparaître le choix opéré.
D'ailleurs, si on revient sur l'enregistrement 2 :
le choix s'affiche à nouveau… et les autres disparaissent !
VIII-B. Que se passe-t-il ?▲
Pour bien comprendre, nous allons ajouter un contrôle au formulaire pour afficher la valeur contenue dans idPlatChoisi.
Voici :
Quand on est positionné sur la ligne 2, la requête qui fixe le contenu de la zone de liste limite le choix à 169 et 170 :
… et ni 168 ni 204 ne trouvent leur intitulé !
VIII-C. Une astuce pour sortir de l'impasse▲
Nous allons ajouter un nouveau contrôle au formulaire :
avec comme propriété Source contrôle une fonction de domaine qui recherche dans tPlats l'intitulé correspondant à la valeur mémorisée dans cboPlat (soit idPlatChoisi de cet enregistrement).
Et voilà le travail :
VIII-D. Une dernière retouche pour faire « joli »▲
On réduit la largeur de la zone de liste pour ne laisser apparaître que son carré 
que l'on place à droite de la zone de texte qui contient l'intitulé :
Et on obtient finalement une « illusion » de zone de liste modifiable :
Pas beau ça ?
IX. Le sous-formulaire sfMenuJour▲
Il est destiné à sélectionner le plat de chaque item des 21 cartes :
Sur chaque ligne, une zone de liste modifiable qui permet de choisir un plat compatible avec l'item de la carte (uniquement les soupes sur la 1re ligne, uniquement des entrées sur la 2e, etc.)
Pour ce qui est de la zone de liste modifiable, ce sous-formulaire est conçu suivant la technique décrite au chapitre précédent.
X. L'état eMenuJour▲
L'idée, c'est d'afficher le formulaire fMenu et d'obtenir, en double-cliquant sur une date, un état qui donne la carte bilingue de cette date :
L'objectif est de récupérer les données déjà disponibles dans le formulaire.
X-A. Un squelette : eMenuJourVierge▲
 |
Remarquez comment sont nommés les conteneurs de sous-état  L'objet source du conteneur n'est pas complété. |
X-B. ▲
Pour confectionner l'état, on va prendre une copie de ce squelette, nous l'appellerons eMenuJour et nous complèterons à la volée la date dans txtDate et l'objet source dans chacun des douze conteneurs.
La structure adoptée pour nommer les contrôles nous permettra de factoriser le code.
X-C. Le code du double-clic sur une date de fMenuSemaine▲
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
Option Compare Database
Option Explicit
Private Sub txtJour0_DblClick(Cancel As Integer)
Me.txtDateDepart.SetFocus
Call MenuJour(0)
End Sub
Private Sub txtJour1_DblClick(Cancel As Integer)
Me.txtDateDepart.SetFocus
Call MenuJour(1)
End Sub
Private Sub txtJour2_DblClick(Cancel As Integer)
Me.txtDateDepart.SetFocus
Call MenuJour(2)
End Sub
Private Sub txtJour3_DblClick(Cancel As Integer)
Me.txtDateDepart.SetFocus
Call MenuJour(3)
End Sub
Private Sub txtJour4_DblClick(Cancel As Integer)
Me.txtDateDepart.SetFocus
Call MenuJour(4)
End Sub
Private Sub txtJour5_DblClick(Cancel As Integer)
Me.txtDateDepart.SetFocus
Call MenuJour(5)
End Sub
Private Sub txtJour6_DblClick(Cancel As Integer)
Me.txtDateDepart.SetFocus
Call MenuJour(6)
End Sub
Public Sub MenuJour(NumJour As Integer)
Dim ctl As Control
Dim i As Integer
'Geler temporairement l'affichage à l'écran et les messages
Application.Echo False
DoCmd.SetWarnings False
'Initialiser eMenuJour
DoCmd.CopyObject , "eMenuJour", acReport, "eMenuJourVierge"
'Ouvrir eMenuJour en mode construction
DoCmd.OpenReport "eMenuJour", acViewDesign
'Dupliquer seLignes en seLignes_i_F et E
'---------------------------------------
DoCmd.OpenReport "seLignes", acViewDesign
'En français
Reports!seLignes!txtLigne.ControlSource = "LigneF"
DoCmd.Save acReport, "seLignes"
For i = 1 To 3
'Créer les sous-états
DoCmd.CopyObject , "seLignes" & i & "F", acReport, "seLignes"
'Aménager les ObjectSource des sous-états seLignes_i
Reports!eMenujour("seLignes" & i & "F").SourceObject = "État.seLignes" & i & "F"
Next i
'En anglais
Reports!seLignes!txtLigne.ControlSource = "LigneE"
DoCmd.Save acReport, "seLignes"
For i = 1 To 3
'Créer les sous-états
DoCmd.CopyObject , "seLignes" & i & "E", acReport, "seLignes"
'Aménager les ObjectSource des sous-états seLignes_i
Reports!eMenujour("seLignes" & i & "E").SourceObject = "État.seLignes" & i & "E"
Next i
DoCmd.Close acReport, "seLignes"
'Dupliquer seMenu
'----------------
DoCmd.OpenReport "seMenu", acViewDesign
'En français
Reports!seMenu!txtNom.ControlSource = "NomF"
Reports!seMenu!txtNom.FontSize = 8
DoCmd.Save acReport, "seMenu"
For i = 1 To 3
'Créer les sous-états
DoCmd.CopyObject , "seMenu" & i & "F", acReport, "seMenu"
'Aménager les ObjectSource des sous-états seMenu_i
Reports!eMenujour("seMenu" & i & "F").SourceObject = "État.seMenu" & i & "F"
Next i
'En anglais
Reports!seMenu!txtNom.ControlSource = "NomE"
DoCmd.Save acReport, "seMenu"
For i = 1 To 3
'Créer les sous-états
DoCmd.CopyObject , "seMenu" & i & "E", acReport, "seMenu"
'Aménager les ObjectSource des sous-états seMenu_i
Reports!eMenujour("seMenu" & i & "E").SourceObject = "État.seMenu" & i & "E"
Next i
DoCmd.Close acReport, "seMenu"
'Aménager les dates
Reports!eMenujour!txtDate.ControlSource = _
"=#" & Format(Me("txtJour" & NumJour), "mm/dd/yyyy") & "#"
'Sauvegarder et rouvrir en construction
DoCmd.Save acReport, "eMenujour"
DoCmd.Close acReport, "eMenujour"
DoCmd.OpenReport "eMenujour", acViewDesign
'Aménager les sources
For Each ctl In Reports!eMenujour.Controls
If ctl.Name Like "seMenu*" Then
Reports!eMenujour!(ctl.Name).Report.RecordSource = _
Me("CTNR" & NumJour & Mid(ctl.Name, 7, 1)).Form.RecordSource
End If
Next ctl
'Rétablir l'affichage et les messages
Application.Echo True
DoCmd.SetWarnings True
'Afficher l'état
DoCmd.OpenReport "eMenuJour ", acViewPreview
End Sub
Complément aux commentaires du code
4 - 33 : le double-clic sur chacune des sept dates appelle la sous-routine MenuJour en donnant son numéro d'ordre comme paramètre (0 pour le 1er jour, 6 pour le dernier).
35 - fin : la sous-routine Menujour.
À l'ouverture, la base contient uniquement ces états qui vont servir de modèle :
Dans le code qui suit, nous allons construire à la volée toute une série d'états… que nous supprimerons à la fermeture.
50 - 54 : on crée eMenuJour à l'image de eMenuJourVierge.
56 - 78 : on garnit les conteneurs de type
d'abord en français (60 - 66), puis en anglais (70 - 78).
82 - 104 : on suit la même logique pour garnir les conteneurs de type
111 - 113 : dans certaines circonstances (et c'est le cas ici) Access a du mal à s'y retrouver lorsqu'on fait référence à des contrôles d'un objet que l'on est en train de construire à la volée (en l'occurrence eMenujour). Pour cette raison, on sauvegarde, on ferme et on rouvre en construction eMenujour avant de compléter sa construction.
117 - 122 : on s'occupe ici de l'objet source de chacun des six conteneurs
En fait, on récupère pour chacun, l'objet source du conteneur correspondant sur le formulaire fMenuSemaine.
Après l'exécution de ce code, la fenêtre des objets « États » a évolué :
C'est la fermeture du formulaire fMenuSemaine qui provoquera le « nettoyage » de la base
Private Sub Form_Close()
Dim db As DAO.Database, doc As DAO.Document
'Fermer eMenuJour s'il est encore ouvert
DoCmd.Close acReport, "eMenuJour"
Set db = CurrentDb
For Each doc In db.Containers("Reports").Documents
If doc.Name Like "seMenu#*" Or doc.Name Like "seLignes#*" Or doc.Name = "eMenuJour" Then
DoCmd.DeleteObject acReport, doc.Name
End If
Next doc
Set db = Nothing
DoCmd.Restore
End SubXI. L'état eMenuSemaine▲
On complète les contrôles variables à la volée, la technique est la même que pour eMenuJour.
XI-A. Le code du clic sur le bouton Menu de la semaine▲
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
Private Sub btSemaine_Click()
Dim i As Integer
Dim j As Integer
Dim Reponse As String
'Demander dans quelle langue
Reponse = UCase(InputBox("Quelle langue ?" & Chr(10) & Chr(13) _
& "Pour français, tapez « F »" & Chr(10) & Chr(13) _
& "for English, type « E »"))
If Reponse <> "F" And Reponse <> "E" Then
MsgBox "Choix de langue invalide " & Chr(10) & Chr(13) _
& "Procédure interrompue."
Exit Sub
End If
Application.Echo False
'Aménager les contrôles variables de eMenuSemaine
'Créer les sous-états selon la langue
'Formater seLignes en fonction de la langue
DoCmd.SetWarnings False
DoCmd.OpenReport "seLignes", acDesign
Reports!seLignes!txtLigne.ControlSource = "Ligne" & Reponse
DoCmd.Save acReport, "seLignes"
DoCmd.Close acReport, "seLignes"
DoCmd.SetWarnings True
'Formater seMenu en fonction de la langue
DoCmd.SetWarnings False
DoCmd.OpenReport "seMenu", acDesign
Reports!seMenu!txtNom.ControlSource = "Nom" & Reponse
Reports!seMenu!txtNom.FontSize = 6
DoCmd.Save acReport, "seMenu"
DoCmd.Close acReport, "seMenu"
DoEvents
'Démultiplier
For i = 1 To 3
For j = 0 To 6
DoCmd.CopyObject , "seMenu" & i & j, acReport, "seMenu"
Next j
Next i
DoCmd.SetWarnings True
'Aménager les contrôles de dates
DoCmd.OpenReport "eMenuSemaine", acViewDesign
Reports!eMenuSemaine!txtDu.ControlSource = "=#" & Format(Me.txtDateDepart, "mm/dd/yyyy") & "#"
Reports!eMenuSemaine!txtAu.ControlSource = "=#" & Format(Me.txtDateDepart + 6, "mm/dd/yyyy") & "#"
For i = 0 To 6
Reports!eMenuSemaine("txtDate" & i).ControlSource = "=#" & Format(Me.txtDateDepart + i, "mm/dd/yyyy") & "#"
Next i
'Aménager les sources
For i = 0 To 6
Reports!eMenuSemaine("ctnrLunch" & i).Report.RecordSource = Me("CTNR" & i & 1).Form.RecordSource
Reports!eMenuSemaine("ctnrDinner" & i).Report.RecordSource = Me("CTNR" & i & 2).Form.RecordSource
Reports!eMenuSemaine("ctnrMidnight" & i).Report.RecordSource = Me("CTNR" & i & 3).Form.RecordSource
Next i
DoCmd.Save acReport, "eMenuSemaine"
'Afficher
Application.Echo True
DoCmd.OpenReport "eMenuSemaine", acViewPreview
End Sub
XII. Téléchargement▲
La db (format Access2000) qui a servi à mettre ce processus au point peut être téléchargée ici.
XIII. Remerciements▲
Merci à Malick Seck (milkoseck) et f-leb pour la correction orthographique.