Relier des listes déroulantes sur un formulaire Access

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


Sujets que vous pourriez aussi aimer :


Synthétiser et relier les données par requêtes Access

Nous proposons ici de bâtir une application de liaison dynamique des données, entre un classeur Excel et une base de données Access. L'objectif est de relever ce défi sans intervention du code VBA Excel et avec un minimum de calculs. Nous allons décomposer cette formation en trois volets. Dans ce premier volet, il s'agit de préparer le terrain en créant des requêtes de synthèse capables de fournir des listes uniques de valeurs, purgées de leurs doublons. Nous devons de même concevoir un formulaire de saisie pour les nouvelles entrées, avec des listes déroulantes reliées entre elles par le jeu de requêtes aux critères dynamiques.



L'objectif sera de récupérer dynamiquement depuis Excel, ces données préparées et de constater qu'elles s'actualisent automatiquement, au fur et à mesure des nouvelles entrées insérées dans la base de données, que nous imaginons hébergée sur un intranet ou un serveur distant.

Source et présentation de la problématique
Nous proposons de débuter les travaux de synthèse et de relation dynamique, à partir d'une petite base de données existante.

• Télécharger la base de données donnees-externes.accdb en cliquant sur son lien,
• L'ouvrir dans Access et cliquer sur le bouton Activer le contenu du bandeau de sécurité,

Comme vous le remarquez, cette base de données est constituée d'une seule table nommée Parc.

• Depuis l'explorateur d'objets sur la gauche, double cliquer sur la table Parc pour l'ouvrir,

Cette table recense des véhicules en transit dans le parc automobile d'une société de distribution, après contrôles d'usage. Quatre champs permettent de décrire chacune des voitures. Elles sont donc référencées bien sûr par leur immatriculation et catégorisées selon la marque et le modèle. Une dernière information renseigne sur le nombre de chevaux. Cette base de données a volontairement été restreinte et simplifiée dans sa construction, pour concentrer notre attention sur la liaison dynamique des données. En effet, comme nous l'avait appris la formation pour débuter avec Access, il serait plus judicieux de référencer les marques dans une table parente liée, afin de ne pas les répéter.



Néanmoins, cette base de données est totalement fonctionnelle pour recevoir nos travaux de préparation des données, avant de réaliser la liaison pour exploitation.

Listes évolutives sans doublons
L'application finale Excel devra permettre de questionner les données liées à la base de données externe pour en extraire l'information, selon des choix recoupés par des listes déroulantes reliées entre elles. Ces listes doivent se nourrir de valeurs uniques, donc purgées de leurs doublons. Une première liste permettra de choisir une marque de véhicule parmi les autres. Une seconde liste permettra de choisir un modèle de cette marque, définie en amont, parmi les autres. Donc nous devons concevoir des requêtes capables d'énumérer de façon unique, les modèles pour chaque marque recensée. Comme il s'agit de requêtes, si de nouvelles marques et modèles sont insérés, leurs sélections s'actualiseront dynamiquement. Et comme l'objectif est de réaliser la liaison dynamique, ces nouvelles données apparaîtront automatiquement sur la feuille de calcul Excel liée.

• Fermer la table Parc en cliquant sur la croix de son onglet,
• Cliquer sur l'onglet Créer en haut de la fenêtre Access pour activer son ruban,
• Dans la section Requêtes du ruban, cliquer sur le bouton Création de requête,
• Avec la boîte de dialogue qui suit, ajouter la table Parc et cliquer sur le bouton Fermer,

Nous basculons ainsi dans l'éditeur de requêtes Access. L'objectif de cette première requête consiste à lister tous les modèles de la marque Renault, sans doublons. Une fois terminée, nous la dupliquerons pour créer les requêtes des autres marques, en adaptant le critère bien entendu.

• Glisser les champs Marque et Modèle de la représentation schématique de la table, vers la grilles de requête,
• Dans la zone Critères pour le champ Marque, taper le texte Renault,
• Puis, décocher la case de sa zone Afficher,

