Création d'un formulaire de recherche multicritère

Étape 1 :

Étape 2 :

Étape 3 :

Étape 4 :

Étape 5 :

Étape 6 :

Cette phase de la création de notre contrôle va nous permettre de :
- renommer le contrôle qui pour l'instant a reçu un nom par défaut (ModifiableX - X étant un numéro affecté par Access) ;
- modifier le contenu de la liste afin de pouvoir la rendre solidaire des trois autres ;
- ajouter les expressions critères dans la requête source de la liste ;
- ajouter dans la source de notre liste la mention « --- Tous --- ».
En cliquant droit sur le contrôle, affichez les propriétés.

Première modification : changer le nom de notre contrôle.

Seconde modification : rendre la liste dépendante des trois autres.

Troisième modification : ajouter les critères sous les champs CodeDiplome, CodePosteOccupe, CodeFamille.
Si pour les Utilisateurs confirmés la création d'une expression ne pose pas de problème particulier, je vais quand même repasser en détail les manipulations à faire pour créer une expression critère en utilisant le générateur d'expression.
Avant de passer au pas à pas pour la création de l'expression, il me faut préciser que la fonction que nous allons utiliser est une fonction conditionnelle du même type que la fonction SI() d'Excel. En effet, nous ne récupèrerons la valeur de la liste déroulante que si un choix a été fait dans la liste.
Ainsi, sous le champ « GExpression2 », si la liste CodeDiplome affiche la mention « ---Tous--- » alors je n'ai pas de critère sinon je récupère la valeur du choix.
Cette fonction qui s'appelle VraiFaux dans Access s'articule comme SI.
Elle se décompose en trois arguments :
=VraiFaux(Condition; Valeur si Condition vraie; Valeur si Condition fausse).

Étape 1 :

Étape 2 :

Étape 3 :

Étape 4 :

Quatrième modification : ajouter la mention « Tous » dans la liste.
Nous devrons intervenir dans la syntaxe SQL de la requête source de notre liste.
Il nous faut donc retourner dans les propriétés de notre liste afin d'afficher le générateur de requête.

Cliquez à la fin de la syntaxe affichée, supprimez le caractère « ; » et tapez :
UNION SELECT 0, « ---Tous--- » FROM T_Agences
Fermer la fenêtre Sql et cliquez sur Oui pour enregistrer les modifications.

Voici l'image de la requête source de notre sous-formulaire. Nous aurons besoin de recréer les mêmes critères que dans les différentes listes. En effet, lorsqu'un choix est fait dans une des listes, il faut que le sous-formulaire soit mis à jour en fonction du choix.
Nous retrouvons sous les quatre champs codes (Agence, Diplôme, Famille, PosteOccupe) la même syntaxe « VraiFaux » que nous avions créée dans nos différentes listes du formulaire principal.
Enregistrez la requête sous le nom de « R_Personnel ».

III-A-2-b-i. Création de l'entête et de la section Détail▲

III-A-2-b-ii. Création du pied de formulaire▲

III-A-2-c-i. Propriétés du sous-formulaire▲

III-A-2-c-ii. La section Pied de formulaire▲

Ce bouton est implanté via la boîte à outils. Le dessin de celui-ci ne présente pas de difficultés particulières. Cependant, il faudra bien lui appliquer une action lorsqu'on cliquera dessus. Dans un premier temps, cliquez sur le bouton « Assistants de contrôle » de la boîte à outils afin de désactiver ceux-ci.
Cliquez à nouveau dans la boîte à outils sur l'outil « Bouton » et dessinez un rectangle sur le formulaire principal.
Affichez les propriétés du bouton et modifiez les propriétés suivantes :
- Nom (onglet « Autres ») : btnEffacerTout
- Légende (onglet : « Format ») : Effacer tous les filtres

