Extraire et filtrer les données en VBA Access

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


Sujets que vous pourriez aussi aimer :


Extraire et filtrer de l'information de base de données Access

Dans cette formation, nous souhaitons bâtir un formulaire à partir duquel l'utilisateur pourra, à l'aide de listes déroulantes, filtrer instantanément les données d'une table volumineuse, afin d'en extraire l'information. Nous allons préfabriquer les requêtes Access qui permettent de remplir les listes déroulantes et de recouper les critères. Elles seront conçues sur des critères dynamiques en fonction des choix utilisateurs spécifiés depuis le formulaire. Et c'est un code VBA qui permettra de réaliser le lien entre tous ces objets afin de rendre l'application parfaitement dynamique et fonctionnelle.

Présentation des données à filtrer
Pour réaliser cette application, nous partons d'une base de données existante pour laquelle, un formulaire a été préconçu.

• Télécharger la base de données sorties.accdb en cliquant sur son lien,
• Puis, l'ouvrir dans Access,
• Si nécessaire, cliquer sur le bouton Activer le contenu du bandeau jaune de sécurité,
• Double cliquer sur la table societes depuis le volet de gauche des objets Access,

Nous ouvrons ainsi cette table en mode feuille de données. Cette table comporte un nombre important de champs sur 1536 enregistrements. Elle liste des idées de sorties regroupées par activités dans des départements de la France. A la lecture de cette table, il est donc difficile de réunir rapidement les idées de sorties qui correspondent à l'envie du moment, dans un département donné. C'est pourquoi nous proposons de finaliser un formulaire qui permettra de filtrer et extraire les idées de sorties, correspondant à la demande, grâce à des choix dans des listes déroulantes.

• Fermer la table en cliquant sur la croix de son onglet,
• Double cliquer sur le formulaire Formulaire_extraction depuis le volet des objets Access,

Le formulaire est ainsi ouvert en exécution. Comme le propose la capture ci-dessous, deux listes déroulantes permettent à l'utilisateur de formuler des critères de tris à recouper. Pour l'instant, ces listes déroulantes sont vides. Le formulaire ne propose aucun lien avec des objets Access.

A l'issue, le sous formulaire de la partie inférieure, devra afficher en temps réel, les résultats de l'extraction commandée par les requêtes effectuées par l'utilisateur, par le biais des listes déroulantes. Il va s'agir dans un premier temps de concevoir des requêtes dynamiques à relier aux différents contrôles du formulaire.


Requêtes dynamiques et bases SQL
Avant de plonger dans le code VBA Access, nous allons réaliser tous les filtres et extractions qu'il est possible de faire avec les objets que propose Access par défaut. La première liste déroulante doit proposer de choisir une activité de sortie recensée dans la base de données. La seconde doit permettre de restreindre le filtre sur le département. Ces données sont répétées de nombreuses fois dans la table societes puisque beaucoup d'idées de sorties sont situées dans le même département pour la même activité. Par exemple, il n'y a pas qu'un seul restaurant dans la Drôme. Il s'agit donc de construire deux requêtes capables d'extraire sans doublons, d'une part les activités et d'autre part, les départements. Une fois conçues, elles serviront de sources à chacune des listes déroulantes du formulaire. C'est le langage SQL avec une syntaxe très simple qui va réaliser cette extraction sans doublons.

• Fermer le formulaire 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, cliquer sur le bouton Création de requête,
• Dans la boîte de dialogue qui suit, sélectionner la table societes,
• Cliquer sur le bouton Ajouter puis sur le bouton Fermer,

Nous n'allons pas utiliser la grille de requête pour réaliser la sélection distincte des informations que nous souhaitons extraire, afin de nourrir la liste déroulante. L'objectif est de pouvoir, dans un premier temps, réaliser l'extraction de toutes les activités recensées dans la table, sans doublons. Le critère SQL doit donc être réalisé sur le champ societes_activite de la table.

• Cliquer sur la flèche du bouton Affichage, en haut à gauche du ruban Créer, dédié aux requêtes,
• Dans la liste, choisir Mode SQL,

Vous basculez sur l'affichage d'une feuille blanche, semblable à une feuille de code VBA. Il s'agit ici de bâtir la requête SQL qui réalisera cette extraction SQL. Le SQL est un langage universel qui permet d'attaquer les bases de données, notamment pour extraire de l'information.

Vous remarquez qu'une requête est déjà présente avec la syntaxe suivante :

SELECT FROM societes;