En effet, nous souhaitons seulement afficher la liste des modèles pour la marque. Donc, en même temps que le critère doit être spécifié sur ce champ, ce dernier doit être masqué. Il constitue seulement un moyen pour obtenir le résultat de la sélection pour la requête.

• Cliquer sur le bouton Exécuter en haut à gauche du ruban Créer,

Nous obtenons bien la liste des modèles pour la marque Renault. Et comme vous le constatez, des doublons apparaissent fort logiquement. La méthode la plus simple pour produire une requête sélection éliminant les doublons, consiste à exploiter la syntaxe SQL avec la clause Distinct.

• Cliquer sur la flèche du bouton Affichage, tout à fait à gauche dans le ruban Accueil,
• Dans la liste, choisir Mode SQL,

Nous basculons dans l'éditeur Sql des requêtes. Ce dernier livre la syntaxe de la requête transcrite dans ce langage, telle que nous l'avons construite depuis l'éditeur.

SELECT Parc.Modèle FROM Parc WHERE (((Parc.Marque)='Renault'));

Le nom de la table (Parc) est rappelé en préfixe de chaque Champ. Il n'est pas nécessaire puisqu'il ne s'agit pas d'une requête multi-table. De même, la clause WHERE ne présente qu'un seul critère pour la correspondance sur la marque. Donc les parenthèses, elles aussi, ne sont pas nécessaires. Il s'agit de purger la syntaxe en ajoutant l'instruction DISTINCT, juste après le mot clé SELECT.

• En conséquence, modifier la syntaxe de la requête SQL, comme suit :

SELECT DISTINCT Modèle FROM Parc WHERE Marque = 'Renault';

L'instruction DISTINCT demande explicitement d'extraire les valeurs uniques du champ Modèle, lorsque pour l'enregistrement, la valeur du champ Marque vaut Renault.

• Cliquer de nouveau sur le bouton Exécuter en haut à gauche du ruban Créer,

Le nombre d'enregistrements isolés par la requête passe de 17 à 13. Tous les doublons ont en effet disparu. Les modèles ont été sélectionnés de façon distinctive pour la marque spécifiée, dans la clause WHERE de la requête. Il s'agit de réitérer exactement le même processus afin de préparer les requêtes permettant de lister tous les modèles distincts, pour chaque marque.

• Dans le ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans la liste, choisir Mode création,
• Réaliser le raccourci clavier CTRL + S pour l'enregistrer,
• Dans la boîte de dialogue qui surgit, la nommer R_Renault,
• Puis, fermer la requête en cliquant sur la croix de son onglet,
• Dans le volet des objets Access sur la gauche, sélectionner la requête R_Renault,
• La copier, par exemple à l'aide du raccourci clavier CTRL + C,
• Puis, la coller à l'aide du raccourci clavier CTRL + V,
• Dans la petite boîte de dialogue qui surgit, la renommer R_Audi et valider par Ok,
• Cliquer avec le bouton droit de la souris, sur la requête R_Audi,
• Dans le menu contextuel qui apparaît, choisir Mode création,
• Dans la grille de requête, remplacer le critère Renault par le critère Audi,
• Puis, sauvegarder les modifications et fermer la requête,
• En répliquant scrupuleusement les mêmes étapes (Nom et critère), créer les requêtes : R_Bmw, R_Citroen, R_Fiat, R_Mercedes, R_Nissan, R_Peugeot et R_Volkswagen,

Toutes les requêtes qui permettront d'alimenter dynamiquement les listes déroulantes sans doublons, sur la feuille Excel, sont prêtes. Il est important de comprendre que ces requêtes sont dynamiques. Elles ne sont pas figées à l'instant t. Si de nouveaux modèles sont ajoutés, ils sont automatiquement sélectionnés et restitués par chacune des requêtes. Et comme la liaison que nous allons établir à cette base depuis Excel est elle-même dynamique, si de nouveaux éléments sont extraits par les requêtes, ils sont automatiquement restitués sur la feuille de calcul.

