Charger dynamiquement une liste déroulante en VBA

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Charger la source d'une liste déroulante en VBA

Dans cette formation Visual Basic Access, nous souhaitons préparer une application qui permettra d'évaluer les candidats avec des questionnaires à choix multiples. Ici nous nous contenterons de développer le formulaire d'accueil qui permettra à l'utilisateur de choisir, par le biais d'une liste déroulante, le questionnaire sur lequel il souhaite être évalué.


Ces questionnaires dépendent des noms des tables Access dans lesquelles ils ont été bâtis et archivés. Au chargement du formulaire, un code VBA Access doit donc parcourir toutes les tables de la base de données, afin de prélever leur nom et de les charger dans une liste déroulante de choix. Au clic de l'utilisateur sur l'un des questionnaires ainsi proposés, le QCM doit débuter par le biais d'un autre formulaire, chargé dynamiquement des questions et choix de la table correspondante.

Présentation de la base de données des QCM
Pour aller à l'essentiel, nous allons débuter les travaux avec une base de données existante contenant déjà les questionnaires et les deux formulaires de l'application.

• Télécharger la base de données charger-liste-vba.accdb en cliquant sur son lien,
• L'ouvrir dans Access et cliquer sur le bouton Activer le contenu, si nécessaire,
• Double cliquer sur le formulaire Listes depuis le volet gauche des objets Access,

Il s'agit du formulaire qui doit permettre à l'utilisateur de choisir un questionnaire, comme celui illustré par la capture ci-dessus. Mais ce dernier n'est pas encore fonctionnel puisqu'au clic sur la liste déroulante, aucun choix n'est proposé.

C'est tout l'enjeu du code que nous devons développer. Il s'agit certes de charger le contenu d'une liste déroulante par le code Visual Basic Access. Mais il ne s'agit pas de n'importe quel contenu, puisque nous devons être capables de récupérer les informations de tables et plus précisément, leur nom.

La capture suivante, illustre les tables qui composent la base de données et archivent les questionnaires. Il s'agit certes de QCM anciens mais qui feront très bien l'affaire pour développer l'application Access.


Accès aux données en VBA Access
Comme nous l'avions appris dans la formation VBA Access pour accéder aux données, avant de développer le code, nous aurons besoin d'ajouter une référence à la librairie permettant la connexion aux données de la base. Le contrôle Microsoft ActiveX DataObject (ADO ou DAO) permettra alors d'instancier n'importe quel objet de base de données. Nous pourrons ainsi accéder aux propriétés et méthodes nécessaires pour notamment, parcourir les tables de la base et récupérer leur nom. Mais avant cela, nous devons créer la procédure événementielle qui permettra de déclencher le code à l'ouverture du formulaire.

• Cliquer avec le bouton droit sur le formulaire Listes depuis le volet des objets Access,
• Dans le menu contextuel, choisir Mode Création,

Le formulaire s'affiche au centre de l'écran, en mode conception, avec tous les contrôles qui le composent. Le formulaire lui-même est le contrôle actif par défaut. Nous allons donc en profiter pour le paramétrer avec sa feuille de propriétés.

• Si elle n'est pas visible, cliquer sur le bouton Feuille de propriétés du ruban Création,
• Activer l'onglet Evénement de cette feuille de propriétés,
• Cliquer sur le petit bouton de son événement Sur chargement,
• Dans la boîte de dialogue qui suit, choisir Générateur de code et valider par Ok,

Nous basculons ainsi dans l'éditeur de code Visual Basic Access, entre les bornes de la procédure événementielle Form_Load(). Load est l'événement qui se déclenche au chargement du formulaire. En d'autres termes, tout code saisi entre ces bornes, se déclenchera en même temps que le formulaire se charge puis s'ouvre. C'est donc ici que nous devons développer les lignes de programmation permettant de charger la liste déroulante, des noms de tables composant la base de données. Mais avant cela, nous devons ajouter la référence nécessaire au pilotage des objets de base de données.