Il s'agit de la requête SQL la plus basique et la plus conventionnelle. Elle permet d'extraire toutes les informations de la base de données puisqu'aucun critère n'est encore posé. Le mot clé SELECT indique qu'il s'agit d'une requête sélection dont le but est de réaliser un filtre sur la table désignée après le mot clé FROM. Comme aucun champ n'est stipulé entre le SELECT et le FROM, cette requête doit extraire tous les champs de la table. Toutes les requêtes que vous construisez en mode création avec Access sont traduites en langage SQL.

• Modifier la requête comme suit :

SELECT DISTINCT societes_activite FROM societes;

Le mot clé DISTINCT suivi du nom du champ sur lequel il agit, indique que la requête doit extraire les informations de ce champ sans doublons. Et seul ce champ sera affiché dans la sélection puisqu'aucun autre n'est stipulé.

• Enregistrer cette requête (CTRL + S) sous le nom activites_distinctes,
• Fermer cette requête en cliquant sur la croix de son onglet,
• Puis, double cliquer sur cette requête depuis le volet de gauche pour l'exécuter,

La requête affiche une sélection de la table source, en mode feuille de données. Et, comme vous le constatez, seul figure le champ societes_activite. Et pour ce champ, les activités sont en effet filtrées sans doublons. Il ne restera plus qu'à relier la liste déroulante du formulaire à cette requête SQL afin qu'elle propose ce contenu extrait. Mais avant cela, selon le même mode opératoire, nous allons bâtir la requête qui permet d'extraire tous les départements sans doublons, pour la seconde liste déroulante.

• Fermer la requête en cliquant sur la croix de son onglet,
• Activer le ruban Créer en cliquant sur son onglet en haut de la fenêtre Access,
• Dans la section Requêtes, cliquer sur le bouton Création de requête,
• Dans la fenêtre qui suit, sélectionner la table societes,
• Cliquer sur le bouton Ajouter puis sur le bouton Fermer,
• Cliquer sur la flèche du bouton Affichage en haut à gauche du ruban Créer,
• Dans la liste, choisir Mode SQL comme précédemment,
• Modifier la requête SQL comme suit :

SELECT DISTINCT societes_departement FROM societes;

• Enregistrer la requête (CTRL + S) sous le nom : departements_distincts,
• Puis la fermer en cliquant sur la croix de son onglet,
• Enfin, double cliquer sur cette requête depuis le volet des objets Access pour l'exécuter,

Chose promise, la requête extrait bien les départements issus de la table, sans doublons. Avant de connecter les listes déroulantes à ces requêtes SQL, nous avons besoin d'une requête sélection classique, pour alimenter le sous formulaire par défaut, à l'ouverture du formulaire. Mais nous ne souhaitons pas le détail de tous les champs. Donc nous allons la concevoir à l'aide de la grille de requête que propose Access.

• Fermer la requête en cours en cliquant sur la croix de son onglet,
• Dans le ruban Créer, cliquer sur le bouton Assistant Requête,
• Dans la boîte de dialogue qui suit, choisir Requête simple et cliquer sur Suivant,
• Dans l'étape qui suit, ajouter les champs societes_nom, societes_activite, societes_departement, societes_ville, societes_contact, societes_tel, societes_mail et societes_web,

Pour ce faire, il suffit de sélectionner un champ depuis la liste de gauche et de cliquer sur la flèche dirigée vers la droite, pour le basculer dans les champs à intégrer, dans la liste de droite.

• Cliquer sur le bouton Suivant,
• Dans la dernière étape, nommer la requête : societes_sans_critere,
• Puis cliquer sur Terminer,

Les 1536 enregistrements s'affichent en mode feuille de données, mais seulement pour les champs sélectionnés. Cette requête, bien que n'excluant aucun enregistrement, offre une vue simplifiée de la table d'origine.

• Fermer la requête en cliquant sur la croix de son onglet.

Relier les contrôles de formulaire aux requêtes - Objets sources
Les contrôles de formulaire sont semblables à des objets de programmation. Ils disposent de propriétés, accessibles depuis leur feuille de propriétés qui permettent de les personnaliser, ou encore de les lier à des sources de données. Ils proposent de même un gestionnaire d'événements qui permet de déclencher des actions, gérées par des macros ou du code VBA, sur ordre précis. C'est ce que nous avions appris au travers de la formation pour débuter la programmation en Visual Basic pour Access. Et c'est ce dont nous allons nous servir ici.

