Saisie semi-automatique 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 :


Saisie semi-automatique en VBA Access

A l'instar du moteur de recherche Google, nous proposons ici d'aider l'utilisateur dans la saisie de ses termes de recherche, au travers d'un formulaire. Cette saisie intuitive est rendue possible par le déclenchement de codes Visual Basic à la détection d'événements bien particuliers, comme la saisie. Cela implique que chaque frappe de l'utilisateur doit être contrôlée et examinée par le code VBA en temps réel.



Comme l'illustre la capture ci-dessus, une liste des termes correspondants au début de la saisie se déclenche automatiquement, sous la zone du moteur de recherche. L'utilisateur peut poursuivre et les suggestions de la liste s'affinent, ou bien l'utilisateur peut valider l'une des propositions par clic dans la liste, pour lancer la recherche.

Préparation du formulaire de recherche
Pour concentrer nos efforts sur le développement de la recherche intuitive, nous récupérons une source existante. Il s'agit de la base de données des communes françaises avec leurs codes INSEE. Nous l'avions exploitée pour extraire les données de doublons, sans code VBA Access.

• Télécharger la base de données saisie-semie-automatique.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 Extractions_doublons depuis le volet de gauche,

Le formulaire propose déjà certaines fonctionnalités. L'utilisateur peut saisir une ville de recherche et au clic sur le bouton, les résultats s'affichent dans le sous formulaire.

• Taper par exemple le nom de commune Valence et cliquer sur le bouton,

Toutes les villes portant le même nom mais ne figurant pas dans le même département, sont ainsi listées. Nous avions réussi cette extraction des informations de doublons sans faire intervenir de code VBA. Le sous-formulaire est lié à une requête dynamique dont le critère, sur le champ nom_commune, attend la saisie dans la zone du moteur. Au clic sur le bouton, c'est une macro Access qui déclenche une action d'actualisation des enregistrements, afin de rafraîchir les résultats.

Bien sûr à ce stade, aucune suggestion ne se déclenche au fil de la saisie puisqu'il s'agit de l'objectif de cette formation. Nous avons besoin d'une liste déroulante pour charger dynamiquement son contenu en fonction des lettres tapées par l'utilisateur dans le moteur de recherche.

• Dans le ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans la liste, choisir Mode Création,
• Si elle n'est pas visible, cliquer sur le bouton Feuille de propriétés du ruban Création,
• Dans la section Contrôles de ce même ruban, choisir la Zone de liste,
• La tracer sous le moteur de recherche, sur une largeur identique,
• Cliquer sur le bouton Annuler de l'assistant qui se déclenche,
• Sélectionner son étiquette associée et la supprimer,
• Cliquer de nouveau sur la zone de liste pour la sélectionner,
• Saisir liste_communes dans la propriété Nom de l'onglet Autres de sa feuille de propriétés,
• Cliquer sur l'onglet Organiser en haut de la fenêtre Access pour activer son ruban,
• Sur la droite du ruban, cliquer sur le bouton Mettre au premier plan,

L'objectif est de pouvoir placer les suggestions de la liste au-dessus des résultats fournis par le sous formulaire. Bien sûr il s'agira de piloter l'affichage et le masquage de cette zone de liste, en fonction des événements.

• Cliquer sur l'onglet Format en haut de la fenêtre Access pour activer son ruban,
• Lui attribuer un fond vert identique à celui du bouton à l'aide du bouton Remplissage,
• Cliquer ensuite sur le bouton Contour et choisir Transparent dans la liste,
• Définir la taille de police à 12, toujours pour cette zone de liste,
• Enregistrer les modifications(CTRL + S) et exécuter le formulaire (F5),

Comme le montre la capture ci-dessous, le contrôle ListBox pour l'instant vide, apparaît bien au-dessus du sous formulaire. Il est de même visible par défaut. Alors que ses suggestions ne devront apparaître que lorsqu'une saisie sera détectée dans le moteur de recherche.



Cette zone de liste doit être masquée par défaut puis gérée par le code VBA.

