Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes et SQL. Discussion :

optimiser code sql access par boucle sur tous les chkbox


Sujet :

Requêtes et SQL.

  1. #1
    Membre du Club
    optimiser code sql access par boucle sur tous les chkbox
    Bonjour à tous,

    je cherche à rendre plus clair un code que j'ai rélisé mais qui devient difficile à maintenir compte tenu du nombre de chkbox qui me permettent de contruire ma requête sql.

    J'ai deux catégories de chkbox, l'une préfixée chk_rg, l'autre chk_st.
    Les premières servent à sélectionnés mes champs en select et group by, les secondes servent à réaliser des statistiques, sont donc en select et sont des champs calculés.
    Chacun de ces champs est donc une formule de calcul qui lui est propre.

    Le code que je cherche à réaliser est (plus qu') inspiré d'une FAQ access prise sur le site et donne quelques chose comme ceci :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
     
    Dim ctl as control
    Dim nbChoixRgpt as integer
     
    for each ctl in Me.controls
     
    select case left (ctl.name,6)
     
    case "chk_rg"
     
    if ctl.name.value =1 then
    nbChoixRgpt = nbChoirRgpt +1
     
    if nbChoixRgpt > 1 then
    strChamps = StrChamps & ", "
    strGroup = strGroup & ", "
    End if
     
    strchamps = strChamps & "Formule propre à mon ctl.name"
    strGroup = strGroup & "Autre formule propre à mon même ctl.name"
    End if
     
    Case "chk_st"
    traitement approximativement similaire
     
    End Select
     
    Next ctl
    Je viens de taper ceci à main levée et n'ai encore rien testé sous access mais déjà deux questions me posent problème :

    Est-ce que je peux programmer un ctl.name.value ?
    Comment faire en sorte de paramétrer et récupérer des formules propres à chacun de mes chkbox ?

    Si quelqu'un pouvait me donner la main...

    Merci par avance

    TF
    Si l'homme a deux oreilles pour une bouche, c'est pour écouter deux fois plus qu'il ne parle...

  2. #2
    Responsable
    Office & Excel

    Bonjour

    Balancer un code saisi à la va-vite, sans l'indenter, et demander de l'aide relève de l'ironie, ou de l'utopie, ou d'une foi inébranlable en la compassion et au bon vouloir des individus, ou d'un peu de tout cela, mais aussi, et surtout, d'une méconnaissance des règles de fonctionnement de nos forums, qui formalisent l'attente légitime des contributeurs bénévoles d'un minimum d'implication du demandeur, ce qui ne transparait pas ici.

    La saisie du code dans l'environnement VBA aurait montré que derrière ctl.Name, il n'y a rien, et que cette syntaxe suffit donc à récupérer le nom du contrôle.

    La compréhension minimale de l'anglais du VBA et une utilisation de la saisie semi-automatique permettrait de voir qu'il y a une propriété Value attachée à un contrôle et que, fort logiquement, cette propriété renvoie... la valeur dudit contrôle...

    L'utilisation d'un checkbox permet de déduire que la valeur de ce type de contrôle est une valeur binaire (coché-Non coché, Vrai-Faux, ...)

    Donc, a priori, un message inutile si la personne qui l'a posté s'était donné un peu de peine, en tout cas moins que celle que j'ai eue à écrire cette réponse...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  3. #3
    Membre du Club
    Bonjour à tous,

    Après avoir pris le temps d'effectuer plus de tests en revenant de l'école de ma fille, voici l'avancement de mon code
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    Dim nbChoixRgpt As Integer
    Dim nbChoixStat As Integer
     
    nbChoixRgpt = 0
    nbChoixStat = 0
     
        ' construit les clauses "Select" via strChamps et "group by" via strGroup
        Dim strChamps   As String
        Dim strGroup    As String
     
        Dim ctl As Control
     
        For Each ctl In Me.Controls
     
            Select Case Left(ctl.Name, 6)
                Case "chk_rg"
                    If ctl.Value = 1 Then
                        nbChoixRgpt = nbChoixRgpt + 1
     
                        If nbChoixRgpt > 1 Then
                            strChamps = strChamps & ", "
                            strGroup = strGroup & ", "
                        End If
     
                        strChamps = strChamps & "Formule propre à mon ctl.name"
                        strGroup = strGroup & "Autre formule propre à mon même ctl.name"
                    End If
     
                Case "chk_st"
                    If ctl.Value = 1 Then
                        nbChoixStat = nbChoixStat + 1
     
                        If nbChoixStat > 0 Then
                            strChamps = strChamps & ", "
                        End If
     
                        strChamps = strChamps & "Formule propre à mon ctl.name"
                    End If
     
            End Select
     
        Next ctl
    La construction des variables strChamps et strGroup fonctionne.
    Il s'agit pour moi à présent de renseigner les formules qui vont bien.

    Pour être plus précis :

    - si traitement de chk_rg_annee, on complète :
    strChamps avec "Year(tbl_donnees.date) AS Annee"
    strGroup avec "Year(date)"

    - si traitement de chk_st_vitmoy (pour le calcul de la vitesse moyenne de course), on complète :
    strchamps avec "Avg([tbl_donnees.Distance_km]/([tbl_donnes.Temps]*24)) AS VitesseMoy"

    L'enjeu est de traiter 6 chkbox_rg et 12 chkbox_st avec pour objectif de pouvoir ultérieurement créer de nouvelles possibilité avec des modifications de code limitées et d'avoir une bien meilleure lisibilité de mon code actuel qui fonctionne à l'aide d'une multitude de condition if.

    Merci par avance de votre aide

    TF
    Si l'homme a deux oreilles pour une bouche, c'est pour écouter deux fois plus qu'il ne parle...

  4. #4
    Responsable
    Office & Excel

    Et... Où est le problème? Où est la question? Y-en-a-t-il une?
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  5. #5
    Membre du Club
    Cela me paraissait pourtant clair.

    Lorsque je suis dans ma boucle For each en case chk_rg par exemple sur le 3ème checkbox de ma série, comment m'y prendre pour remplacer les bouts de codes :
    "Formule propre à mon ctl.name"
    "Autre formule propre à mon ctl.name"
    par les valeurs spécifiques à ce chkbox en question ?
    Donc les rendre variable, paramétrer et appeler ces variables.

    C'est probablement simpliste au vu des aimables réponses précédentes mais je bloque;

    Merci de votre aide à tous

    TF

    La provocation et l'agressivité gratuite font rarement bon ménage avec l'efficacité
    Si l'homme a deux oreilles pour une bouche, c'est pour écouter deux fois plus qu'il ne parle...

  6. #6
    Membre expérimenté
    Eh bien il te faut une table de correspondance (ou autre chose) qui te permet de lier une formule à chaque chexbox.

    Après dans tes case, il faudra tester le chexbox aussi pour retrouver la formule liée.
    Amicalement

  7. #7
    Membre du Club
    Erreur 94, utilisation de la valeur Null
    Salut DMBoup et merci bcp pour ton aide.
    Ce qui se conçoit bien s'énonce clairement et c'est vrai qu'une fois, dis, cela paraît tellement évident !!!

    J'ai donc créé une table tbl_formula en bdd avec trois champs control_name, strFormula_rg et strFormula_st.
    J'utilise en code la formule Dlookup pour récupérer mes formules en table selon le ctl.Name de la chkbox concernée.

    Mais là, j'ai un msg d'erreur 94 pour utilisation incorrecte de la valeur Null.
    J'ai essayé d'y remédier par le Nz mais sans succès.
    Est-ce que tu verrais ce qui cloche dans mon code stp ?

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
        For Each ctl In Me.Controls
     
            Select Case Left(ctl.Name, 6)
                Case "chk_rg"
                    If ctl.Value = -1 Then
                        nbChoixRgpt = nbChoixRgpt + 1
     
                        If nbChoixRgpt > 1 Then
                            strChamps = strChamps & ", "
                            strGroup = strGroup & ", "
                        End If
     
                        strCtlName = ctl.Name
     
                        strFormularg = DLookup("[strFormula_rg]", "[tbl_formula]", "[tbl_formula]![control_name] = 'strCtlName'")
                        strFormulast = DLookup("[strFormula_st]", "[tbl_formula]", "[tbl_formula]![control_name] = 'strCtlName'")
     
                        strChamps = strChamps & strFormularg
                        strGroup = strGroup & strFormulast
                    End If
     
                Case "chk_st"
                    If ctl.Value = -1 Then
                        nbChoixStat = nbChoixStat + 1
     
                        If nbChoixStat > 0 Then
                            strChamps = strChamps & ", "
                        End If
     
                        strCtlName = ctl.Name
     
                        strFormulast = DLookup("[strFormula_st]", "[tbl_formula]", "[tbl_formula]![control_name] = 'strCtlName'")
                        strChamps = strChamps & strFormulast
                    End If
     
            End Select
     
        Next ctl
    Merci de ton aide

    TF
    Si l'homme a deux oreilles pour une bouche, c'est pour écouter deux fois plus qu'il ne parle...

  8. #8
    Responsable
    Office & Excel

    Pour compléter DMBoup, maintenant que j'ai compris le problème posé ...

    Créer une table avec au moins trois champs
    Ctl_Name, Ctl_Formule_Champs, Ctl_Formule_Group

    Compléter cette table avec tous les contrôles susceptibles de devoir "servir" leurs formules.

    Dans le code, se passer de la boucle Select Case, identifier le contrôle via un If et rechercher la valeur dans la table
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Private Sub Commande6_Click()
        Dim ctl As Control
        Dim strChamps As String
        Dim strGroup As String
     
        For Each ctl In Me.Controls
            If Left(ctl.Name, 3) = "chk" Then
                If ctl.Value = -1 Then
                    strChamps = strChamps & "," & CurrentProject.Connection.Execute("select Ctl_Formule_Champs from tcontroles where ctl_nom = '" & ctl.Name & "'")!ctl_formule_champs
                    strGroup = strGroup & "," & CurrentProject.Connection.Execute("select Ctl_Formule_group from tcontroles where ctl_nom = '" & ctl.Name & "'")!ctl_formule_group
                End If
            End If
        Next
    End Sub


    Voici un fichier d'exemple

    [EDIT] Une autre solution sans la boucle If...End If consisterait à rechercher le nom du contrôle dans la table ad hoc et si trouvé, à appliquer les formules comme dans le code que j'ai donné, ou le tien avec DLookup. Cela aurait l'avantage de rendre le code plus léger et plus facilement maintenable...[/EDIT]
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  9. #9
    Membre du Club
    merci bcp
    Bonsoir Messieurs,

    merci bcp pour vos aides conjuguées.
    Je n'ai finalement pas réussi à me sortir de mes messages 94 sur la fonction Dlookup, j'ai donc adopté la solution préconisée par Pierre et tou fonctionne parfaitement.
    Si cela peut servir à d'autres, le fonctionnement est le suivant :
    Création d'une table pour les formules (tbl_formula) composée de 3 champs, un control_Name reprenant le nom du ctrl chkbox du formulaire, une formule à inclure au select (str_select) et une formule à inclure au group by (str_groupby)
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
        ' construit les clauses "Select" via strChamps et "group by" via strGroup
        Dim strChamps   As String
        Dim strGroup    As String
        Dim strselect   As String
        Dim strgroupby  As String
     
        Dim ctl As Control
     
        For Each ctl In Me.Controls
     
            Select Case Left(ctl.Name, 6)
                Case "chk_rg"
                    If ctl.Value = -1 Then
                        nbChoixRgpt = nbChoixRgpt + 1
                        strselect = CurrentProject.Connection.Execute("select str_select from tbl_formula where control_name = '" & ctl.Name & "'")!str_select
                        strgroupby = CurrentProject.Connection.Execute("select str_groupby from tbl_formula where control_name = '" & ctl.Name & "'")!str_groupby
     
                        If nbChoixRgpt = 1 Then
                            strChamps = strChamps & strselect
                            strGroup = strGroup & strgroupby
                        Else
                            strChamps = strChamps & ", " & strselect
                            strGroup = strGroup & ", " & strgroupby
                        End If
     
                    End If
     
                Case "chk_st"
                    If ctl.Value = -1 Then
                        nbChoixStat = nbChoixStat + 1
                        strselect = CurrentProject.Connection.Execute("select str_select from tbl_formula where control_name = '" & ctl.Name & "'")!str_select
     
                        If strselect <> "" Then
                            strChamps = strChamps & ", " & strselect
                        End If
                    End If
     
            End Select
     
        Next ctl
    Hope this helps

    @ bientôt

    TF
    Si l'homme a deux oreilles pour une bouche, c'est pour écouter deux fois plus qu'il ne parle...