• Cliquer avec le bouton droit de la souris sur l'objet Formulaire_extraction depuis le volet gauche des objets Access,
• Dans le menu contextuel, choisir Mode création,
• Sélectionner la première liste déroulante pour filtrer par activités,
• Dans le ruban Création, cliquer sur le bouton Feuille de propriétés si cette dernière n'est pas visible,
• Dans la feuille de propriétés sur la droite, activer l'onglet Données,
• Cliquer sur la flèche de la propriété Contenu,
• Dans la liste, choisir la requête activites_distinctes,
• Sélectionner la seconde liste déroulante du formulaire,
• Cliquer sur la flèche de sa propriété Contenu depuis sa feuille de propriétés,
• Dans la liste, choisir la requête departements_distincts,

Désormais, les deux listes déroulantes doivent se remplir du contenu extrait par les deux requêtes SQL. Mais avant de le vérifier en exécutant le formulaire, nous devons lier le sous formulaire à la requête sélection.

• Sélectionner le rectangle sous le titre Retour de l'extraction,
• Cliquer sur la flèche de sa propriété Objet source dans l'onglet Données de sa feuille de propriétés,
• Dans la liste, choisir l'objet Requête.societes_sans_critere,
• Enregistrer les modifications (CTRL + S) puis exécuter le formulaire (F5),

Comme vous le constatez, le sous formulaire offre la vue des 1536 enregistrements de la table source, uniquement pour les champs définis par la requête Sélection. Les listes déroulantes proposent respectivement les activités et départements sans doublons. A ce stade en revanche, aucune interaction n'existe entre les contrôles. Le fait de choisir une activité et/ou un département par le biais des listes, ne filtre pas les données du sous formulaire selon ces critères dynamiques. C'est l'objectif que nous devons atteindre désormais.

Extraire l'information selon action utilisateur depuis le formulaire
Nous devons construire des requêtes dynamiques qui extraient les résultats, selon les choix opérés par l'utilisateur au travers des listes déroulantes. Alors nous devrons être en mesure de modifier la source de contrôle du sous formulaire, en temps réel, afin qu'il affiche les résultats extraits, correspondant aux filtres occasionnés par les listes déroulantes. Nous devons prévoir trois requêtes aux critères dynamiques. La première doit correspondre au critère de l'activité sélectionnée seule. La seconde doit correspondre au critère dynamique du département sélectionné seul. La dernière doit permettre de recouper les deux critères, si l'utilisateur a fait des choix dans les deux listes déroulantes. Comme ces requêtes dynamiques doivent extraire l'information sur les mêmes champs que la requête sélection, nous allons partir de cette dernière et la personnaliser.

• Fermer le formulaire en cliquant sur la croix de son onglet,
• Sélectionner la requête societes_sans_critere depuis le volet de gauche,
• Réaliser le raccourci CTRL + C pour la copier et CTRL + V pour la dupliquer,
• La renommer critere_activite dans la boîte de dialogue qui suit et valider,
• Cliquer avec le bouton droit sur cette requête critere_activite depuis le volet de gauche,
• Dans le menu contextuel, choisir Mode Création,

La requête s'affiche en mode conception. Tous les champs qui avaient été désignés grâce à l'assistant sont déjà placés dans la grille de requête. Il s'agit maintenant de définir un critère dynamique sur le champ societes_activite. Ce critère doit correspondre au choix fait par l'utilisateur, dans la liste déroulante des activités, depuis le formulaire. Nous avions appris à créer ce type de critère dynamique dans la formation Access pour relier un formulaire et une requête.

• Cliquer avec le bouton droit de la souris dans la zone Critères du champ societes_activite,
• Dans le menu contextuel, choisir Créer,
• Dans le générateur d'expressions, déployer l'affichage des formulaires depuis le liste de gauche,
• Sélectionner alors le formulaire Formulaire_extraction,
• Dans la partie centrale, double cliquer sur le contrôle liste_activites,

L'expression de correspondance s'affiche dans la zone d'expression en haut de la boîte de dialogue.

[Formulaires]![Formulaire_extraction]![liste_activites]

Liste_activites désigne l'objet liste déroulante des activités du formulaire. Comme ce critère est posé sur le champ societes_activite, la requête doit filtrer les enregistrements correspondant à l'activité choisie depuis le formulaire, par le biais de cette liste déroulante.

• Valider cette expression en cliquant sur le bouton Ok,
• Enregistrer les modifications et fermer la requête,

Bien sûr, cette requête ne peut pas être exploitée indépendamment du formulaire puisque son critère dépend de la valeur choisie, à l'instant t par l'utilisateur, par le biais de la liste déroulante. Ainsi, si vous double cliquez dessus depuis le volet des objets Access, une boîte de dialogue vous demandera de préciser le critère sur le champ societes_activite.