Description de la fenêtre « Macro »
Au niveau de l'image de gauche
1) Bouton qui affiche ou masque la colonne « Nom de la Macro ». (Marquée également par (1) à gauche de l'image.)
En identifiant les macros par un nom, cela nous permet d'en regrouper plusieurs dans une même feuille.
2) Colonne « Action » : permet de choisir l'action que la macro devra exécuter.
3) Colonne « Commentaire » : reçoit un texte explicitant l'action exécutée.

Au niveau de l'image de droite
1) Argument : zone permettant de paramétrer l'action à exécuter.
2) Aide contextuelle sur l'argument en cours de renseignement. (À noter : F1 toujours de rigueur pour compléter l'information.)

Nous allons écrire une macro qui va initialiser les différents éléments de notre formulaire à l'ouverture de celui-ci ou en cliquant sur le bouton que nous venons de dessiner.

Pour écrire une macro :
- dans la fenêtre « Base de données », cliquez sur l'objet « Macros » puis « Nouveau » ;
- dans la fenêtre affichée, cliquez sur l'outil marqué (1) dans l'image de gauche ci-dessus. (La colonne « Nom de la macro » s'affiche. (Numérotée (1) également).) ;
- dans la colonne de gauche, saisir un nom suffisamment évocateur de l'action de la macro (ici : Initialisation) ;
- dans la seconde colonne intitulée « Action », sélectionnez l'action « DéfinirValeur ». En choisissant une action, le volet des arguments s'affiche ;
- renseignez les arguments comme dans le tableau ci-dessous.

Lorsque les valeurs sont affectées aux différentes listes, il faut « Actualiser » le sous-formulaire et la zone de liste. Nous allons donc ajouter deux actions à notre macro comme indiqué ci-dessous.

Il faut maintenant enregistrer la macro. Cliquez sur « Enregistrer » puis saisissez le nom « M_ManipulationsControles »

Ce bouton est bâti de la même façon que le précédent. Je vous laisse donc le réaliser.
Les propriétés modifiées seront :
- Nom (onglet « Autres ») : btnImprimerFiltre ;
- Légende (onglet : « Format ») : Imprimer le filtre.

Cette macro aura pour but de lancer l'impression en mode Aperçu. Elle se construira de la même façon que la précédente.
- Cliquez sur l'objet Macros.
- Cliquez sur Nouveau.
- Afficher la colonne Nom de la macro en cliquant sur l'outil concerné.
- Tapez Imprimer. Ce sera le nom de notre macro.
- Saisissez les différents éléments de la macro repris dans le tableau ci-dessous.
- Puis enregistrez la macro sous le nom « M_ImprimerFiltre » et fermez.

Dans le groupe « Macros », faites « Nouveau » et composez la macro comme détaillée dans le tableau ci-dessous. Cette fois-ci, il ne sera pas nécessaire de faire afficher la colonne des noms puisque notre feuille ne contiendra qu'une seule macro.

L'action « OuvrirFormulaire » demande plusieurs arguments. Voir le tableau ci-dessous.

Fermez et enregistrez la macro sous le nom de « M_OuvrirFiche ».
Pour implanter la macro, il nous faut faire appel aux propriétés de la liste « lstEmploye ».
- Cliquez droit sur le contrôle.
- Affichez les propriétés et cliquez dans l'onglet « Évènements ».
- Cliquez dans la propriété « sur Double Clic ».
- Dans la liste déroulante, choisissez la macro « M_OuvirFiche ».
- Enregistrez le formulaire.

Cette procédure doit, en fait, afficher tout le contenu. Elle sera donc également utilisée sur le « clic » du bouton « Effacer tout ». Nous écrirons une routine qui pourra être appelée plusieurs fois

Sub InitialisationFormulaire()
' Initialisation des variables
p_strSqlWhere = ""

'Réinitialisation du tableau Critères
For p_intCompteur = 0 To UBound(p_tabCriteres, 2)
    p_tabCriteres(1, p_intCompteur) = "Pas de critère pour ce champ"
Next

' Initialisation du sous-formulaire et réinitialisation
Me.SF_FiltreAvecCode.Form.RecordSource = cstSourceFiltre
Me.SF_FiltreAvecCode.Requery
' Initialisation de la zone de liste et réinitialisation
Me.lstEmploye.RowSource = cstSourceFiltre
Me.lstEmploye.Requery
End Sub

Dans ce code, on peut remarquer la partie « Réinitialisation des variables »

' Initialisation des variables
p_strSqlWhere = ""
'Réinitialisation du tableau Critères
For p_intCompteur = 0 To UBound(p_tabCriteres, 2)
    p_tabCriteres(1, p_intCompteur) = "Pas de critère pour ce champ"
Next

Ces variables ont été déclarées en public, car elles sont réutilisées dans différentes procédures du projet.
La variable p_strSqlWhere est de type string, elle représente la clause WHERE qui sera appliquée à la source du sous-formulaire et récupère par concaténation, les différents critères sélectionnés.
D'autre part, la variable p_tabCritères() est une variable tableau dont l'objet est de stocker les valeurs de critères choisies pour les imprimer dans l'état (voir cahier des charges).

' Initialisation du sous-formulaire et réinitialisation
Me.SF_FiltreAvecCode.Form.RecordSource = cstSourceFiltre
Me.SF_FiltreAvecCode.Requery
' Initialisation de la zone de liste et réinitialisation
Me.lstEmploye.RowSource = cstSourceFiltre
Me.lstEmploye.Requery

Il s'agit là de l'affectation de la source « cstSourceFiltre » au sous-formulaire et à la zone de liste. Cette constante est déclarée « Public » puisqu'elle sera réutilisée comme source de l'état.
Cette procédure étant mise en place, on peut l'implanter sur l'évènement « Sur Ouverture » du formulaire

Private Sub Form_Open(Cancel As Integer)
    ' Initialisation du formulaire
    InitialisationFormulaire
End Sub

En fait, il s'agit ici de réinitialiser tous les objets du formulaire principal.

Private Sub btnEffacerLesCriteres_Click()
    ' Initialisation du formulaire
    InitialisationFormulaire
End Sub

Il s'agit en fait d'une procédure qui permettra d'appliquer facilement le même process sur tous les contrôles du sous-formulaire. En effet, chaque contrôle du sous-formulaire est censé réagir à un double clic récupérant la valeur du champ pour l'utiliser en tant que critère. Cela sous-entend qu'il faut répéter la même procédure pour chaque contrôle (ouf !!!, il n'y en aurait que cinq).
Afin d'éviter un travail fastidieux, nous allons implanter par programmation, dans la propriété « sur double clic » de chaque contrôle concerné, une fonction qui récupèrera notre critère sélectionné.

