Extractions externes sur des choix recoupés 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 à partir d'une autre BDD

Avec le développement précédent, nous avons appris à articuler trois listes déroulantes entre elles sur un formulaire Access et à partir d'informations émanant d'une base de données externe. Dans cette suite logique, sur ces choix recoupés par l'utilisateur, nous souhaitons produire l'extraction de toutes les données concordantes, au centre du formulaire.



C'est ce que démontre le résultat illustré par la capture. Lorsque l'utilisateur choisit un département avec la première liste déroulante, seules les activités de sorties de ce département sont restituées dans le sous-formulaire du dessous. Dans le même temps, la deuxième liste déroulante se remplit des activités disponibles dans ce département. Lorsqu'il choisit l'une d'entre elles, seules les idées pour cette activité dans ce département sont importées dans le sous-formulaire. Toujours dans le même temps, la troisième liste déroulante se nourrit des villes disponibles pour cette activité dans ce département. Lorsque l'utilisateur choisit l'une d'entre elles, il obtient l'extraction chirurgicale des idées de sorties dans cette ville, pour cette activité et ce département.

Bases de données Access à télécharger
Pour poursuivre les travaux, nous avons tout d'abord besoin de les récupérer là où nous les avions laissés.

• Télécharger le fichier compressé extraire-depuis-autre-bdd.rar en cliquant sur ce lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous pouvez le voir, la décompression livre deux fichiers nommés respectivement extraire-depuis-autre-bdd.accdb et id2sorties.accdb. Le premier est la base de données devant produire les extractions. Le second représente la source de données externe.

• Double cliquer sur le fichier id2sorties.accdb pour l'ouvrir dans Access,
• Dans le volet de navigation sur la gauche, double cliquer sur la table societes,

Ainsi, nous affichons son contenu en mode feuille de données. Elle archive des activités de sorties sur plus de mille enregistrements. Les champs que nous devrons questionner se nomment respectivement societes_nom, societes_activite, societes_departement et societes_ville.

• Fermer cette base de données en cliquant sur la croix de sa fenêtre,
• Double cliquer désormais sur le fichier extraire-depuis-autre-bdd pour l'ouvrir dans Access,
• Cliquer ensuite sur le bouton Activer le contenu du bandeau de sécurité,
• Puis, depuis le volet de navigation, double cliquer sur le formulaire f_recup pour l'exécuter,

Un traitement externe s'opère déjà au chargement de ce formulaire.

• Déployer la liste déroulante de gauche,

Comme vous le constatez, ce sont tous les départements uniques issus de la base de données externe qui se dévoilent. En effet, la base active n'héberge aucune information.

• Cliquer sur l'un d'entre eux puis déployer la deuxième liste déroulante,

Cette fois, ce sont toutes les activités disponibles pour le département choisi, qui se proposent.

• Cliquer sur l'une de ces activités et déployer la troisième liste déroulante,

Ce sont toutes les villes offrant cette activité dans ce département, qui se dressent cette fois. Sur la base de ces données externes et par le jeu de requêtes SQL dirigées en VBA Access, ces trois listes déroulantes sont donc reliées entre elles. Mais comme vous avez pu le voir lors de ces choix successifs, aucune extraction n'est encore produite dans le sous formulaire existant.

Découvrir le code VBA existant
Avant de débuter, nous proposons de reprendre contact avec le code VBA réalisant ces articulations.

• A gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans les propositions, choisir le mode création pour afficher le formulaire en conception,
• Puis, réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Access,

Vous constatez la présence de plusieurs procédures événementielles. Celle qui nous intéresse particulièrement et que nous allons enrichir, se nommecharger_liste. Elle est appelée au chargement du formulaire (Form_Load), au changement de département (dep_Change) et au changement d'activité avec la deuxième liste déroulante (activites_Change). A chaque occasion ce sont des paramètres adaptés qui lui sont transmis.

C'est en fonction de la liste déroulante qui lui est passée en paramètre que cette procédure adapte la requête SQL à exécuter pour remplir cette liste en conséquence :

...
If controle.Name = "dep" Then
requete = "SELECT DISTINCT societes_departement FROM societes"
ElseIf controle.Name = "activites" Then
requete = "SELECT DISTINCT societes_activite FROM societes WHERE societes_departement='" & dep.Value & "'"
Else
requete = "SELECT DISTINCT societes_ville FROM societes WHERE societes_departement='" & dep.Value & "' And societes_activite ='" & activites.Value & "'"
End If
...

Nous le verrons, nous transformerons cette instruction conditionnelle à trois branches en une instruction conditionnelle à quatre branches pour prendre en compte le changement de ville avec la dernière liste déroulante, ce qui n'est pas le cas à ce stade.

Puis, cette procédure exécute cette requête pour parcourir tous les enregistrements filtrés et charger les données dans la liste déroulante concernée :

...
Set enr = autreBdd.CurrentDb.OpenRecordset(requete)
enr.MoveFirst
Do
controle.AddItem enr.Fields(champ).Value
enr.MoveNext
Loop While Not enr.EOF
...