• Cliquer sur la flèche du bouton Affichage dans le ruban Accueil,
• Dans la liste, choisir Mode Création,
• Sélectionner le contrôle Zone de liste sur le formulaire,
• Activer l'onglet Format de sa feuille de propriétés,
• Régler sa propriété Visible sur Non,

Si nous exécutons de nouveau le formulaire, le contrôle ListBox n'apparaît plus au-dessus des autres objets. Il est caché jusqu'à nouvel ordre.

Afficher et masquer des contrôles de formulaire
Dès qu'une saisie est détectée dans le moteur de recherche, la zone de liste doit apparaître. Au clic sur l'une des suggestions dans la zone, elle doit disparaître. Mais ce n'est pas tout, un clic sur le bouton doit aussi commander le masquage de la zone de liste.

• Activer l'onglet Evénement du contrôle Zone de liste dans sa feuille de propriétés,
• Cliquer sur le petit bouton à droite de son événement Au clic,
• Dans la boîte de dialogue qui apparaît, choisir Générateur de code et valider par Ok,

Comme nous en avons l'habitude, nous basculons ainsi dans l'éditeur de code VBA Access, entre les bornes de la procédure événementielle liste_communes_Click. Le code VBA que nous ajouterons entre ces bornes, se déclenchera lorsqu'un clic sera détecté dans la zone de liste.

Il s'agit ici de déclencher le masquage de la zone de liste si cette dernière est affichée. Ainsi nous évitons de réaliser des exécutions inutiles. Nous devons donc exploiter l'instruction conditionnelle If incontournable en VBA pour vérifier ce critère.

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

If liste_communes.Visible = True Then liste_communes.Visible = False

Visible est la propriété booléenne d'un contrôle de formulaire, plus précisément du ListBox ici, nommé liste_communes. Nous testons d'abord la valeur de cette propriété pour savoir si le contrôle est affiché (If liste_communes.Visible = True). Et si ce critère est validé, alors nous choisissons de le masquer en basculant sa propriété à False. Pas de End If ici pour boucler l'instruction If. Cette syntaxe sur une seule ligne, est rendue possible lorsqu'une seule action est engagée.

• Sélectionner cette ligne de code et la copier (CTRL + C),
• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + F11),
• Sélectionner la zone de saisie moteur,
• Activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le petit bouton de sa propriété Sur changement,
• Dans la boîte de dialogue, choisir Générateur de code et valider par Ok,
• Entre les bornes de la procédure moteur_Change, coller l'instruction précédemment copiée,
• Changer l'affectation True en False et l'affectation False en True,

If liste_communes.Visible = False Then liste_communes.Visible = True

En effet, cette fois c'est l'inverse. Lorsque VBA détecte une saisie de l'utilisateur dans le moteur de recherche, il doit déclencher l'affichage de la zone de liste. A terme, cette dernière proposera les suggestions en lien avec la recherche.

• Enregistrer les modifications (CTRL + S),
• Basculer sur le formulaire (ALT + F11) et l'exécuter (F5),
• Taper quelques lettres dans le moteur de recherche,

La zone de liste, certes vide, apparaît instantanément. C'est un bon début.

• Cliquer sur cette zone de liste pour déclencher le code qui doit la masquer,

Une erreur est générée. En effet, il est important de comprendre qu'un contrôle de formulaire ne peut pas être masqué tant qu'il est actif. L'astuce consiste donc d'abord à rendre la main au moteur de recherche, puis à masquer le ListBox qui ne sera plus considéré comme activé.

• Cliquer sur le bouton Débogage de la boîte de dialogue d'erreur,

L'instruction incriminée apparaît en surbrillance jaune dans le code et confirme ce que nous venons d'énoncer.

• Cliquer sur le menu Exécution en haut de l'éditeur de code,
• Dans la liste, choisir Réinitialiser,

La conception du code peut reprendre.

• Dans la procédure liste_communes_Click, juste avant le test de l'instruction If, ajouter la ligne de code suivante :

moteur.SetFocus

