Extraire selon une recherche verticale avec Access

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


Sujets que vous pourriez aussi aimer :


Correspondance par recherche verticale

Dans cette formation Access, nous proposons de finaliser les travaux initiés lors de la formation précédente. Nous avions réussi à construire les accords grammaticaux sur un formulaire Access grâce à une petite fonction VBA, exploitée dans un champ calculé d'une requête.

En fonction du nombre d'enfants pour chacun, il s'agit désormais de calculer les aides sociales. Pour cela, nous devons être en mesure d'extraire la donnée externe par recherche sur l'indication du nombre d'enfants. Il s'agit donc de répliquer le principe de fonctionnement d'une fonction Excel RechercheV. Les salariés sont en effet archivés dans une table. Fort logiquement, les aides recensées par tranches, sont archivées dans une autre. Seule une requête Access peut réaliser la correspondance dynamique entre les deux.



La capture ci-dessus illustre le formulaire de l'application Access finalisée. Les aides sociales sont extraites et calculées selon les informations du salarié en cours de consultation. Une pette liste déroulante permet de naviguer au travers des enregistrements, pour faciliter l'accès à l'information sur demande.

Source et présentation de la problématique
Pour la mise en oeuvre des solutions techniques, nous avons besoin de récupérer les travaux là où nous les avions laissés.

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

Dans le volet des objets Access sur la gauche de la fenêtre, vous notez la présence de différents éléments. L'objet Module1 accueille la fonction VBA accord que nous avons conçue pour reproduire les accords grammaticaux sur le formulaire, en fonction du nombres d'enfants. L'élément requete_sal est la requête intermédiaire bâtie sur la table salaries. Un champ calculé appelle la fonction VBA pour enrichir l'information avec les accords.

• Dans le volet des objets Access, double cliquer sur l'élément formulaire_sal pour exécuter son formulaire,

Il s'agit du formulaire que nous avions construit lors de la formation précédente, à partir de la requête requete_sal. Il permet de visualiser chaque salarié. L'accord grammatical sur le nombre d'enfants est restitué grâce au champ calculé. Une mise en forme conditionnelle déclenche une couleur d'alerte lorsque les familles sont nombreuses.

Sur la partie inférieure, nous devons rattacher les informations calculées sur les aides sociales attribuées à chacun. Sur la partie droite, nous devons construire la liste déroulante permettant de simplifier la navigation au travers des enregistrements.

• Sur la gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans la liste, choisir Mode création pour afficher la conception du formulaire,
• Dans le volet des objets Access, double cliquer sur l'élément tranches pour ouvrir sa table,

Il s'agit des aides sociales à allouer aux salariés en fonction du nombre d'enfants. L'aide par enfant est indiquée dans le champ Seuils en fonction d'une tranche à laquelle il faut appartenir. Par exemple, entre 3 et 5 enfants, l'aide consentie est de 75 Euros par enfant. C'est en raison de ce référencement par intervalles que les deux tables ne peuvent être reliées entre elles. Il n'y a aucune correspondance exacte.

• Fermer la table tranches en cliquant sur la croix de son onglet,

Pour établir cette correspondance, nous devons effectuer une recherche rapprochée, comme le propose la fonction Excel RechercheV avec son dernier argument booléen réglé sur True. Un salarié est référencé avec 4 enfants par exemple. La recherche du chiffre 4 n'est pas concluante dans les tranches. La fonction prélève donc le résultat inférieur le plus proche, soit 3. Il en résulte une aide de 75 Euros par enfant, correspondant parfaitement à la catégorie recherchée.

Recherche approchée par requête Access
La fonction Excel RechercheV s'inspire des requêtes Access. Ce sont les requêtes les véritables outils de recherche et d'extraction dans les bases de données. Elles proposent une souplesse et une puissance que les fonctions, même Index et Equiv, ne peuvent offrir. C'est pourquoi la fonction RechercheV n'existe pas dans Access.

Il s'agit d'établir une requête capable de récupérer dynamiquement l'information sur le nombre d'enfants, pour le salarié en cours de consultation depuis le formulaire. Cette donnée doit lui permettre d'exclure les tranches d'aides non correspondantes. Mais elle doit aussi servir à réaliser le calcul finalisé de l'aide totale attribuée. Il s'agira ensuite d'attacher ces résultats dynamiques sur le formulaire des salariés, bien que ce dernier ait été construit en amont sans les considérer.