• Cliquer sur le menu Outils en haut de l'éditeur de code,
• Dans la liste, choisir Références,
• Dans la boîte de dialogue qui suit, cocher la case Microsoft ActiveX Data Objects 6.1 Library,
• Puis valider par Ok,

Le numéro de cette librairie 6.1 peut dépendre de la version Office installée sur votre poste.

Variables Objets pour accéder aux données Access
Comme toujours, nous devons commencer l'implémentation du code VBA par la déclaration des variables nécessaires. Et parmi elles, figurent les variables objets qui permettront d'instancier les classes ADO afin d'hériter de leurs propriétés et méthodes. Dès lors nous serons en mesure d'accéder aux propriétés des objets Access et notamment des tables.

• Entre les bornes de la procédure, saisir les déclarations VBA suivantes :

Dim la_base As Database
Dim chaque_table As TableDef
Dim tabval(20)
Dim i As Integer, j As Integer
Dim nouvelle_table As TableDef : Dim nouveau_champ As Field : Dim nouvel_enregistrement As Recordset
Dim nom_table As String, nom_champ As String

Tout d'abord, grâce à la référence à la librairie Microsoft ActiveX Data Objects, nous pouvons déclarer un objet en tant que base de données (Dim la_base As Database). Nous l'utiliserons pour pointer sur la base de données en cours. Dès lors, nous pourrons manipuler ses éléments, comme ses tables. Nous déclarons ensuite un objet qui pourra accéder à la définition stockée des tables (Dim chaque_table As TableDef). Une boucle les parcourant toutes, permettra alors d'en extraire les noms respectifs. Puis nous déclarons un tableau de valeurs (Dim tabval(20)), limité arbitrairement à 20 éléments pour stocker temporairement les informations prélevées des noms de tables. Les variables entières i et j seront utilisées pour réaliser des boucles de traitements. Les variables objets nouvelle_table, nouveau_champ et nouvel_enregistrement seront exploitées pour créer une table temporaire dans laquelle seront stockées les informations prélevées sur les tables de la base de données. Cette table temporaire servira alors de source de données pour la liste déroulante proposant les noms des QCM. Enfin, les variables nom_table et nom_champ déclarées comme des chaînes de caractères (String) permettront de piloter cette table.

Après la déclaration, suit l'initialisation et l'affectation des variables.

• A la suite du code VBA, ajouter les affectations suivantes :

i = 0
nom_table = 'Liste_tables'
nom_champ = 'Questionnaire'
Set la_base = Application.CurrentDb

La variable i servira de compteur pour insérer chacun des noms de table prélevés, dans le tableau de valeur précédemment déclaré. Nous l'initialisons donc à zéro, soit sur l'indice du premier élément à insérer. Les variables nom_table et nom_champ stockent respectivement le nom de la table temporaire et de son champ dans lequel seront ajoutés les titres des questionnaires. Enfin, nous initialisons la variable objet la_base grâce au mot clé Set sur la base de données en cours. C'est la méthode CurrentDb de l'objet VBA Access Application qui permet de pointer sur la base de données courante.

Nous devons désormais parcourir toutes les tables de la base de données. Pour ce faire nous devons exploiter la variable objet chaque_table afin de parcourir et d'accéder à la définition de chacune d'elle. Et pour parcourir chacun de ces objets, nous devons utiliser une boucle For Each.

• En conséquence, ajouter les bornes de la boucle For Each comme suit :

For Each chaque_table In la_base.TableDefs

Next

Une boucle For Each se borne avec le mot clé Next. Les éléments parcourus se situent à droite du mot clé In dans la syntaxe de la boucle. La propriété TableDefs d'un objet base de données est une collection qui permet d'accéder à toutes les tables qu'elle contient. L'élément à gauche du mot clé In doit être du même type que le groupe parcouru. C'est pourquoi nous avions déclaré l'objet chaque_table comme un TableDef.