Nous devons maintenant concevoir la requête qui permettra d'extraire les informations de la table, en fonction du département choisi par l'utilisateur, par le biais de la seconde liste déroulante. La technique est exactement la même.

• Dupliquer de nouveau la requête societes_sans_critere comme précédemment,
• Nommer la copie : critere_departement et l'ouvrir en mode création,
• Générer l'expression : Formulaires![Formulaire_extraction]![liste_departement], dans la zone de critères du champ societes_departement, en suivant la même procédure que précédemment,
• Enregistrer les modifications et fermer la requête,

Elle apparaît, tout comme la précédente, dans le volet des objets Access, précisément dans la section des requêtes. Il reste à créer une dernière requête dynamique, celle qui permet d'extraire les informations selon les critères recoupés sur l'activité et le département. C'est une sorte d'union des deux précédentes requêtes.

• Dupliquer cette fois la requête critere_departement,
• La renommer : critere_departement_activite, puis l'ouvrir en mode Création,

Le critère sur le champ societes_departement est ainsi déjà créé. Il s'agit de répliquer celui de l'autre requête sur le champ societes_activite. Lorsque des critères sont ainsi énoncés sur plusieurs champs, mais sur la même ligne de la zone de critères, ils agissent comme un ET. Cela signifie qu'ils doivent être vérifiés ensemble, l'un et l'autre, donc recoupés.

• Dans la zone de critères du champ societes_activite, générer l'expression :

[Formulaires]![Formulaire_extraction]![liste_activites]

• Enregistrer les modifications et fermer la requête.

Changer dynamiquement la source de contrôle d'un sous formulaire
L'objectif désormais est de pouvoir charger le sous formulaire des informations extraites par les requêtes, en fonction des choix réalisés par l'utilisateur, au travers des listes déroulantes. Or la source de contrôle du sous formulaire est définie une fois pour toutes, grâce à la feuille de propriétés. C'est là que VBA Access intervient. Visual Basic va permettre de modifier la source du contrôle, selon l'événement généré par l'utilisateur. La formation VBA Access pour créer des interactions entre les objets de base de données, nous avait appris à manipuler ce gestionnaire d'événements. Les événements en question doivent être interceptés lorsque l'utilisateur fait un choix dans une liste déroulante.

• Cliquer avec le bouton droit de la souris sur l'objet Formulaire_extraction depuis le volet des objets Access,
• Dans le menu contextuel, choisir Mode création,
• Sélectionner la première liste déroulante, celle des activités,
• Activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le petit bouton associé à l'événement Sur changement,
• Dans la boîte de dialogue qui suit, sélectionner Générateur de code et valider,

Nous basculons ainsi dans l'éditeur de code Visual Basic entre les bornes de la procédure événementielle : Private Sub liste_activites_Change(). liste_activites est le nom de l'objet liste déroulante. Change est l'événement associé à cet objet. Lorsqu'il est intercepté parce que l'utilisateur change la valeur choisie dans la liste déroulante, le code saisi entre les bornes de cette procédure se déclenche.

Il s'agit ici de modifier la source du sous formulaire, selon le cas. Si les deux listes déroulantes contiennent des valeurs, alors la requête aux critères recoupés (Activités + Départements : critere_departement_activite) doit servir à charger le sous formulaire. Sinon, seule la requête critere_activite doit servir à charger le sous formulaire. Pour vérifier ce critère nous devons exploiter l'instruction de branchement If que nous avions apprise à programmer dans la formation VBA Excel pour gérer les critères. En d'autres termes, nous devons traduire ceci : Si la valeur de la liste déroulante des départements n'est pas définie (IsNull), alors le sous formulaire doit être bâti sur la requête des activités. Sinon, il doit être bâti sur la requête recoupée des activités et départements. Grâce aux macros converties de la formation sur les interactions des objets Access en VBA, nous avions appris que nous pouvions désigner un contrôle de formulaire par son nom, grâce à l'objet Forms pour formulaires, en désignant et le nom du formulaire, et le nom du contrôle liste déroulante, comme suit :

Forms![ Formulaire_extraction]![liste_departements]

• Selon ce principe, saisir le code suivant entre les bornes de la procédure :

If( IsNull( Forms![Formulaire_extraction]![liste_departement] )) Then
zone_extraction.SourceObject = 'Query.critere_activite'
Else
zone_extraction.SourceObject = 'Query.critere_departement_activite'
End If

zone_extraction.Requery