• 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 tranches,
• Cliquer sur le bouton Ajouter puis sur le bouton Fermer,
• Dans la représentation schématique de la table, sélectionner les deux champs Tranches et Seuils,
• Les glisser sur la grille de requête située juste en dessous pour les y déposer,
• Pour le champ Tranches, décocher la case de la zone Afficher,

Ce champ est utile et précieux pour la construction de la requête dynamique. C'est lui d'ailleurs qui va permettre d'établir la correspondance avec le salarié du formulaire. Néanmoins, ses informations ne sont pas utiles à l'affichage. C'est pourquoi nous l'intégrons dans la construction en le masquant.

D'ailleurs, il s'agit dans un premier temps d'exclure toutes les tranches qui sont directement supérieures au nombre d'enfants du salarié. Si l'information retournée pour l'enregistrement en cours depuis le formulaire indique 4 enfants, nous savons que les intervalles 5 à 8 et 8 à 10 sont nécessairement hors catégorie. La formation Access pour relier des listes déroulantes sur un formulaire, nous avait appris à bâtir un critère de requête en fonction de la valeur d'un contrôle de formulaire. Il s'agit d'exploiter le générateur d'expression.

• Dans la zone Critères du champ Tranches, cliquer avec le bouton droit de la souris,
• Dans le menu contextuel, cliquer sur Créer,

Nous affichons ainsi le générateur d'expression. Il offre des fonctions de calculs mais aussi l'arborescence des objets Access. Et c'est cette arborescence qui va nous permettre de descendre jusqu'au formulaire afin de désigner le contrôle du nombre d'enfants, pour la correspondance.

• Dans la zone de saisie du générateur, taper tout d'abord l'inégalité suivante : <=,
• Dans la liste de gauche des éléments d'expression, déployer complètement la liste des formulaires,
• Sélectionner alors l'objet formulaire_sal,
• Dans la liste du centre des catégories d'expressions, double cliquer sur le champ NbEnfants,

L'expression se construit instantanément dans la partie supérieure du générateur d'expression :

<=[Formulaires]![formulaire_sal]![NbEnfants]

Attention, le champ à désigner est le champ Nb Enfants avec un espace dans le nom. Il est masqué sur le formulaire. Mais c'est bien lui qui renferme l'information numérique permettant de satisfaire l'inégalité. Le champ nommé nbEnfants sans espace est issue du champ calculé pour réaliser l'accord, donc la concaténation textuelle.

• Cliquer sur le bouton Ok pour valider l'expression,
• Enregistrer la requête (CTRL + S) sous le nom requete_aides,

Si vous lancez la requête à ce stade, elle ne produit aucun résultat puisque le formulaire n'est pas exécuté. En conséquence, la valeur dynamique du champ Nb Enfants ne peut lui être transmise.

• Basculer sur le formulaire en mode création et l'exécuter (Touche F5 du clavier),
• A l'aide de la barre de navigation placée en bas de la fenêtre, atteindre le 5^ème enregistrement (Mr Céhef - 3 enfants),
• Revenir sur la requête en mode création,
• Cliquer sur le bouton Affichage pour l'exécuter,

Comme vous le constatez, deux enregistrements subsistent sur les 5 de départ. Les autres ont naturellement été chassés par le critère dynamique de la requête. La tranche correspondant à la valeur dynamique récupérée, est celle de l'aide à 75 Euros. Nous devons donc chasser l'autre. Nous allons commencer par trier ces résultats dans l'ordre décroissant. Ainsi la valeur à considérer sera positionnée en tête de liste.

• Cliquer sur la flèche du bouton Affichage dans le ruban Accueil,
• Dans la liste, choisir Mode création,
• A l'aide de la liste déroulante dans la zone Tri du champ Seuil, choisir un ordre décroissant,

Avant de chercher à isoler la valeur restituée à récupérer, nous devons créer un champ calculé pour déterminer l'aide totale allouée. Le calcul consiste à multiplier le nombre d'enfants par l'aide octroyée. Comme nous l'a appris la formation Access pour classer les résultats, l'expression du champ calculé se crée dans la grille de requête. Elle doit être préfixée du nom du nouveau champ suivi du symbole deux points (:). Nous proposons de l'appeler Aide.

• Dans la grille de requête, à droite du champ Seuils, créer le champ calculé Aide, selon la syntaxe suivante :

Aide : [Formulaires]![formulaire_sal]![Nb Enfants]*[Seuils]



Pour désigner le champ Nb Enfants du formulaire, il est conseillé de récupérer l'expression produite par le générateur, sans les symboles de l'inégalité. Nous multiplions cette donnée dynamique par l'aide allouée par enfant, stockée dans le champ Seuils. Un champ se désigne entre crochets dans Access ([Seuils]). Il n'est pas nécessaire de le préfixer des objets qui le contiennent, comme pour le champ du formulaire. Le champ Seuil fait précisément partie de la requête que nous sommes en train de construire. Access fait naturellement le lien.