Formulaire d'ajout et de manipulation de données
Pour l'intérêt de l'application finale, nous avons besoin d'un formulaire simple, facilitant l'insertion de nouveaux véhicules dans le parc automobile. Chaque ajout pourra ainsi être directement visualisé et constaté sur la feuille Excel qui, par le biais de la liaison dynamique, pourra manipuler les données actualisées et restituées. Nous ne souhaitons pas nous attarder sur la conception de ce formulaire. Mais si vous souhaitez le personnaliser, vous pouvez vous référer à la formation sur la création et le paramétrage des formulaires Access.

• Dans le volet des objets Access sur la gauche de l'écran, sélectionner la table Parc,
• Puis, cliquer sur l'onglet Créer en haut de la fenêtre Access, pour activer son ruban,
• Dans la section Formulaires, cliquer sur le bouton Formulaire,

Comme la table concernée était présélectionnée, Access propose désormais la création instantanée du formulaire avec agencement automatique des champs issus de cette table. Vous remarquez que des rubans contextuels apparaissent. Il s'agit des rubans Création, Organiser et Format. Ils sont dédiés à la mise en forme, à la mise en page et à l'organisation du formulaire.



Même si nous ne souhaitons pas nous attarder sur la conception de ce formulaire, nous souhaitons simplifier tant que possible, la saisie des nouvelles entrées. Des automatisations peuvent être apportées sur les champs Marque et Modèle par le biais de listes déroulantes reliées entre elles. Et pour remplir dynamiquement le contenu de ces listes, des requêtes intermédiaires sont nécessaires. C'est entre autres ce que nous avait appris la formation Access pour extraire et filtrer les données par le biais de listes déroulantes.

La première requête doit simplement extraire les marques de la table Parc, de façon unique. L'instruction DISTINCT dans la syntaxe SQL est donc de nouveau à l'honneur. Ensuite, il s'agira de faire le lien entre le champ du formulaire et la requête. La seconde requête est légèrement plus complexe en revanche. Son critère n'est pas défini à l'avance. Il dépend du choix réalisé en amont par l'utilisateur, pour définir la marque par le biais de la première liste déroulante. Nous aurons besoin du générateur d'expression pour créer cette liaison dynamique. Nous proposons de débuter par la plus simple des deux, celle des marques.

• Enregistrer (CTRL + S) le formulaire sous le nom Parc et le fermer,
• Cliquer sur l'onglet Créer en haut de la fenêtre Access pour activer son ruban,
• Dans la section Requêtes, cliquer sur le bouton Création de requête,
• Fermer la boîte de dialogue qui suit sans ajouter aucune table,
• Puis, tout à fait à gauche dans le ruban Créer, cliquer sur la flèche du bouton Affichage,
• Dans la liste, choisir Mode SQL,

En effet, comme la clause DISTINCT est nécessaire, il est plus confortable de créer intégralement la requête en mode SQL, dont la syntaxe est très simple.

• Dans la feuille de code au centre, saisir la syntaxe SQL suivante :

SELECT DISTINCT Marque From Parc;

Très simplement, nous sélectionnons chaque marque de façon distincte à partir de la table Parc.

• Cliquer sur le bouton Exécuter, à gauche dans le ruban Créer pour visualiser les résultats,

Chaque marque est effectivement extraite sans doublons. Cette liste doit servir de contenu à la liste déroulante que nous souhaitons ajouter sur le formulaire.

• Enregistrer cette requête (CTRL + S) sous le nom R_Marques puis la fermer,
• Dans le volet des objets Access sur la gauche, cliquer avec le bouton droit de la souris sur le formulaire Parc,
• Dans le menu contextuel, choisir Mode création,
• Cliquer sur la zone de texte Marque pour la sélectionner sur le formulaire,
• Cliquer avec le bouton droit de la souris sur cette dernière,
• Dans le menu contextuel, pointer sur la rubrique Remplacer par, puis choisir Zone de liste déroulante dans le sous menu qui apparaît,

Le contrôle Zone de texte est instantanément remplacé par un contrôle de liste déroulante. De fait, il est désormais possible de définir son contenu en le reliant à la requête que nous venons de construire.