Les variables supplémentaires
Pour poursuivre ce développement, nous devons commencer par déclarer deux variables supplémentaires afin de stocker les nouvelles syntaxes SQL. La première doit sélectionner les enregistrements externes en fonction des choix émis par l'utilisateur avec les listes déroulantes. La seconde doit insérer tour à tour ces lignes dans la table locale t_sorties qui sert de source d'informations au sous-formulaire.

• Dans la procédure charger_liste, ajouter les déclarations de variables suivantes :

Sub charger_liste(controle As ComboBox, champ As String)
Dim autreBdd As Access.Application: Dim enr As Recordset
Dim chemin As String: Dim requete As String:Dim requete2 As String: Dim requeteAj As String
...

Nous déclarons donc deux variables supplémentaires (requete2 et requeteAj) comme des textes (As String), tout simplement pour mémoriser les syntaxes SQL des requêtes sélection et insertion.

Tous les enregistrements
Au chargement du formulaire, la première nouvelle requête doit restituer tous les enregistrements de la base de données externe, sans exception. Il s'agit donc d'une syntaxe triviale, sans clause Where. Cette construction doit intervenir dans la première branche de l'instruction conditionnelle.

• Dans la première branche de l'instruction conditionnelle, ajouter l'affectation suivante :

...
If controle.Name = "dep" Then
requete = "SELECT DISTINCT societes_departement FROM societes"
requete2 = "SELECT * FROM societes"
...

Nous sélectionnons donc toutes les données de tous les champs (*) sans exception.

Les enregistrements du département
Dans le cas où l'utilisateur choisit un département avec la première liste déroulante, nous devons restreindre les données extraites aux activités de sorties de ce département. Donc, une clause Where est à émettre dans la deuxième branche de l'instruction conditionnelle.

• Dans la deuxième branche de l'instruction conditionnelle, créer la requête SQL suivante :

...
ElseIf controle.Name = "activites" Then
requete = "SELECT DISTINCT societes_activite FROM societes WHERE societes_departement='"& dep.Value & "'"
requete2 = "SELECT * FROM societes WHERE societes_departement='" & dep.Value & "'"
...