SetFocus est la méthode qui permet d'activer le contrôle désigné par son nom d'objet. En l'occurrence ici, il s'agit du moteur de recherche nommé moteur. Comme la zone de liste n'est plus active au moment où son masquage est demandé, l'erreur doit disparaître.

• Enregistrer les modifications, basculer sur le formulaire et l'exécuter,
• Taper quelques lettres dans le moteur de recherche pour faire apparaître le ListBox,
• Puis cliquer dans le ListBox,

Cette fois la zone de liste disparaît proprement. Cependant un défaut persiste.

• Dans le moteur de recherche, saisir le nom de ville : Valence,
• Puis, cliquer sur le bouton Rechercher et extraire,

Le ListBox demeure visible. Et comme nous l'avons paramétré au premier plan, il masque partiellement les résultats retournés par la requête dynamique liée au sous formulaire. Donc, en même temps que le bouton réactualise les enregistrements pour mettre à jour les résultats, il doit masquer la zone de liste. Mais ce dernier est déjà associé à une macro. Donc nous ne pouvons pas l'associer en plus à un code VBA. C'est soit l'un, soit l'autre. En revanche, nous pouvons ajouter une action à cette macro qui consiste à déclencher un code. Pour qu'un code VBA puisse être déclenché par une macro, il doit être écrit dans une fonction (Function et non Sub).

• Afficher le formulaire en mode création,
• Réaliser le raccourci clavier ALT + F11 pour revenir dans l'éditeur de code VBA,
• Créer la fonction masquer_liste comme suit :

Function masquer_liste()
If liste_communes.Visible = True Then liste_communes.Visible = False
End Function

L'instruction de la fonction est récupérée de la procédure événementielle : liste_communes_Click. Puisque cette fonction existe, elle peut désormais être appelée par son nom à l'aide d'une macro.

• Enregistrer les modifications et basculer sur le formulaire,
• Cliquer sur le bouton Rechercher et extraire pour le sélectionner,
• Activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le petit bouton en regard de son événement Au clic,

Nous basculons directement dans l'éditeur de macros. En effet une macro est déjà associée à l'événement du clic sur le bouton. L'action AfficherTousEnreg apparaît d'ailleurs dans la liste de l'éditeur. C'est elle qui rafraichit les résultats au clic.

• Sous cette action, cliquer sur la flèche de la liste déroulante pour la déployer,
• Dans la liste, choisir l'action ExécuterCode,
• Dans la zone Nom de la fonction, saisir masquer_liste() comme sur la capture,

Ainsi la macro, après avoir réactualisé les enregistrements, commande l'exécution d'un code VBA, par appel du nom de sa fonction, afin de masquer le ListBox.

• Dans le ruban de la macro, cliquer sur le bouton Enregistrer puis sur le bouton Fermer,
• De retour sur le formulaire, enfoncer la touche F5 pour l'exécuter,
• Taper un nom de ville dans le moteur de recherche, Saillans par exemple,
• Puis, cliquer sur le bouton Rechercher et extraire,

Cette fois, la zone de liste qui était apparue aux premières lettres saisies dans le moteur de recherche, disparaît au clic sur le bouton, en même temps que les résultats de la demande s'affichent dans le sous formulaire.

Maintenant que les contrôles réagissent bien sur ordre, nous devons nous pencher sur la finalité de cette application.

Saisie semi-automatique - Suggestions intuitives
Suggestions intuitives ? Pas vraiment ! Une requête doit se charger de questionner la base de données Access, en fonction de la saisie dans le moteur de recherche, au fur et à mesure que les lettres sont tapées. Et cette requête doit s'exécuter sur ordre, donc par le code VBA. Un code Visual Basic qui se déclenche à chaque changement détecté dans la zone de saisie. Il s'agit donc de faire évoluer la procédure événementielle déjà créée pour faire apparaître la zone de liste.

• Afficher le formulaire en mode création puis basculer dans l'éditeur de code VBA,
• Après l'instruction If dans la procédure moteur_Change, ajouter la ligne suivante :

liste_communes.RowSource = 'SELECT DISTINCT nom_commune FROM Liste_communes WHERE nom_commune LIKE '' & moteur.Text & '*''