• Dans le ruban Création, cliquer sur le bouton Feuille de propriétés,

Le volet Feuille de propriétés apparaît sur la droite du formulaire. C'est lui qui permet de personnaliser le contrôle sélectionné.

• En haut du volet Feuille de propriétés, cliquer sur l'onglet Données,
• Dans sa propriété Contenu, dérouler la liste déroulante et choisir la requête R_Marques,

A ce stade, le lien est fait. Grâce à sa propriété contenu, la liste déroulante des marques est censée proposer le résultat de l'extraction de la requête ainsi liée. Quelques lignes en dessous, vous notez la présence d'une autre propriété fort précieuse, Limiter à liste. Cette dernière est réglée sur Non. Cela signifie que nous pouvons choisir dans la liste mais aussi créer une nouvelle entrée qui n'y figure pas, en la saisissant.

• Enregistrer les modifications (CTRL + S) et exécuter le formulaire (Touche F5),
• Cliquer sur la flèche de la liste déroulante des marques,

Comme vous le constatez, c'est bien le contenu de la requête qui s'affiche. Le lien entre les deux éléments a donc été parfaitement établi.

Listes déroulantes en cascade sur un formulaire
Comme nous le disions précédemment, nous devons proposer les modèles dépendant du choix réalisé pour la marque. Il s'agit de bâtir une nouvelle requête dont le critère est la valeur dynamique à l'instant t, inscrite dans la liste déroulante des marques depuis le formulaire. Ainsi, à chaque appel, la requête regénère son contenu. Ensuite, il s'agit de remplacer le contrôle des modèles par une zone de liste déroulante, puis de relier les deux grâce à sa propriété Contenu.

• Dans le ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans la liste, choisir Mode création,
• En haut de la fenêtre Access, cliquer sur l'onglet Créer pour activer son ruban,
• Dans la section Requêtes, cliquer sur le bouton Création de requête,
• Avec la boîte de dialogue qui suit, ajouter la table Parc et cliquer sur le bouton Fermer,
• Glisser les champs Marque et Modèle sur la grille de requête,
• Dans la zone Afficher, décocher le champ Marque,
• Puis, cliquer avec le bouton droit de la souris dans la zone de critère du champ Marque,
• Dans le menu contextuel, choisir Créer pour afficher le générateur d'expression,
• Dans la liste de gauche des éléments d'expression, déployer complètement l'affichage, jusqu'à visualiser le formulaire Parc,
• Sélectionner ce dernier et double cliquer sur le contrôle Marque, dans la liste du centre des catégories d'expression,

L'expression générée apparaît dans la partie supérieure de la boîte de dialogue :

Formulaires![Parc]![Marque]



Puisqu'elle est écrite dans la zone de critère du champ Marque, elle indique que la sélection de la requête doit se faire en fonction de la marque définie depuis le contrôle du même nom, sur le formulaire.

• Valider l'expression en cliquant sur le bouton Ok,

Bien que le lien soit effectué, rien n'empêche cette requête, lorsqu'elle recevra le critère sur la marque, d'énumérer tous les modèles, donc avec les doublons. Comme toujours, nous allons pallier le problème grâce à la syntaxe SQL.

• Grâce au bouton Affichage, basculer dans le mode SQL,
• Tout en allégeant sa syntaxe, adapter son écriture comme suit :

SELECT DISTINCT Modèle FROM Parc WHERE Marque = [Formulaires]![Parc]![Marque];

• Revenir en mode Création, grâce au bouton Affichage,
• De retour sur l'éditeur de requête, réaliser le raccourci CTRL + S pour l'enregistrer,
• La nommer R_Modeles et la fermer,

La requête R_Modeles ne peut pas être exécutée indépendamment du formulaire. Son critère est dynamique. Pour extraire des résultats, elle a besoin de connaître la valeur choisie pour la marque. Sa fonction est de remplir la seconde liste déroulante en fonction du choix réalisé dans la première.