Les tables système ne sont pas visibles par l'utilisateur mais sont accessibles par le code. Il faut donc les exclure du traitement. Nous devons réaliser un test sur le nom de chacune des tables dans la boucle, grâce à une instruction conditionnelle If pour vérifier la condition. Le nom des tables système commence toujours par les trois lettres Msy.

• Dans la boucle, ajouter le test pour exclure les tables système du traitement :

If UCase( Left( chaque_table.Name, 3)) <> 'MSY' And Left( chaque_table.Name, 1) <> '~' And chaque_table.Name <> 'Liste_tables' Then

End If

Grâce à la propriété Name de l'objet TableDef, nous récupérons le nom de la table en cours de lecture dans la boucle. Nous n'en prélevons que les trois premières lettres grâce à la fonction VBA Left et son second paramètre (3). Nous convertissons ces trois caractères prélevés en majuscules pour éviter les problèmes de casse dans la comparaison. Nous cherchons à savoir s'ils sont bien différents des trois lettres (MSY) du préfixe des tables système. Dans la foulée, nous vérifions un autre critère (AND) pour nous assurer que le nom de la table ne commence pas par le symbole ~. Ce cas se produit lorsque des tables temporaires sont supprimées. Enfin nous excluons du filtre la table temporaire potentielle que nous utiliserons pour enregistrer les informations (chaque_table.Name <> 'Liste_tables').

Si ces trois conditions sont vérifiées, nous savons que les tables restantes correspondent bien aux questionnaires dont nous souhaitons prélever les noms. Pour nous en assurer, nous allons réaliser un test grâce à la fonction MsgBox qui permet d'afficher un message à l'écran.

• Dans les bornes de l'instruction If de la boucle For Each, ajouter la ligne de code suivante :

MsgBox chaque_table.Name

• Enregistrer les modifications (CTRL + S),
• Basculer sur le formulaire Access (ALT + F11),
• Exécuter ce dernier (F5),

Le formulaire apparaît et affiche effectivement le premier nom de table présent dans la base de données. Il faut cliquer jusqu'à atteindre le dernier nom pour que l'exécution du code se termine. Cet essai a permis de confirmer que nous parvenions bien à accéder aux informations des tables de la base de données.

• Afficher de nouveau le formulaire en mode Création,
• Basculer dans l'éditeur de code Visual Basic Access (ALT + F11),
• Supprimer la ligne précédente du MsgBox,
• A la place, ajouter les instructions de code suivantes :

i = i + 1
tabval(i) = chaque_table.Name

Ainsi, nous stockons chacun des noms prélevés (chaque_table.Name) dans une ligne du tableau de valeur (tabval(i) = chaque_table.Name) après avoir incrémenté l'indice qui permet de désigner la position (i = i + 1).

Avant de pouvoir stocker ces informations dans la table temporaire, nous devons commencer par la supprimer, si elle a été générée par une précédente ouverture du formulaire. Comme elle est exclue du traitement par le critère de l'instruction conditionnelle If, nous devons ajouter cette nouvelle ligne de code après le End If, mais toujours dans la boucle.

• Après le End If mais avant le Next qui clôture la boucle, ajouter la ligne suivante :

If chaque_table.Name = nom_table Then
la_base.TableDefs.Delete nom_table

L'instruction If permet de vérifier si le nom de la table est bien celui de la table prévue pour stocker tous les noms de questionnaires (nom_table). Si c'est le cas, la méthode Delete de la propriété TableDefs de l'objet de base de données la_base permet de supprimer cette table, en lui passant en paramètre, son nom mémorisé par la variable.