La fonction VBA Access IsNull permet de savoir si le contrôle passé en paramètre (liste_departement) est défini. Si aucune valeur n'a encore été choisie, elle renvoie True, signifiant qu'aucun critère n'a encore été spécifié par le biais de la liste déroulante des départements. Dans ce cas, nous lions le sous formulaire à la source de données générée par la requête critere_activite. Cette dernière génère en effet une extraction de la base de données, selon l'activité choisie par l'utilisateur par le biais de la première liste déroulante. C'est la propriété SourceObject, de l'objet sous formulaire désigné par son nom (zone_extraction), qui permet de modifier la source de données. Notez que dans l'affectation, le nom de la requête source doit être préfixée du mot clé Query (Query.critere_activite), désignant une requête. En effet, rien n'empêche un formulaire de porter le même nom par exemple. Pour éviter tout ambiguïté, il faut donc spécifier la nature de l'objet source.

Dans le cas contraire (Else), la requête aux deux critères recoupés sert de source de données (zone_extraction.SourceObject = 'Query.critere_departement_activite'). Enfin, une fois que le test est terminé (Enf If), nous forçons la mise à jour du sous formulaire grâce à sa méthode Requery (zone_extraction.Requery). Nous mettons ainsi à jour la liaison et donc, la source de données.

• Enregistrer les modifications (CTRL + S),
• Basculer sur le formulaire grâce au raccourci clavier ALT + F11 par exemple,
• Exécuter ce dernier en enfonçant la touche F5 du clavier,
• Sélectionner une activité de sortie à l'aide de la première liste déroulante,

Vous remarquez que le contenu du sous formulaire s'adapte instantanément, grâce au code VBA Access, selon le critère dynamique généré par la requête, qui a récupéré l'information choisie par l'utilisateur dans la liste.

En revanche, si vous choisissez un élément dans la liste déroulante des départements, rien ne se produit. En effet, pour l'instant, nous n'avons développé aucun code associé à l'événement sur changement, pour cette liste.

Par contre, une fois le département désigné, si vous changez de nouveau d'activité, le sous formulaire se charge des critères recoupés. C'est la branche Else du If qui est déclenchée pour charger la requête critere_departement_activite, comme source de données du sous formulaire.

C'est d'ailleurs ce qu'illustre la capture ci-dessous. Nous avons choisi de n'afficher que les restaurants pour le département de la Drôme.


Nous devons maintenant développer le code associé au changement de la seconde liste déroulante. Le principe est le même. Si le choix des activités n'est pas défini, la source du sous formulaire doit être la requête sur les départements. Sinon, il doit s'agir, comme précédemment, de la requête recoupée sur les deux critères.

• Afficher de nouveau le formulaire en mode création,
• Sélectionner la deuxième liste déroulante (liste_departement),
• Cliquer sur le bouton de son événement Sur changement depuis sa feuille de propriétés,
• Choisir Générateur de code et valider,

Nous basculons de nouveau dans l'éditeur de code VBA, entre les bornes de la procédure événementielle : liste_departement_Change. Le code développé entre ces bornes se déclenchera donc lorsqu'un changement de valeur sera détecté dans la liste déroulante nommée : liste_departement.

• Saisir le code suivant :

If( IsNull( Forms![Formulaire_extraction]![liste_activites] )) Then
zone_extraction.SourceObject = 'Query.critere_departement'
Else
zone_extraction.SourceObject = 'Query.critere_departement_activite'
End If

zone_extraction.Requery

On change le nom de l'objet sur lequel on fait le test (liste_activites) ainsi que le nom de la requête à charger (critere_departement), si le critère est vérifié. Littéralement, au changement de valeur dans la liste des départements, si aucune valeur n'est encore définie pour les activités, on charge la requête du critère du département. Sinon on charge la requête qui recoupe les deux critères. A l'issue, comme précédemment, on réactualise le lien entre le sous formulaire et la requête grâce à la méthode Requery.

• Enregistrer les modifications et basculer sur le formulaire,
• L'exécuter et choisir une activité puis un département,

Cette fois, les données du sous formulaire se mettent à jour instantanément, au changement de valeur dans les deux listes déroulantes. Il aurait pu être intéressant d'ajouter une zone de saisie pour définir un critère sur le nom de la société. Mais avec une requête, nous aurions défini un critère LIKE général sur le mot clé tapé. Nous aurions obtenu des résultats assez grossiers. Pour des résultats plus fins et plus proches, nous aurons besoin cette fois d'un code VBA Access plus subtil. C'est ce que nous verrons dans une prochaine formation.