• Revenir sur le formulaire en mode création,
• Cliquer avec le bouton droit de la souris sur le contrôle Modèle,
• Dans le menu contextuel, pointer sur Remplacer par et choisir Zone de liste déroulante,
• Dans sa feuille de propriétés, activer l'onglet Données,
• Dans sa propriété Contenu, choisir la requête R_Modeles avec la liste déroulante,
• Enregistrer les modifications (CTRL + S) et Exécuter le formulaire (F5),
• A l'aide de la barre de navigation en bas du formulaire, cliquer sur le bouton Nouvel Enregistrement,
• Taper une immatriculation, comme : Ht878FG par exemple,
• Choisir la marque Peugeot avec la première liste déroulante,
• Puis dérouler la seconde liste déroulante,

Tous les modèles de la marque sont a priori proposés. Mais cette liaison n'est pas encore parfaitement dynamique, et nous allons le constater.

• A l'aide de la première liste déroulante, choisir la marque Renault,
• Puis, dérouler la seconde liste déroulante,

Malheureusement et comme vous le constatez, ce sont toujours les modèles de la marque Peugeot qui sont proposés. Le lien existe bien mais il n'a pas été réactualisé pour générer le critère dynamique. L'astuce consiste à déclencher une macro Access, pour forcer l'actualisation des données au changement de marque. C'est le même principe que le recalcul Excel à validation d'une donnée ou d'une formule. Cette macro doit être déclenchée sur un événement précis, lorsque la valeur est modifiée dans la première liste déroulante.

• Dans le ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans la liste, choisir Mode création,
• Sur le formulaire, sélectionner la première liste déroulante, celle des marques,
• Activer l'onglet Evénement de sa feuille de propriétés,
• Pour son événement Sur changement, cliquer sur le petit bouton à l'extrémité droite,
• Dans la boîte de dialogue qui suit, choisir Générateur de Macro et valider par Ok,

Nous basculons ainsi dans l'éditeur de macro. Il s'agit ici de définir l'action qui s'enclenchera sur l'événement détecté, du changement de valeur dans la liste déroulante des marques.

• Avec la liste déroulante, sélectionner l'action ActualiserEnregistrement,
• Cliquer sur le bouton Enregistrer puis sur le bouton Fermer,

Nous sommes de retour sur le formulaire en mode création. L'action que nous venons de définir, attachée à l'événement de la liste déroulante, doit permettre de réactualiser le critère à passer dynamiquement à la seconde liste déroulante.

• Enregistrer les modifications et exécuter le formulaire,
• Cliquer sur le bouton Nouvel Enregistrement,
• Saisir l'immatriculation suivante par exemple : HG345FV,
• A l'aide de la première liste déroulante, choisir la marque Renault,
• Puis, dérouler la seconde liste déroulante,

Comme vous le constatez, les deux listes sont parfaitement reliées entre elles. La seconde ne propose que les modèles uniques dépendant de la marque sélectionnée dans la première liste. Si vous changez la marque dans la première liste déroulante, le contenu de la seconde liste s'adapte instantanément, grâce à l'action d'actualisation de la macro. Son critère a changé donc son contenu s'ajuste.

• Pour la marque Renault, sélectionner le modèle Koleos,
• Taper 130 dans la zone de saisie des chevaux,
• Puis, fermer le formulaire,

Les données d'une base de données sont inscrites en temps réel sur le disque dur, si bien qu'il n'est pas nécessaire d'enregistrer ces modifications. Si vous souhaitez en avoir le coeur net, il vous suffit d'ouvrir la table Parc pour constater la présence du nouveau véhicule saisi.

Nous arrivons à la fin de cette première étape. Nous avons construit le formulaire d'ajout de données et construit des fonctionnalités pour simplifier la saisie, grâce à ces listes déroulantes en cascade. Nous avons synthétisé des listes de valeurs uniques par marque, afin de les réceptionner dans Excel par connexion dynamique. Il s'agira d'y exploiter l'actualisation dynamique des données pour construire sans calcul, des listes reliées entre elles et fournir des extractions dynamiques selon critères en temps réel.