Une seule ligne de code suffit a priori, mais elle mérite des explications. Tout d'abord, la propriété RowSource du ListBox list_communes permet de définir dynamiquement la source de données qui remplit le contrôle. Comme cette source de données varie en fonction de la saisie, nous lui affectons une requête SQL qui questionne la base de données, avec une clause WHERE dynamique. Cette requête sélection (SELECT) extrait les noms de communes sans doublons (DISTINCT nom_commune). Cette extraction se fait bien entendu à partir de la table Liste_communes (FROM Liste_communes). Et comme il s'agit de remplir la zone de liste des noms de villes qui commencent strictement comme la saisie utilisateur, nous utilisons une clause WHERE (WHERE nom_commune). Le mot clé LIKE dans la syntaxe SQL permet d'exprimer l'opérateur Comme. En l'occurrence ici, comme le début de la saisie. Et c'est l'étoile (*) concaténée (&) à la variable VBA qui récupère le contenu de la saisie (moteur.Text), qui indique de faire l'équivalence avec les caractères tapés, peu importe ce qui suit. Notez la présence essentielle des simples cotes avant et après les guillemets qui encadrent la concaténation. Ils permettent d'indiquer que la comparaison est textuelle puisque le champ est lui-même de type texte.

• Enregistrer les modifications, basculer sur le formulaire et l'exécuter (F5),
• Taper les premières lettres dans le moteur, par exemple Bourg,

Au fur et à mesure de la saisie, le contenu de la liste déroulante évolue et s'affine grâce à la requête SQL déclenchée par l'événement associé au TextBox.



Cependant à ce stade, au clic sur la liste déroulante afin de valider une des suggestions, rien d'autre que le masquage du ListBox ne se produit. Le code VBA doit transmettre ce choix dans la zone de texte moteur.

• Afficher le formulaire en mode création et revenir dans l'éditeur de code VBA,
• Dans la procédure liste_communes_Click, avant le SetFocus du moteur, ajouter la ligne de code suivante :

moteur.Value = liste_communes.Value

Grâce aux propriétés Value des contrôles respectifs, nous affectons le contenu cliqué par l'utilisateur dans la zone de liste au moteur de recherche. Dans la foulée, pour permettre à l'utilisateur de ne pas avoir à cliquer sur le bouton, nous devons commander l'affichage des résultats. Pour ce faire, il suffit d'exploiter l'objet DoCmd que nous avions étudié dans la formation sur l'interaction des objets en VBA Access. Sa méthode Requery permet de déclencher l'action Actualiser pour mettre à jour les contrôles. Ainsi la source de données du sous formulaire, dépendant de la saisie dans le moteur, se réactualisera.

• Après l'instruction If, dans la procédure liste_communes_Click, ajouter l'instruction suivante :

DoCmd.Requery

• Enregistrer les modifications, basculer sur le formulaire et l'exécuter,
• Taper les premières lettres suivantes dans le moteur : Villefrance s,
• Dans la liste des propositions, cliquer sur Villefranche sur Saone,

Instantanément, en même temps que la proposition est inscrite dans la zone du moteur de recherche, les résultats correspondants sont livrés dans le sous formulaire. Notre saisie semi-automatique est ainsi parfaitement fonctionnelle. Et finalement, très peu de code a été nécessaire à sa construction. C'est toute la puissance d'Access alliant événements, requêtes SQL et code Visual Basic qui rendent les applications les plus complexes, faciles à mettre en oeuvre. Le code complet est présenté ci-après :

Private Sub liste_communes_Click()

moteur.Value = liste_communes.Value
moteur.SetFocus
If liste_communes.Visible = True Then liste_communes.Visible = False
DoCmd.Requery

End Sub

Private Sub moteur_Change()

If liste_communes.Visible = False Then liste_communes.Visible = True

liste_communes.RowSource = 'SELECT DISTINCT nom_commune FROM Liste_communes WHERE nom_commune LIKE '' & moteur.Text & '*''

End Sub

Function masquer_liste()

If liste_communes.Visible = True Then liste_communes.Visible = False

End Function