• Enregistrer les modifications et exécuter la requête,

Nous retrouvons les enregistrements précédemment sélectionnés par la requête. Mais cette fois, un champ supplémentaire apparaît. Il s'agit bien du champ calculé Aide que nous venons de construire. Il multiplie l'aide unitaire par le nombre d'enfants, soit 3 pour le salarié en cours depuis le formulaire.

Il s'agit désormais de prélever l'aide correspondant à la bonne tranche. Cette catégorie est celle qui est directement inférieure. Comme nous avons classé les résultats par ordre décroissant, l'astuce consiste à indiquer à la requête de ne conserver que le premier des deux. C'est une toute petite adaptation de la syntaxe SQL de la requête qui permet d'obtenir ce résultat.

• Dans le ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans la liste choisir Mode SQL,

Nous basculons dans l'éditeur SQL des requêtes Access. Nous y trouvons la syntaxe de la requête que nous venons de construire. Elle pourrait être largement simplifiée. Mais nous ne nous y attarderons pas.

• Après le tout premier mot clé SELECT, ajouter l'instruction suivante : TOP 1,

L'instruction complète est désormais la suivante :

SELECT TOP 1 tranches.Seuils, [Formulaires]![formulaire_sal]![NbEnfants]*[Seuils] AS Aide FROM tranches WHERE(((tranches.Tranches) <= [Formulaires]![formulaire_sal]![Nb Enfants])) ORDER BY tranches.Seuils DESC;

Select est l'instruction qui permet d'enclencher une requête sélection. La clause From indique à partir de quelle table cette sélection s'effectue. En ajoutant l'instruction TOP 1, nous demandons de limiter cette sélection au tout premier enregistrement, celui situé en haut de la liste (TOP). Si nous avions saisi le chiffre 2, nous aurions obtenu les deux premiers enregistrements.

• Enregistrer les modifications et exécuter la requête,

Cette fois, seule l'aide correspondante et son calcul associé subsistent. Il s'agit des données dynamiques qui correspondent à l'enregistrement en cours sur le formulaire. Nous allons donc devoir les exploiter pour les rattacher dans la même vue.

Intégrer des données dynamiques sur un formulaire
Le défi consiste à rattacher les aides calculées sur le formulaire. Or ce dernier a été bâti sur la requête requete_sal. Elle-même puise ses ressources sur la table salaries. Les aides proviennent de la table tranches. Et ces deux tables ne peuvent être reliées entre elles. C'est donc bien notre requête qui va permettre de réaliser la jonction entre les enregistrements des deux tables. Elle isole l'enregistrement et recalcule la valeur de ses champs dynamiquement, en fonction de la donnée du contrôle Nb Enfants sur le formulaire. Il suffit donc de les insérer sur le formulaire. Et pour ce faire, une méthode simple consiste à exploiter un sous-formulaire bâti sur la requête.

Comme nous l'avait appris le cas pratique sur la facturation Access, un sous formulaire procure l'avantage de réactualiser automatiquement ses informations, au changement d'enregistrement depuis le formulaire parent. Nous n'aurons donc pas besoin de gérer cet événement.

• Revenir sur le formulaire et cliquer sur la flèche du bouton Affichage dans le ruban Accueil,
• Dans la liste, choisir Mode création,
• Dans le ruban Création, déployer la liste des contrôles,
• Choisir le contrôle Sous-formulaire/Sous-état comme l'illustre la capture ci-dessous,

• Le tracer dans le premier rectangle, sous les autres contrôles,
• Dans l'assistant qui se déclenche automatiquement, cliquer sur le bouton Suivant,
• Dans l'étape qui suit, choisir la requête requete_aides avec la liste déroulante,
• Puis, cliquer sur le bouton matérialisé par deux symboles supérieur (>>) pour basculer les valeurs de la section Champs disponibles vers la section Champs sélectionnés,

Ainsi, nous les intégrons comme sources pour la construction du sous formulaire.

• Cliquer sur le bouton Suivant,

Dans cette nouvelle étape, Access propose d'établir la liaison entre le formulaire et le sous formulaire, en désignant les champs liés de chaque source. Comme nous l'avons dit, la relation naturelle n'existe pas. C'est le calcul dynamique de la requête qui s'en charge. Nous devons donc ignorer cette demande.

