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 |
