Un formulaire (état) qui contient de multiples fois le même sous-formulaire (sous-état), chacun avec une source différente

Claude Leloup

- Dans cette application, on a choisi de construire un formulaire qui contient 21 fois le même sous-formulaire avec pour chacun une source différente. Ces derniers sont construits à la volée.

- Un procédé identique sera présenté pour un état.

- On verra aussi comment il est possible, dans un formulaire continu, d'avoir une zone de liste modifiable dont le contenu est spécifique à chaque enregistrement.

Les commentaires et les suggestions d'amélioration sont les bienvenus, alors, après votre lecture, n'hésitez pas Commentez

- Titre : Un formulaire (état) qui contient de multiples fois le même sous-formulaire (sous-état), chacun avec une source différente

- Auteur : Claude Leloup

- Parution : 30 novembre 2015

- Mise à jour : 4 décembre 2015

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2015 Claude Leloup. 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. Prérequis

- II. Le but de l'exercice

- III. L'idée

- IV. Les difficultés

- V. La table tMenus

- VI. Le formulaire fMenuSemaine

- VI-A. Les pièces du puzzle

- VI-B. Le code déclenché par la mise à jour de txtDateDepart

- VII. Le sous-formulaire sfLignes

- VIII. La problématique des zones de liste à contenu variable dans un formulaire continu

- VIII-A. Un exemple pour bien se comprendre

- VIII-B. Que se passe-t-il ?

- VIII-C. Une astuce pour sortir de l'impasse

- VIII-D. Une dernière retouche pour faire « joli »

- IX. Le sous-formulaire sfMenuJour

- X. L'état eMenuJour

- X-A. Un squelette : eMenuJourVierge

- X-B.

- X-C. Le code du double-clic sur une date de fMenuSemaine

- XI. L'état eMenuSemaine

- XI-A. Le code du clic sur le bouton Menu de la semaine

- XII. Téléchargement

- XIII. Remerciements

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

1. Private Sub txtDateDepart_AfterUpdate()

2. Dim ctl As Control

3. Dim sSqlDebut As String

4. Dim sSql As String

5. Dim i As Integer

6. 'Rendre les contrôles visibles

7. For Each ctl In Me.Controls

8. ctl.Visible = True

9. Next ctl

10. 'Aménager les dates

11. DoCmd.SetWarnings False

12. For Each ctl In Me.Controls

13. If ctl.Name Like "txtJour#" Then

14. 'Garnir les contrôles

15. ctl = Me.txtDateDepart + Right(ctl.Name, 1)

16. 'Créer (éventuellement) les enregistrements vierges avec ces dates dans la table tMenus

17. DoCmd.RunSQL "INSERT INTO tMenus ( MenuDate, MenuService, MenuLigne ) " _

18. & "SELECT #" & Format(ctl, "mm/dd/yyyy") & "# AS Expr1, 1 AS Expr2, tMatrice.MenuLigne FROM tMatrice;"

19. DoCmd.RunSQL "INSERT INTO tMenus ( MenuDate, MenuService, MenuLigne ) " _

20. & "SELECT #" & Format(ctl, "mm/dd/yyyy") & "# AS Expr1, 2 AS Expr2, tMatrice.MenuLigne FROM tMatrice;"

21. DoCmd.RunSQL "INSERT INTO tMenus ( MenuDate, MenuService, MenuLigne ) " _

22. & "SELECT #" & Format(ctl, "mm/dd/yyyy") & "# AS Expr1, 3 AS Expr2, tMatrice.MenuLigne FROM tMatrice;"

23. End If

24. Next ctl

25. DoCmd.SetWarnings True

26. 'Construire la source de chaque sous-formulaire et le contenu des listes

27. For Each ctl In Me.Controls

28. If ctl.Name Like "CTNR*" Then

29. 'source

30. ctl.Form.RecordSource = "SELECT tMenus.* FROM tMenus " _

31. & "WHERE MenuDate=[Formulaires]![fMenuSemaine]![txtJour" & Mid(ctl.Name, 5, 1) & "] " _

32. & "AND MenuService=" & Right(ctl.Name, 1) & " ORDER BY tMenus.MenuLigne;"

33. 'Liste

34. ctl.Form!cboPlat.RowSource = "SELECT tPlatsPK, PlatNomF,PlatNomE FROM tPlats " _