• Cliquer sur le bouton Suivant pour atteindre la dernière étape de l'assistant,
• Le nommer sal_aides sous-formulaire et cliquer sur le bouton Terminer,
• Enregistrer les modifications (CTRL + S),
• Puis, l'exécuter (Touche F5 du clavier),
• Enfin, naviguer au travers des enregistrements pour vérifier la cohésion,

Comme vous le constatez, la liaison est parfaitement établie. A chaque fois que le salarié change, la requête récupère l'information sur le nombre d'enfants. Elle isole l'aide correspondante. Le champ calculé fait le reste pour fournir l'aide totale allouée. Et ces informations sont désormais restituées et rattachées par le biais du sous formulaire.

En revanche, la présentation laisse à désirer. Le sous formulaire déborde. De plus, dans ce mode de présentation en feuille de données, il ne donne pas l'illusion que les nouveaux contrôles sont parfaitement intégrés. Pour pallier ces défauts, nous proposons de retravailler l'aspect du sous formulaire dans un premier temps.

• Fermer le formulaire en cliquant sur la croix de son onglet,
• Dans le volet des objets Access, cliquer avec le bouton droit sur le sous formulaire,
• Dans le menu contextuel, choisir Mode création,
• Si elle n'est pas visible, cliquer sur le bouton Feuille de propriétés dans le ruban Création,
• Activer l'onglet Format de la feuille de propriétés du sous formulaire,
• Régler sa propriété Affichage par défaut sur : Formulaire unique,
• Régler sa propriété Afficher sélecteur sur : Non,
• Régler sa propriété Boutons de déplacement sur : Non,
• Enregistrer les modifications (CTRL + S),

La valeur de propriété Formulaire unique permet d'afficher les champs du sous formulaire de la même manière que les contrôles du formulaire parent. Ils ne donneront plus l'illusion d'avoir été intégrés après coup dans un sous formulaire. Les boutons de déplacement ne sont pas nécessaires puisque le sous formulaire ne restitue qu'un seul enregistrement par salarié. Le sélecteur est utile seulement en mode feuille de données afin de pointer sur l'enregistrement en cours. Il est habituellement matérialisé par une petite flèche en direction de la feuille.

Pour parfaire ces réglages, il s'agit d'attribuer des couleurs d'arrière-plan identiques à celles du formulaire parent.

• Cliquer sur la section Détail du formulaire pour désigner son contenu,
• Dans l'onglet Format de sa feuille de propriétés, lui attribuer un violet pâle pour sa couleur de fond,
• Supprimer les étiquettes des champs Seuils et Aide,
• Réduire leur largeur et les positionner sur la même ligne,
• Leur attribuer une couleur de fond dans un violet pâle,
• Passer la taille de leur police à 14 pt,
• Définir leur hauteur sur 0,7 cm grâce à la feuille de propriétés,
• Enregistrer les modifications et fermer le sous formulaire,

• Dans le volet des objets Access, cliquer avec le bouton droit sur le formulaire parent,
• Dans le menu contextuel, choisir Mode création,
• Supprimer l'étiquette du sous formulaire,
• Dans le ruban Format, définir son contour sur Transparent,
• Positionner le formulaire pour placer les contrôles sous ceux du formulaire parent,
• Enregistrer les modifications et exécuter le formulaire,

Bien sûr, il convient de redimensionner les contrôles du sous formulaire en fonction de la taille des contrôles du formulaire. La feuille de propriétés est faite pour ça. Mais comme il ne s'agit pas de la vocation de cette formation, nous ne nous attardons pas sur ces notions déjà enseignées.

• Afficher de nouveau le formulaire en mode création,

Déplacements au travers des enregistrements
Comme nous l'avons évoqué en préambule, le principe consiste à remplir une liste déroulante du nom des salariés. Ces derniers doivent être triés par ordre croissant pour proposer la même chronologie que le formulaire. Une requête est un objet idéal pour charger le contenu d'une liste déroulante. Mais il n'est pas nécessaire d'en construire une nouvelle. La requête qui a servi à construire le formulaire parent propose déjà l'organisation des salariés par ordre croissant. Il suffit de la faire pointer sur le champ Nom pendant la construction.

• Dans le ruban Création, déployer la liste des contrôles,
• Sélectionner le contrôle Zone de liste déroulante,
• Le tracer sur le deuxième rectangle vide sur la droite du formulaire,
• Dans l'assistant qui se déclenche, conserver le premier choix : Je veux que la liste...,
• Cliquer sur le bouton Suivant pour afficher la nouvelle étape,
• Cocher la case Requêtes et sélectionner la requête requete_sal,
• Puis, cliquer sur le bouton Suivant,

