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 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190
| Dim UDateNaissance As String
Dim UAge As String
Dim UGroupeAge As String
Dim ULangue As String
Dim USexe As String
Dim UTypeClient As String
Dim UAdresse As String
Dim UIntervenantPivot As String
Dim UDiscipline As String
Dim UMilieuService As String
Dim UIntervenantDossier As String
Dim CTitre As String
Dim CNomPrenom As String
Dim CAdresse As String
Dim CTypeLien As String
Dim CContactUrgence As String
Dim CResponsabilite As String
Dim CTelephone As String
Dim CIndicateurDeces As String
Dim CDateDebut As String
Dim CDateFin As String
Dim CIndicateurPostale As String
Dim Regroupement As String
Dim bds As Database, qdf As QueryDef
Dim chSQL As String
Dim Statut As String
Public Function baseSQL(requete As String)
If Me.cdrStatut.Value = 2 Then
Statut = "((tb_usagers_uniques.statut_dossier)= ""Inscrit"")"
End If
If Me.cdrStatut.Value = 3 Then
Statut = "((tb_usagers_uniques.statut_dossier)= ""Indéterm"" OR (tb_usagers_uniques.statut_dossier)= ""Att 1 sv"")"
End If
If Me.cdrStatut.Value = 1 Then
Statut = "((tb_usagers_uniques.statut_dossier)= ""Inscrit"" OR (tb_usagers_uniques.statut_dossier)= ""Att 1 sv"" OR (tb_usagers_uniques.statut_dossier)= ""Indéterm"")"
End If
If Me.chkUDateNaissance Then
UDateNaissance = ", tb_usagers_uniques.usager_date_naissance"
Else: UDateNaissance = ""
End If
If Me.chkUAge Then
UAge = ", tb_usagers_uniques.usager_age"
Else: UAge = ""
End If
If Me.chkUGroupeAge Then
UGroupeAge = ", tb_usagers_uniques.usager_groupe_age"
Else: UGroupeAge = ""
End If
If Me.chkULangue Then
ULangue = ", tb_usagers_uniques.usager_langue"
Else: ULangue = ""
End If
If Me.chkUSexe Then
USexe = ", tb_usagers_uniques.usager_sexe"
Else: USexe = ""
End If
If Me.chkUTypeClient Then
UTypeClient = ", tb_usagers_uniques.usager_type_clientele"
Else: UTypeClient = ""
End If
If Me.ChkUAdresse Then
UAdresse = ", tb_usagers_uniques.usager_adresse, tb_usagers_uniques.usager_case_postale, tb_usagers_uniques.usager_municipalite, tb_usagers_uniques.usager_province, tb_usagers_uniques.usager_code_postal"
Else: UAdresse = ""
End If
If Me.chkUIntervenantPivot Then
UIntervenantPivot = ", tb_usagers_uniques.usager_intervenant_pivot"
Else: UIntervenantPivot = ""
End If
If Me.chkUDiscipline Then
UDiscipline = ", tb_disciplines.discipline"
Else: UDiscipline = ""
End If
If Me.chkUMilieuService Then
UMilieuService = ", tb_milieu_service.milieu_service"
Else: UMilieuService = ""
End If
If Me.chkUIntervenantDossier Then
UIntervenantDossier = ", tb_intervenants.intervenant"
Else: UIntervenantDossier = ""
End If
If Me.chkCtitre Then
CTitre = ", tb_personne_lien.titre"
Else: CTitre = ""
End If
If Me.chkCNomPrenom Then
CNomPrenom = ", tb_personne_lien.nom_personne_lien, tb_personne_lien.prenom_personne_lien"
Else: CNomPrenom = ""
End If
If Me.chkCAdresse Then
CAdresse = ", tb_personne_lien.adresse_principale, tb_personne_lien.case_postale, tb_personne_lien.municipalite, tb_personne_lien.province, tb_personne_lien.code_postal"
Else: CAdresse = ""
End If
If Me.chkCTypeLien Then
CTypeLien = ", tb_personne_lien.type_de_lien"
Else: CTypeLien = ""
End If
If Me.chkCContactUrgence Then
CContactUrgence = ", tb_personne_lien.contact_urgence"
Else: CContactUrgence = ""
End If
If Me.chkCResponsabilite Then
CResponsabilite = ", tb_personne_lien.responsabilite_autre"
Else: CResponsabilite = ""
End If
If Me.chkCTelephone Then
CTelephone = ", tb_personne_lien.telephone_domicile, tb_personne_lien.telephone_travail"
Else: CTelephone = ""
End If
If Me.chkCIndicateurdeces Then
CIndicateurDeces = ", tb_personne_lien.indicateur_deces"
Else: CIndicateurDeces = ""
End If
If Me.chkCDateDebut Then
CDateDebut = ", tb_personne_lien.date_debut"
Else: CDateDebut = ""
End If
If Me.chkCDateFin Then
CDateFin = ", tb_personne_lien.date_fin"
Else: CDateFin = ""
End If
If Me.chkCIndicateurPostale Then
CIndicateurPostale = ", tb_personne_lien.code_envoi_postal_adresse_principale"
Else: CIndicateurPostale = ""
End If
Regroupement = UDateNaissance & UAge & UGroupeAge & ULangue & USexe & UTypeClient & UAdresse & UIntervenantPivot & UDiscipline & UMilieuService & UIntervenantDossier & CTitre & CNomPrenom & CAdresse & CTypeLien & CContactUrgence & CResponsabiliteAutre & CTelephone & CIndicateurDeces & CDateDebut & CDateFin & CIndicateurPostale
'Activation de la base de donnée en cours
Set bds = CurrentDb
bds.QueryDefs.Refresh
' Si la requête NouvelleRequête existe, la supprime.
For Each qdf In bds.QueryDefs
If qdf.Name = "Test10" Then
bds.QueryDefs.Delete qdf.Name
End If
Next qdf
chSQL = requete
Debug.Print (requete)
' Crée un objet QueryDef.
Set qdf = bds.CreateQueryDef("Test10", chSQL)
Set bds = Nothing
End Function
Private Sub txt_A03_DblClick(Cancel As Integer)
chSQL = "SELECT tb_usagers_uniques.no_dossier, tb_usagers_uniques.usager_nom_prenom, tb_usagers_uniques.statut_dossier, tb_prog_serv.programme_service, tb_prog_serv.unite_adm_3 " & Regroupement & " " _
& "FROM (((((tb_usagers_uniques LEFT JOIN tb_prog_serv ON tb_usagers_uniques.no_dossier = tb_prog_serv.no_dossier) LEFT JOIN tb_ressources ON tb_usagers_uniques.no_dossier = tb_ressources.no_dossier) " _
& "LEFT JOIN tb_personne_lien ON tb_usagers_uniques.no_dossier = tb_personne_lien.no_dossier) LEFT JOIN tb_milieu_service ON tb_usagers_uniques.no_dossier = tb_milieu_service.no_dossier) " _
& "LEFT JOIN tb_intervenants ON tb_usagers_uniques.no_dossier = tb_intervenants.no_dossier) LEFT JOIN tb_disciplines ON tb_usagers_uniques.no_dossier = tb_disciplines.no_dossier " _
& "GROUP BY tb_usagers_uniques.no_dossier, tb_usagers_uniques.usager_nom_prenom, tb_usagers_uniques.statut_dossier, " _
& "tb_prog_serv.programme_service, tb_prog_serv.unite_adm_3 " & Regroupement & " " _
& "HAVING ( '" & Statut & "' AND ((tb_prog_serv.programme_service)=""Adapt / Réad DI"") AND ((tb_prog_serv.unite_adm_3)=""Adap_Réad Est 0-6 ans""));"
Call baseSQL(chSQL)
End Sub |
Partager