35. & "WHERE Ligne=Replace([Forms]![fMenuSemaine]!" & ctl.Name & ".[form]![txtMenuLigne],6,7);"

37. End If

38. Next ctl

39. 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 Sub

Ce 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

1. Option Compare Database

2. Option Explicit

4. Private Sub txtJour0_DblClick(Cancel As Integer)

5. Me.txtDateDepart.SetFocus

6. Call MenuJour(0)

7. End Sub

9. Private Sub txtJour1_DblClick(Cancel As Integer)

10. Me.txtDateDepart.SetFocus

11. Call MenuJour(1)

12. End Sub

14. Private Sub txtJour2_DblClick(Cancel As Integer)

15. Me.txtDateDepart.SetFocus

16. Call MenuJour(2)

17. End Sub

19. Private Sub txtJour3_DblClick(Cancel As Integer)

20. Me.txtDateDepart.SetFocus

21. Call MenuJour(3)

22. End Sub

23. Private Sub txtJour4_DblClick(Cancel As Integer)

24. Me.txtDateDepart.SetFocus

25. Call MenuJour(4)

26. End Sub

28. Private Sub txtJour5_DblClick(Cancel As Integer)

29. Me.txtDateDepart.SetFocus

30. Call MenuJour(5)

31. End Sub

33. Private Sub txtJour6_DblClick(Cancel As Integer)

34. Me.txtDateDepart.SetFocus

35. Call MenuJour(6)

36. End Sub

38. Public Sub MenuJour(NumJour As Integer)

40. Dim ctl As Control

41. Dim i As Integer

43. 'Geler temporairement l'affichage à l'écran et les messages

44. Application.Echo False

45. DoCmd.SetWarnings False

47. 'Initialiser eMenuJour

48. DoCmd.CopyObject , "eMenuJour", acReport, "eMenuJourVierge"

50. 'Ouvrir eMenuJour en mode construction

51. DoCmd.OpenReport "eMenuJour", acViewDesign

53. 'Dupliquer seLignes en seLignes_i_F et E

54. '---------------------------------------

55. DoCmd.OpenReport "seLignes", acViewDesign

56. 'En français

57. Reports!seLignes!txtLigne.ControlSource = "LigneF"

58. DoCmd.Save acReport, "seLignes"

59. For i = 1 To 3

60. 'Créer les sous-états

61. DoCmd.CopyObject , "seLignes" & i & "F", acReport, "seLignes"

62. 'Aménager les ObjectSource des sous-états seLignes_i

63. Reports!eMenujour("seLignes" & i & "F").SourceObject = "État.seLignes" & i & "F"

64. Next i

65. 'En anglais

66. Reports!seLignes!txtLigne.ControlSource = "LigneE"

67. DoCmd.Save acReport, "seLignes"

68. For i = 1 To 3

69. 'Créer les sous-états

70. DoCmd.CopyObject , "seLignes" & i & "E", acReport, "seLignes"

71. 'Aménager les ObjectSource des sous-états seLignes_i

72. Reports!eMenujour("seLignes" & i & "E").SourceObject = "État.seLignes" & i & "E"

73. Next i

74. DoCmd.Close acReport, "seLignes"

76. 'Dupliquer seMenu

77. '----------------

78. DoCmd.OpenReport "seMenu", acViewDesign

79. 'En français

80. Reports!seMenu!txtNom.ControlSource = "NomF"

81. Reports!seMenu!txtNom.FontSize = 8

82. DoCmd.Save acReport, "seMenu"

83. For i = 1 To 3

84. 'Créer les sous-états

85. DoCmd.CopyObject , "seMenu" & i & "F", acReport, "seMenu"

86. 'Aménager les ObjectSource des sous-états seMenu_i

87. Reports!eMenujour("seMenu" & i & "F").SourceObject = "État.seMenu" & i & "F"

88. Next i

89. 'En anglais

90. Reports!seMenu!txtNom.ControlSource = "NomE"

91. DoCmd.Save acReport, "seMenu"

92. For i = 1 To 3

93. 'Créer les sous-états

94. DoCmd.CopyObject , "seMenu" & i & "E", acReport, "seMenu"

95. 'Aménager les ObjectSource des sous-états seMenu_i

96. Reports!eMenujour("seMenu" & i & "E").SourceObject = "État.seMenu" & i & "E"