Cette fois, nous émettons une restriction de sélection sur le département choisi, grâce à une clause Where (WHERE societes_departement='" & dep.Value & "'").

Les enregistrements du département et de l'activité
La troisième branche de l'instruction conditionnelle doit se transformer en avant dernière branche pour permettre l'insertion du cas de l'appel sur un changement de ville. Nous le verrons, nous utiliserons un leurre pour ce dernier cas puisqu'il sera uniquement question d'extraire et non plus de filtrer pour une énième liste déroulante. Nous devons commencer par ajuster le test.

• Adapter la troisième branche comme suit :

...
ElseIf controle.Name = "villes" Then
requete = "SELECT DISTINCT societes_ville FROM societes WHERE societes_departement='" & dep.Value & "' And societes_activite ='" & activites.Value & "'"
Else

End If
...

S'il s'agit de la liste déroulante pour les villes, nous récupérons toutes les données du champ correspondant et concordant avec la demande croisée sur le département et l'activité. Dans le même temps, nous en profitons pour prévoir une quatrième et dernière branche, celle qui consistera à construire la requête pour extraire les données correspondant aux trois critères recoupés par les trois listes déroulantes.

• Dans cette troisième branche, créer la requête SQL suivante :

...
ElseIf controle.Name = "villes" Then
requete = "SELECT DISTINCT societes_ville FROM societes WHERE societes_departement='" & dep.Value & "' And societes_activite ='" & activites.Value & "'"
requete2 = "SELECT * FROM societes WHERE societes_departement='" & dep.Value & "' And societes_activite ='" & activites.Value & "'"
Else
...

Grâce à cette double clause Where (And), nous ne conservons que les enregistrements répondant favorablement à la fois au département et à l'activité choisis par l'utilisateur.

Filtrer les données sur les trois choix recoupés
Nous l'avons dit, nous déclencherons l'exécution de la quatrième branche de l'instruction conditionnelle au changement de ville dans la dernière liste déroulante grâce à un leurre. Le filtre pour l'extraction doit tenir compte du choix du département, de l'activité dans ce département et de la ville pour cette activité dans le département. C'est donc une syntaxe SQL avec une triple clause Where que nous devons construire.

• Dans la quatrième branche de l'instruction conditionnelle, créer la requête suivante :

...
Else
requete2 = "SELECT * FROM societes WHERE societes_departement='" & dep.Value & "' And societes_activite ='" & activites.Value & "' And societes_ville ='" & villes.Value & "'"
End If
...

Purger l'ancienne extraction
Maintenant, avant d'exécuter la requête appropriée pour réaliser les importations dans la table t_sorties, nous devons commencer par la vider des précédentes potentielles extractions réalisées par le biais des choix utilisateur sur le formulaire. Rappelons-le, la table t_sorties est effectivement utilisée comme source de données du sous-formulaire. C'est d'ailleurs la raison pour laquelle ce dernier est encore vide à ce stade.

• Après l'instruction conditionnelle à quatre branches, exécuter la requête suivante :

...
CurrentDb.Execute "DELETE * FROM t_sorties"
...

Nous exploitons la méthode Execute de l'objet CurrentDb pour déclencher l'exécution d'une requête suppression (Delete), sur la base de données en cours. Comme aucune clause Where n'est émise, ce sont tous les enregistrements de la table qui sont ainsi supprimés pour réinitialiser cette dernière, avant les nouvelles insertions.

Contourner le remplissage des listes
Le code qui suit, notamment avec la boucle Do Loop, concerne le remplissage des listes déroulantes, avec la requête appropriée, selon le contrôle qui est passé en paramètre de la procédure. Mais dans le cas du changement d'une ville, toutes les listes étant déjà remplies et reliées (4ème branche), ce code ne doit pas intervenir. Nous choisissons donc de le gérer avec une nouvelle instruction conditionnelle.

• Encapsuler ce code VBA dans une instruction conditionnelle comme suit :

...
If champ <> "" Then
Set enr = autreBdd.CurrentDb.OpenRecordset(requete)
enr.MoveFirst
Do
controle.AddItem enr.Fields(champ).Value
enr.MoveNext
Loop While Not enr.EOF
End If
...

C'est ce paramètre vide ("") que nous passerons en second argument de la procédure, au changement de ville avec la dernière liste déroulante, pour ne pas déclencher ce fragment destiné à remplir les listes et à les relier en cascade.

Exécuter la requête insertion
Enfin, nous devons déclencher les requêtes sélection que nous avons construites dans les branches de l'instruction conditionnelle. Grâce à elles, nous allons isoler tous les enregistrements de la base externe correspondant exactement à la ville, à l'activité et au département. C'est ainsi et dans l'enchaînement que nous devons entreprendre de les parcourir tous, grâce à une nouvelle boucle Do Loop. A l'intérieur, c'est une requête ajout qui doit être exécutée, à chaque passage donc pour chaque enregistrement filtré, pour les ajouter tour à tour dans la table t_sorties.

• Après cette dernière instruction conditionnelle, ajouter le code VBA suivant :

...
End If

Set enr = autreBdd.CurrentDb.OpenRecordset(requete2)
enr.MoveFirst
Do
requeteAj = "INSERT INTO t_sorties (s_rs, s_dep, s_act, s_Ville) VALUES ('" & enr.Fields("societes_nom").Value & "','" & enr.Fields("societes_departement").Value & "','" & enr.Fields("societes_activite").Value & "','" & enr.Fields("societes_ville").Value & "')"
CurrentDb.Execute requeteAj
enr.MoveNext
Loop While Not enr.EOF

DoCmd.Requery

autreBdd.Quit
...

Nous appelons la méthode OpenRecordset sur la base de données externe (autreBdd) pour y exécuter la requête d'extraction. Nous plaçons le pointeur de lecture sur le premier enregistrement filtré grâce à la méthode MoveFirst de l'objet enr. Ensuite, nous engageons une boucle Do pour les parcourir tous jusqu'au dernier (Loop While Not enr.EOF). A chaque passage, dans les champs de la table locale (s_rs, s_dep, s_act, s_Ville), nous insérons (VALUES) les valeurs correspondantes de l'enregistrement externe filtré. Ensuite, nous n'oublions pas de déplacer le pointeur de lecture sur l'enregistrement suivant (MoveNext) pour les passer tous en revue au fur et à mesure. Après la boucle, la méthode Requery de l'objet VBA Access DoCmd permet d'actualiser les liaisons de sorte ici, à ce que le sous-formulaire affiche les nouvelles données extraites et insérées dans la table t_sorties, sa source de données.

Filtrer au changement de ville
Pour terminer, nous devons aussi appeler cette procédure au changement de ville avec la troisième liste déroulante.

• Dans les bornes de la procédure villes_Change, ajouter l'appel suivant :

Private Sub villes_Change()
charger_liste neutre, ""
End Sub

En premier paramètre, la procédure charger_liste attend un contrôle. Or toutes les listes déroulantes sont déjà chargées. Pour que cette procédure soit leurrée, il convient d'ajouter une zone de liste déroulante, par exemple dans l'entête du formulaire, de la nommer neutre et de régler sa propriété Visible à Non.



C'est grâce à ce subterfuge que la quatrième branche de la première instruction conditionnelle va être déclenchée pour générer la requête Sql à la triple clause Where considérant le département, l'activité et la ville à la fois. En second paramètre, en guise de nom de champ, nous lui passons une chaîne vide (""). C'est grâce à cette astuce que le remplissage des listes déroulantes va être ignoré dans ce quatrième cas (If champ <> "" Then).

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + Tab),
• Exécuter ce dernier, par exemple avec la touche F5 du clavier,
• Avec la première liste déroulante, choisir un département,

Comme vous le constatez, les propositions s'actualisent dans le sous-formulaire. Elles se restreignent au département demandé.

• Avec la deuxième liste déroulante, choisir une activité dans ce département,

Le formulaire filtre aussitôt les données sur cette activité dans ce département.

• Enfin avec la troisième liste déroulante, choisir une ville offrant cette activité,

L'extraction s'affine encore un peu plus pour ne garder que les idées dans cette ville, pour cette activité dans ce département.