Private Sub Form_Load()
' Déclaration de la variable
Dim ctlEnCours As Control
' Redimensionnement du tableau
ReDim p_tabCriteres(1, p_intCompteur)
' Boucle sur toutes les zones de texte pour affecter la procédure de filtrage sur l'évènement DoubleClic
For Each ctlEnCours In Me.Controls
   If ctlEnCours.ControlType = acTextBox Then
        If Left(ctlEnCours.Name, 3) <> "txt" Then
            ctlEnCours.Properties("onDblClick") = "=FiltreDonnees('" & ctlEnCours.Name & "' , " & ctlEnCours.Name & ")"
            ' renseigne le tableau avec les noms des champs
            RemplirTabCriteres (ctlEnCours.Name)
      End If
   End If
Next ctlEnCours
' Désactivation de la variable
Set ctlEnCours = Nothing
End Sub

Dans cette procédure on remarquera :
- la déclaration de la variable ctlEnCours ;
- le redimensionnement du tableau dynamique p_tabCriteres.

' Déclaration de la variable
Dim ctlEnCours As Control
' Redimensionnement du tableau
ReDim p_tabCriteres(1, p_intCompteur)

Dans le redimensionnement du tableau, on trouve une variable publique p_intCompteur qui est incrémentée dans un sous-programme : RemplirTabCriteres que l'on verra un peu plus loin dans l'article.

