Bonjour à tous,
J'ai Un formulaire utilisant 2 groupes d'option (fraDep et FrSex).
Dans chaque afterupdate des propriétés de ces groupes d'option j'ai une query qui met a jour un graphique chacun mais FraDep dépend de la valeur fraSEx.
En bref dans FraDep je voudrais executer la query se trouvant dans fraSex.
Ai essayé CurrentDb.Execute(strSQLSex et Docmd.runSQL mais me donne un msg d'erreur)
Voici 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 Private Sub cmdCategories_AfterUpdate() Dim strSQL As String Dim strCategory As String strCategory = Me!CmdCategories.Value strSQL = "TRANSFORM Sum(qryMain.NbrOfDay) AS SommeDeNbrOfDay" strSQL = strSQL & vbCrLf & "SELECT Format([Date_Leave],'mmm'&' yy') AS Mois" strSQL = strSQL & vbCrLf & "FROM qryMain" strSQL = strSQL & vbCrLf & "WHERE ((([Leave])=" & Chr(34) & strCategory & Chr(34) & "))" strSQL = strSQL & vbCrLf & "GROUP BY Format([Date_Leave],'mmm'&' yy'),(Year([Date_Leave])*12+Month([Date_Leave])-1)" strSQL = strSQL & vbCrLf & "PIVOT qryMain.DEPTitel;" Me!FraSex = 1 'Me!fraDep = 1 Debug.Print strSQL 'Affectation de la nouvelle source et mise à jour de graphique GraphLeave.RowSource = strSQL GraphLeave.ChartTitle.Text = "Leave " & strCategory & " by Departement" GraphLeave.Requery End Sub
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 Private Sub cmdClose_Click() DoCmd.Close acForm, Me.Name End Sub
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
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 Private Sub fraDep_AfterUpdate() Dim strDep As String Dim strSQLSex As String Dim strCategory As String Dim strSex_fraDep As String Dim libelleSexe As String Dim strSex As String Dim libelleDep As String strCategory = Me!CmdCategories.Value 'Dim Where_Group As String strSex = Me!FraSex.Value Debug.Print "strSex_Before :"; strSex If FraSex.Value = 1 Then strSex_fraDep = "< 3" libelleSexe = "All" ElseIf FraSex.Value = 2 Then strSex_fraDep = "= 0" libelleSexe = "Man" Else strSex_fraDep = "= 1" libelleSexe = "Woman" End If Debug.Print "libelleSex 0:"; libelleSexe Select Case fraDep.Value Case 1 strDep = "'*'" libelleDep = "All" ' GraphSex.RowSource = strSQLSex Case 2 strDep = "'CEO'" libelleDep = "CEO" Case 3 strDep = "'DGA'" libelleDep = "DGA" Case 4 strDep = "'DGO'" libelleDep = "DGO" Case 5 strDep = "'DGS'" libelleDep = "DGS" Case Else strDep = "'Other'" libelleDep = "Other" End Select strSQLSex = "TRANSFORM Sum(qryMain.NbrOfDay) AS SommeDeNbrOfDay" strSQLSex = strSQLSex & vbCrLf & "SELECT qryMain.DEPTitel" strSQLSex = strSQLSex & vbCrLf & "FROM qryMain" strSQLSex = strSQLSex & vbCrLf & "WHERE ((([Leave])=" & Chr(34) & strCategory & Chr(34) & ") and ([Gender] " & strSex_fraDep & ") and ([DepTitel] like " & strDep & "))" strSQLSex = strSQLSex & vbCrLf & "GROUP BY qryMain.DEPTitel" strSQLSex = strSQLSex & vbCrLf & "PIVOT qryMain.JourLitt;" GraphSex.ChartTitle.Text = "Nbr of Days by weekday for " & libelleSexe & " membership From following Department :" Debug.Print "===> :"; strSQLSex Debug.Print "libelleSex :"; libelleSexe Debug.Print "fraSex 0:"; FraSex Debug.Print "strCategory 2:"; strCategory Debug.Print "strSex 1:"; strSex_fraDep Debug.Print "libelleDep 1:"; libelleDep GraphSex.RowSource = strSQLSex GraphSex.Requery End Sub
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
43 Private Sub FraSex_AfterUpdate() Dim strSQL As String Dim strSex As String Dim libelleSexe As String Dim strSQLSex As String Dim strCategory As String strCategory = Me!CmdCategories.Value 'Debug.Print "strSQLSex : :"; strSQLSex Select Case FraSex Case 1 ' Gender Male strSex = "< 2" libelleSexe = "All" Case 2 'Gender Female strSex = "= 0" libelleSexe = "Man" Case 3 'All strSex = "= 1" libelleSexe = "Woman" End Select ' DoCmd.RunSQL (strSQLSex) GraphSex.Requery Debug.Print "strCategory :"; strCategory strSQL = "TRANSFORM Sum(qryMain.NbrOfDay) AS SommeDeNbrOfDay" strSQL = strSQL & vbCrLf & "SELECT Format([Date_Leave],'mmm'&' yy') AS Mois" strSQL = strSQL & vbCrLf & "FROM qryMain" strSQL = strSQL & vbCrLf & "WHERE ((([Leave])=" & Chr(34) & strCategory & Chr(34) & ") and ([Gender]" & strSex & "))" strSQL = strSQL & vbCrLf & "GROUP BY Format([Date_Leave],'mmm'&' yy'),(Year([Date_Leave])*12+Month([Date_Leave])-1)" strSQL = strSQL & vbCrLf & "PIVOT qryMain.DEPTitel;" Debug.Print "strCategory 2:"; strCategory Debug.Print "strSex :"; strSex GraphLeave.RowSource = strSQL GraphLeave.Requery End Sub
Partager