• Sélectionner le champ Nom dans la liste Champs disponibles,
• Cliquer sur le bouton à une flèche pour le basculer dans la liste Champs sélectionnés,

Nous désignons ainsi le contenu source pour la liste déroulante.

• Cliquer de nouveau sur le bouton Suivant,

Dans cette nouvelle étape, il n'est pas nécessaire de spécifier un tri puisqu'il a déjà été paramétré en amont, grâce à la requête.

• Cliquer sur le bouton Suivant,

L'assistant fournit un aperçu du contenu tel qu'il sera organisé dans la liste déroulante. Nous constatons à ce titre que les noms sont bien triés par ordre croissant.

• Cliquer une dernière fois sur le bouton Suivant pour atteindre la dernière étape de l'assistant,
• Conserver le choix par défaut : Mémoriser la valeur pour usage ultérieur,
• Enfin, cliquer sur le bouton Terminer pour créer la liste déroulante,

Dans la dernière étape, un autre choix proposait de mémoriser la valeur de la liste dans un champ de table. Nous n'avons prévu aucun champ pour ce faire. De plus, il s'agit d'un outil de navigation destiné à modifier régulièrement sa valeur. Nous n'avons donc aucune raison de la stocker.

La liste déroulante apparaît sur le formulaire. Il convient de replacer le contrôle et son étiquette ainsi que de les formater. La feuille de propriétés est l'outil idéal.



Si vous exécutez le formulaire, vous constatez que la liste déroulante permet bien de choisir parmi tous les salariés, triés dans l'ordre croissant. Bien sûr à ce stade, ce choix n'engage aucune action. Il doit permettre de déplacer les enregistrements jusqu'au salarié désigné. Nous proposons de déclencher une toute petite instruction VBA sur l'événement après MAJ. L'autre méthode possible consiste à paramétrer une macro. Après MAJ signifie littéralement : Après mise à jour, soit lorsqu'une autre valeur a été choisie et affichée dans la liste déroulante.

• Revenir en mode création du formulaire si vous l'aviez exécuté,
• Sélectionner le contrôle de la liste déroulante,
• Activer l'onglet Autres de sa feuille de propriétés,
• Dans sa propriété Nom, saisir : liste_noms et valider par Entrée,

Un nom explicite est important pour piloter un contrôle, notamment par le code VBA.

• Activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le petit bouton à trois points de son événement Après MAJ,
• Dans la boîte de dialogue qui suit, double cliquer sur Générateur de code,

Nous basculons ainsi dans l'éditeurde code VBA Access, plus précisément entre les bornes de la procédure évènementielle liste_noms_AfterUpdate. L'instruction que nous y ajouterons se déclenchera donc après mise à jour de la liste (AfterUpdate).

• Entre les bornes de la procédure, ajouter l'instruction suivante :

DoCmd.GoToRecord acDataForm, 'formulaire_sal', acGoTo, liste_noms.ListIndex + 1

L'objet VBA Access DoCmd est un objet de programmation puissant et polyvalent. C'est ce que nous avait appris la formation VBA pour faire interagir les objets Access. Sa méthode GoToRecord permet de réaliser un déplacement sur les enregistrements. Nous lui indiquons en premier paramètre l'objet concerné (acDataForm), soit un formulaire. En deuxième paramètre, nous précisions le nom du formulaire en question, entre guillemets car il s'agit d'un texte. En troisième paramètre nous déterminons la méthode de déplacement. acGoTo permet d'atteindre un enregistrement par son emplacement, soit son numéro. Comme nous l'avons dit, les enregistrements du formulaire sont organisés dans le même ordre que ceux de la liste déroulante. C'est pourquoi nous exploitons la propriété ListIndex de l'objet liste déroulante liste_noms en dernier paramètre. Elle renvoie la position de la donnée sélectionnée. Mais comme une liste déroulante repère son premier élément à l'indice 0, nous corrigeons ce décalage en incrémentant la valeur d'une unité (+1).

• Enregistrer les modifications et fermer l'éditeur de code VBA Access,
• Exécuter le formulaire et choisir un salarié dans la liste déroulante,

Comme vous le constatez, cette petite instruction VBA valait la peine d'être tentée. Elle est très simple et permet de naviguer au travers des enregistrements du formulaire sur demande.

Cette formation est terminée. Son objectif principal consistait à reproduire le mode de fonctionnement d'une rechercheV Excel. C'est une requête Access qui a donné la solution pour lier dynamiquement les informations sur le formulaire.