Créer et paramétrer une table en VBA Access
Nous pouvons désormais récréer cette table vierge. Nous lui associerons un seul champ qui permettra d'ajouter pour chaque enregistrement, le nom de chaque questionnaire, représenté par le nom de chaque table parcourue. Ce traitement ne doit être réalisé qu'une seule fois. Le code doit donc être écrit en dehors de la boucle. C'est la raison pour laquelle nous avons mémorisé temporairement ces noms dans le tableau de valeurs tabval. Pour créer cette table, nous devons exploiter les variables objets déclarées en début de code.

• A la suite du code, après la boucle, soit après le mot clé Next, ajouter les instructions suivantes :

Set nouvelle_table = la_base.CreateTableDef(nom_table)
Set nouveau_champ = nouvelle_table.CreateField(nom_champ, dbText, 25)
nouvelle_table.Fields.Append nouveau_champ
la_base.TableDefs.Append nouvelle_table

Set nouvelle_table = Nothing
Set nouveau_champ = Nothing

Le mot clé Set est utilisé une fois de plus pour initialiser et instancier les objets de base de données qui avaient été déclarés à cet effet. Ainsi la méthode CreateTableDef de l'objet base de données permet de créer une table du nom passé en paramètre. Mais ce n'est pas suffisant, une table doit avoir au moins un champ typé. Et la création du champ comme celle de la table doivent être confirmées par des méthodes VBA précises. C'est pourquoi nous exploitons la méthode CreateField de l'objet fraîchement instancié afin de créer le champ nécessaire passé en paramètre (nom_champ), avec son type (dbText) et sa longueur (25).

C'est alors que la méthode Append de la propriété Fields de l'objet de création de table, permet de confirmer la création du champ représenté par l'objet passé en paramètre, donc avec toutes ses propriétés. Cette même méthode, cette fois de la propriété TableDefs de l'objet de base de données, permet de confirmer la création de la table, avec l'objet passé en paramètre, donc avec toutes ses propriétés.

Enfin, comme nous l'avions appris dans la formation VBA pour archiver les données de base Access, le mot clé Nothing permet de décharger ces objets, qui ne sont plus exploités. Enfin, maintenant que la table est créée, nous devons y accéder par le code, pour ajouter les enregistrements, dont les informations sont contenues dans le tableau de valeur.

Nous allons tester que ces lignes de code ont bien permis de créer la table à la volée.

• Enregistrer les modifications (CTRL + S),
• Basculer sur le formulaire Access (ALT + F11) et exécuter ce dernier (F5),

A notre grande surprise, une erreur est générée. Il s'agit d'une incompatibilité de type. Aussi étonnant que cela puisse paraître, c'est la référence à la librairie Microsoft ActiveX Data Objects qui pose conflit. Nous devons la désactiver et adapter les déclarations des variables objets afin d'accéder seulement aux classes nécessaires, afin que tout conflit soit levé.

• Cliquer sur le bouton Réinitialiser en haut de l'éditeur de code pour stopper l'exécution,
• Dérouler le menu Outils et cliquer sur Références dans la liste,
• Décocher la case Microsoft ActiveX Data Objects 6.1 Library,
• Ajouter le préfixe DAO dans la déclaration des variables d'objets de base de données :

Dim la_base As DAO.Database
Dim chaque_table As DAO.TableDef
Dim tabval(20)
Dim i As Integer, j As Integer
Dim nouvelle_table As DAO.TableDef : Dim nouveau_champ As DAO.Field : Dim nouvel_enregistrement As DAO.Recordset
Dim nom_table As String, nom_champ As String

• Enregistrer les modifications (CTRL + S),
• Basculer sur le formulaire Access (ALT + F11) et exécuter ce dernier (F5),

Comme vous le constatez, la nouvelle table apparaît bien dans le volet de gauche des objets de la base de données Access. Bien sûr à ce stade, si vous double cliquez sur son nom pour l'ouvrir, vous constatez qu'elle est vide. Nous n'avons pas encore écrit le code VBA permettant de lui ajouter des enregistrements.

Ajouter des enregistrements à une table en VBA
Dans la formation permettant d'archiver les données en VBA Access, nous avions appris à ajouter des enregistrements dans une table existante, par le code Visual Basic Access. Nous allons répliquer ce code.