' Boucle sur toutes les zones de texte pour affecter la procédure de filtrage sur l'évènement Double clic
For Each ctlEnCours In Me.Controls
   If ctlEnCours.ControlType = acTextBox Then
        If Left(ctlEnCours.Name, 3) <> "txt" Then
            ctlEnCours.Properties("onDblClick") = "=FiltreDonnees('" & ctlEnCours.Name & "' , " & ctlEnCours.Name & ")"
            ' renseigne le tableau avec les noms des champs
            RemplirTabCriteres (ctlEnCours.Name)
      End If
   End If
Next ctlEnCours

Ci-dessus la boucle qui teste le type de contrôle et qui implante la fonction FiltreDonnees sur l'évènement « sur Double clic ».
Au fur et à mesure de la boucle, on renseigne le tableau p_tabCriteres par l'appel de la procédure :

      ' renseigne le tableau avec les noms des champs
            RemplirTabCriteres (ctlEnCours.Name)

Dans le cahier des charges, on nous demande de faire apparaître les critères sélectionnés dans l'entête de l'état. Pour pouvoir récupérer les valeurs, je les stocke dans un tableau.

Sub RemplirTabCriteres(ByVal strNomChamp As String)
    ' Procédure qui permettra de respecter la consigne du cahier des charges : Imprimer les critères sélectionnés dans l'état.
    ' On récupère les noms des champs dans un tableau avec comme valeur par défaut : "Pas de critère pour ce champ"
    If p_intCompteur > 0 Then
      ReDim Preserve p_tabCriteres(1, p_intCompteur)
    End If
      ' renseigne le tableau avec les noms des champs et la mention par défaut
      ' qui seront affichés dans l'état
      p_tabCriteres(0, p_intCompteur) = strNomChamp
      p_tabCriteres(1, p_intCompteur) = "Pas de critère pour ce champ"
      p_intCompteur = p_intCompteur + 1
End Sub

Dans cette procédure, je ferai simplement un commentaire :

    If p_intCompteur > 0 Then
      ReDim Preserve p_tabCriteres(1, p_intCompteur)
    End If

Ce test vérifie que le tableau contient des valeurs et redimensionne celui-ci tout en préservant le contenu déjà stocké.
Enfin, il nous reste à découvrir la procédure FiltreDonnees

Function FiltreDonnees(ByVal strNomChamp As String, ByVal varValeurChamp As Variant)
    ' Teste le contenu du contrôle
    If IsNumeric(varValeurChamp) Then
        ' Initialisation du contenu de p_strSqlWhere sans ajout de quotes si le contenu est ALPHA
        If p_strSqlWhere = "" Then
            p_strSqlWhere = "WHERE " & strNomChamp & " = " & varValeurChamp
        Else
            p_strSqlWhere = p_strSqlWhere & " AND " & strNomChamp & " = " & varValeurChamp
        End If
    Else
        ' Initialisation du contenu de p_strSqlWhere avec ajout des quotes si le contenu est ALPHA
        If p_strSqlWhere = "" Then
            p_strSqlWhere = "WHERE " & strNomChamp & " = '" & varValeurChamp & "'"
        Else
            p_strSqlWhere = p_strSqlWhere & " AND " & strNomChamp & " = '" & varValeurChamp & "'"
        End If
    End If
    
    ' Récupération de critère sélectionné afin de reporter ces données dans l'état
    For p_intCompteur = 0 To UBound(p_tabCriteres, 2)
        If p_tabCriteres(0, p_intCompteur) = strNomChamp Then
            p_tabCriteres(1, p_intCompteur) = varValeurChamp
            Exit For
        End If
    Next
    
    ' réactualisation du sous-formulaire
    Me.RecordSource = cstSourceFiltre & p_strSqlWhere
    Me.Requery
    ' réactualisation de la zone de liste du formulaire principal
    With Forms("F_FiltreAvecCode").Controls("lstEmploye")
        .RowSource = cstSourceFiltre & p_strSqlWhere
        .Requery
    End With