97. Next i

98. DoCmd.Close acReport, "seMenu"

100. 'Aménager les dates

101. Reports!eMenujour!txtDate.ControlSource = _

102. "=#" & Format(Me("txtJour" & NumJour), "mm/dd/yyyy") & "#"

104. 'Sauvegarder et rouvrir en construction

105. DoCmd.Save acReport, "eMenujour"

106. DoCmd.Close acReport, "eMenujour"

107. DoCmd.OpenReport "eMenujour", acViewDesign

109. 'Aménager les sources

110. For Each ctl In Reports!eMenujour.Controls

111. If ctl.Name Like "seMenu*" Then

112. Reports!eMenujour!(ctl.Name).Report.RecordSource = _

113. Me("CTNR" & NumJour & Mid(ctl.Name, 7, 1)).Form.RecordSource

114. End If

115. Next ctl

117. 'Rétablir l'affichage et les messages

118. Application.Echo True

119. DoCmd.SetWarnings True

121. 'Afficher l'état

122. DoCmd.OpenReport "eMenuJour ", acViewPreview

123. 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 Sub

XI - 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

1. Private Sub btSemaine_Click()

2. Dim i As Integer

3. Dim j As Integer

4. Dim Reponse As String

5. 'Demander dans quelle langue

6. Reponse = UCase(InputBox("Quelle langue ?" & Chr(10) & Chr(13) _

7. & "Pour français, tapez « F »" & Chr(10) & Chr(13) _

8. & "for English, type « E »"))

9. If Reponse <> "F" And Reponse <> "E" Then

10. MsgBox "Choix de langue invalide " & Chr(10) & Chr(13) _

11. & "Procédure interrompue."

12. Exit Sub

13. End If

14. Application.Echo False

15. 'Aménager les contrôles variables de eMenuSemaine

16. 'Créer les sous-états selon la langue

17. 'Formater seLignes en fonction de la langue

18. DoCmd.SetWarnings False

19. DoCmd.OpenReport "seLignes", acDesign

20. Reports!seLignes!txtLigne.ControlSource = "Ligne" & Reponse

21. DoCmd.Save acReport, "seLignes"

22. DoCmd.Close acReport, "seLignes"

23. DoCmd.SetWarnings True

24. 'Formater seMenu en fonction de la langue

25. DoCmd.SetWarnings False

26. DoCmd.OpenReport "seMenu", acDesign

27. Reports!seMenu!txtNom.ControlSource = "Nom" & Reponse

28. Reports!seMenu!txtNom.FontSize = 6

29. DoCmd.Save acReport, "seMenu"

30. DoCmd.Close acReport, "seMenu"

31. DoEvents

32. 'Démultiplier

33. For i = 1 To 3

34. For j = 0 To 6

35. DoCmd.CopyObject , "seMenu" & i & j, acReport, "seMenu"

36. Next j

37. Next i

38. DoCmd.SetWarnings True

39. 'Aménager les contrôles de dates

40. DoCmd.OpenReport "eMenuSemaine", acViewDesign

41. Reports!eMenuSemaine!txtDu.ControlSource = "=#" & Format(Me.txtDateDepart, "mm/dd/yyyy") & "#"

42. Reports!eMenuSemaine!txtAu.ControlSource = "=#" & Format(Me.txtDateDepart + 6, "mm/dd/yyyy") & "#"

43. For i = 0 To 6

44. Reports!eMenuSemaine("txtDate" & i).ControlSource = "=#" & Format(Me.txtDateDepart + i, "mm/dd/yyyy") & "#"

45. Next i

46. 'Aménager les sources

47. For i = 0 To 6

48. Reports!eMenuSemaine("ctnrLunch" & i).Report.RecordSource = Me("CTNR" & i & 1).Form.RecordSource

49. Reports!eMenuSemaine("ctnrDinner" & i).Report.RecordSource = Me("CTNR" & i & 2).Form.RecordSource

50. Reports!eMenuSemaine("ctnrMidnight" & i).Report.RecordSource = Me("CTNR" & i & 3).Form.RecordSource

51. Next i

52. DoCmd.Save acReport, "eMenuSemaine"

53. 'Afficher

54. Application.Echo True

55. DoCmd.OpenReport "eMenuSemaine", acViewPreview

56. 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.