• Afficher le formulaire en mode création puis basculer dans l'éditeur de code (ALT + F11),
• A la suite du code, ajouter les instructions suivantes :

Set nouvel_enregistrement = la_base.OpenRecordset(nom_table, dbOpenDynaset)
For j = 1 To i
nouvel_enregistrement.AddNew
nouvel_enregistrement.Fields(nom_champ) = tabval(j)
nouvel_enregistrement.Update
Next j

La méthode OpenRecordset d'un objet de base de données, permet d'accéder aux enregistrements de la table dont le nom est désigné en premier paramètre (nom_table). Le deuxième paramètre de la méthode, dbOpenDynaset indique le mode d'accès à la table. Il s'agit ici d'un accès en mode modification. Il doit y avoir un nouvel enregistrement pour chaque questionnaire à recenser. Donc nous construisons une boucle capable de parcourir tous les noms mémorisés dans le tableau de valeurs (For j = 1 To i). 1 est le premier élément tandis que le dernier est référencé à la dernière valeur enregistrée par l'incrément de la variable i. Ensuite et comme nous l'avions appris, la méthode AddNew d'un objet RecordSet permet d'initialiser la création d'un enregistrement. Chronologiquement, suivent les informations à renseigner pour les champs de l'enregistrement à créer (nouvel_enregistrement.Fields(nom_champ) = tabval(j)). Puis la méthode Update d'un objet RecordSet, permet de confirmer et valider la création de l'enregistrement.

Nous n'avons plus besoin des objets de base de données. Afin de coder proprement nous devons les décharger.

• Ajouter les lignes permettant de vider les variables objets de base de données de la mémoire :

Set nouvel_enregistrement = Nothing
Set la_base = Nothing

• Enregistrer les modifications (CTRL + S),
• Basculer sur le formulaire Access (ALT + F11) et l'exécuter (F5),
• Double cliquer sur le nom de la table ListeTables depuis le volet des objets Access,

Comme vous le remarquez, un enregistrement a été créé par questionnaire, correspondant chacun au nom de la table qui lui est associé. Mais malgré tout, la liste déroulante du formulaire est toujours vide. En effet, nous avons bien rempli la table des informations nécessaires mais nous ne l'avons pas encore désignée comme source de données de la liste déroulante.

• Fermer la table et afficher le formulaire en mode création,
• Basculer dans l'éditeur de code Visual Basic,

Un contrôle de formulaire se pilote dans le code VBA par son nom d'objet. C'est lui qui permet d'accéder aux propriétés et méthodes. Le nom défini pour la liste déroulante à charger est Liste.

• A la suite du code, ajouter les deux instructions suivantes :

Liste.RowSourceType = 'Table/Requête'
Liste.RowSource = 'ListeTables'

Nous devons d'abord définir quelle est la nature de la source de données pour la liste déroulante, comme si nous le faisions depuis la feuille de propriétés du formulaire. C'est la propriété RowSourceType de l'objet zone de liste qui, affecté à la valeur Table/Requête, permet d'indiquer que la source est une table. Alors, sa propriété RowSource, affectée au nom de la table établit le lien.

• Enregistrer les modifications, basculer sur le formulaire et l'exécuter,

Le lien est cette fois établi. Le code permettant de charger le contenu d'une zone de liste avec les noms de tables de la base de données, a fonctionné. Cependant, si vous cliquez sur l'un des sujets, rien ne se produit pour l'instant. Nous n'avons pas encore écrit le code permettant de transmettre les informations nécessaires au formulaire question, afin qu'il charge la table correspondante du QCM. C'est l'objet de la prochaine formation permettant de modifier des données d'un formulaire depuis un formulaire externe. Ainsi petit à petit, nous allons monter l'application finale qui permettra d'évaluer les candidats selon leur choix de questionnaire.