End Function

Comme vous pourrez le constater, il s'agit d'une fonction.
Or une fonction est censée retourner une valeur, mais pour ce cas, j'ai écrit une procédure « Function », car je ne peux pas faire appel à une procédure de type Sub directement dans une propriété du formulaire

    ' Teste le contenu du contrôle
    If IsNumeric(varValeurChamp) Then
        ' Initialisation du contenu de p_strSqlWhere sans ajout de quotes si le contenu est ALPHA
        If p_strSqlWhere = "" Then
            p_strSqlWhere = "WHERE " & strNomChamp & " = " & varValeurChamp
        Else
            p_strSqlWhere = p_strSqlWhere & " AND " & strNomChamp & " = " & varValeurChamp
        End If
    Else
        ' Initialisation du contenu de p_strSqlWhere avec ajout des quotes si le contenu est ALPHA
        If p_strSqlWhere = "" Then
            p_strSqlWhere = "WHERE " & strNomChamp & " = '" & varValeurChamp & "'"
        Else
            p_strSqlWhere = p_strSqlWhere & " AND " & strNomChamp & " = '" & varValeurChamp & "'"
        End If
    End If

Cette partie de la procédure permet de tester le type de valeur récupérée afin d'ajouter les « ' » autour de la valeur critère si celle-ci est alpha.

    ' Récupération de critère sélectionné afin de reporter ces données dans l'état
    For p_intCompteur = 0 To UBound(p_tabCriteres, 2)
        If p_tabCriteres(0, p_intCompteur) = strNomChamp Then
            p_tabCriteres(1, p_intCompteur) = varValeurChamp
            Exit For
        End If
    Next

Cette partie du code complète le tableau en stockant la valeur du critère sélectionné.

Private Sub btnImprimerFiltre_Click()
On Error GoTo Err_btnImprimerFiltre_Click

    Dim stDocName As String

    stDocName = "E_ListePersonnelFiltreeAvecCode"
    DoCmd.OpenReport stDocName, acPreview

Exit_btnImprimerFiltre_Click:
    Exit Sub
Err_btnImprimerFiltre_Click:
    MsgBox Err.Description
    Resume Exit_btnImprimerFiltre_Click
End Sub

Il s'agit d'une procédure simple, créée à partir de l'assistant lors de la génération du bouton.

Private Sub lstEmploye_DblClick(Cancel As Integer)
    DoCmd.OpenForm "F_FicheDetaillee",,, "CodeEmploye = " & lstEmploye
End Sub

Il s'agit de l'ouverture du formulaire « F_FicheDetaillee » en fonction de CodeEmploye récupéré dans la zone de liste.

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = cstSourceFiltre & p_strSqlWhere

Il s'agit ici, de l'affectation de la source qui est composée de la concaténation de la constante cstSourceFiltre et de la variable p_strSqlWhere.

Private Sub EntêteÉtat_Format(Cancel As Integer, FormatCount As Integer)
    For p_intCompteur = 0 To UBound(p_tabCriteres, 2)
        Me.Controls("lblCritere" & p_intCompteur).Caption = p_tabCriteres(0, p_intCompteur)
        Me.Controls("txtCritere" & p_intCompteur) = p_tabCriteres(1, p_intCompteur)
    Next
End Sub

Il s'agit d'une boucle qui relit le tableau p_tabCriteres et qui affecte, aux différents contrôles en fonction de leur type :
- soit le nom du champ ;
- soit la valeur du critère ou la mention par défaut.