[AC-2000] generer un rapport avec VBA d'une crosstab query

[machipot]

Bonjour,

Avez une idée comment generer un rapport dans access avec VBA a partir d'ue query crosstab. je veux le faire avec vba, parceque le nombre de colonnes change a chaque fois.
merci.

[User]

Salut,

Tu peux le faire directement à partir d'une requête simple.

Il suffit de choisir l'assistant tableau croisée dynamique (Dans nouveau formulaire). Puis de baser ton tableau sur cette requête et enfin positionner tes champs respectivement en en-têtes de lignes, de colonnes et en valeur.

Tout ceci et bien plus encore est expliqué dans cette vidéo de Maxence Hubiche. C'est pour Access 2010, mais pas mal de chose se retrouve dans les versions antérieures.

L'avantage du tableau croisé dynamique est comme son nom l'indique de pouvoir afficher, entre autre, un nombre variable de colonnes.

A+

[machipot]

Salut Denis,

effectivement cette video est excellente pour un rapport dynamic qui a pour le nombre de colonnes fixe.
Dans mon cas le nombre de colonnes/lignes change, donc c'est la raison pour laquelle je dois le generer avec vba. je suis sur access 2000.
j'ai trouvé ce morceau de code, mais il n'affiche pas la totalité des colonnes, (pour les lignes c'est parfait)

Code :

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
 'Function CreateDynamicReport(strSql As String)
    Dim db As DAO.Database ' database object
    Dim rs As DAO.Recordset ' recordset object
    Dim fld As DAO.Field ' recordset field
    Dim txtNew As Access.TextBox ' textbox control
    Dim lblNew As Access.Label ' label control
    Dim rpt As Report ' hold report object
    Dim lngTop As Long ' holds top value of control position
    Dim lngLeft As Long ' holds left value of controls position
    Dim title As String 'holds title of report
 
         'set the title
         title = "Title for the Report"
 
         ' initialise position variables
         lngLeft = 0
         lngTop = 0
 
         'Create the report
         Set rpt = CreateReport
 
         ' set properties of the Report
         With rpt
             .Width = 8500
             .RecordSource = strSql
             .Caption = title
         End With
 
         ' Open SQL query as a recordset
         Set db = CurrentDb
         'Dim strSql As String
 
         Set rs = db.OpenRecordset("00000_NC_OS_OnStrength_1b")
 
         ' Create Label Title
         Set lblNew = CreateReportControl(rpt.name, acLabel, _
         acPageHeader, , "Title", 0, 0)
         lblNew.FontBold = True
         lblNew.fontSize = 12
         lblNew.SizeToFit
 
         ' Create corresponding label and text box controls for each field.
         For Each fld In rs.Fields
 
             ' Create new text box control and size to fit data.
             Set txtNew = CreateReportControl(rpt.name, acTextBox, _
             acDetail, , fld.name, lngLeft + 1500, lngTop)
             txtNew.SizeToFit
 
             ' Create new label control and size to fit data.
             Set lblNew = CreateReportControl(rpt.name, acLabel, acDetail, _
             txtNew.name, fld.name, lngLeft, lngTop, 1400, txtNew.Height)
             lblNew.SizeToFit
 
             ' Increment top value for next control
             lngTop = lngTop + txtNew.Height + 25
         Next
 
         ' Create datestamp in Footer
         Set lblNew = CreateReportControl(rpt.name, acLabel, _
         acPageFooter, , Now(), 0, 0)
 
         ' Create page numbering on footer
         Set txtNew = CreateReportControl(rpt.name, acTextBox, _
         acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
         txtNew.SizeToFit
 
         ' Open new report.
         DoCmd.OpenReport rpt.name, acViewPreview
 
         'reset all objects
         rs.Close
         Set rs = Nothing
         Set rpt = Nothing
         Set db = Nothing

[User]

Re,

effectivement cette video est excellente pour un rapport dynamic qui a pour le nombre de colonnes fixe.

Précisément ca prend en compte aussi un nombre de colonnes variables...

Peux-tu poster ta requête

[machipot]

que veux tu dire par ma requette?
tu veux dire la query!!!

[User]

Oui, peux tu poster son sql :

en mode création de la requête, tu fais affichage puis mode sql et tu copie le code dans ton message

[machipot]

Le nombre de colonnes qui est currentPosition peut varier suivant l'information selectionné..

le premier sql :

Code :

1
2
3
4
5
6
7
TRANSFORM Count([00000_NC_OS_OnStrength].EmployeeCode) AS Total
SELECT [00000_NC_OS_OnStrength].ProjectGroup_Desc AS [Program/Project]
FROM 00000_NC_OS_OnStrength
WHERE ((([00000_NC_OS_OnStrength].ProjectGroup_Desc)<>"Elections Canada"))
GROUP BY [00000_NC_OS_OnStrength].sortorder, [00000_NC_OS_OnStrength].ProjectGroup_Desc
ORDER BY [00000_NC_OS_OnStrength].sortorder DESC , [00000_NC_OS_OnStrength].currentposition
PIVOT [00000_NC_OS_OnStrength].currentposition;

la deuxieme query (utilisé dans la premiere)

Code :

1
2
3
4
SELECT [Agt Info Sheet 2].currentposition, [00000_NC_PL_ProjectGroup].sortorder, Employees.EmployeeCode, Employees.LastName, Employees.FirstName, [00000_NC_PL_ProjectGroup].ProjectGroup_Desc, [000_PL_EmployeeLeaveStatus].EmployeeLeaveStatusDesc, [Agt Info Sheet 1].DepartDate, [000_Agt_EmployeeLeaveStatus].ELS_StartDate, [000_Agt_EmployeeLeaveStatus].ELS_EndDate, [000_PL_EmployeeLeaveStatus].EmployeeLeaveStatusID, DateDiff("d",[ELS_StartDate],Now()) AS [Days From Start Date]
FROM (((Employees LEFT JOIN (000_PL_EmployeeLeaveStatus RIGHT JOIN 000_Agt_EmployeeLeaveStatus ON [000_PL_EmployeeLeaveStatus].EmployeeLeaveStatusID = [000_Agt_EmployeeLeaveStatus].EmployeeLeaveStatusID) ON Employees.EmployeeCode = [000_Agt_EmployeeLeaveStatus].EmployeeCode) LEFT JOIN [Agt Info Sheet 1] ON Employees.ID = [Agt Info Sheet 1].IDempl) LEFT JOIN 00000_NC_PL_ProjectGroup ON [Agt Info Sheet 1].CurrentProjectGroupID = [00000_NC_PL_ProjectGroup].ProjectGroup_ID) INNER JOIN [Agt Info Sheet 2] ON Employees.ID = [Agt Info Sheet 2].IDempl
WHERE ((([Agt Info Sheet 1].DepartDate) Is Null) AND (([000_Agt_EmployeeLeaveStatus].ELS_EndDate) Is Null) AND (([000_PL_EmployeeLeaveStatus].EmployeeLeaveStatusID)=1)) OR ((([Agt Info Sheet 1].DepartDate) Is Null) AND (([000_Agt_EmployeeLeaveStatus].ELS_EndDate) Is Null) AND (([000_PL_EmployeeLeaveStatus].EmployeeLeaveStatusID)=2) AND ((DateDiff("d",[ELS_StartDate],Now()))<14)) OR ((([Agt Info Sheet 1].DepartDate) Is Null) AND (([000_Agt_EmployeeLeaveStatus].ELS_EndDate) Is Null) AND (([000_PL_EmployeeLeaveStatus].EmployeeLeaveStatusID)=3) AND ((DateDiff("d",[ELS_StartDate],Now()))<14)) OR ((([Agt Info Sheet 1].DepartDate) Is Null) AND (([000_Agt_EmployeeLeaveStatus].ELS_EndDate) Is Null) AND (([000_PL_EmployeeLeaveStatus].EmployeeLeaveStatusID)=4) AND ((DateDiff("d",[ELS_StartDate],Now()))<14))
ORDER BY [000_Agt_EmployeeLeaveStatus].ELS_StartDate DESC;

[User]

Salut,

Je ne pensais pas que tes requêtes étaient si complexes

Peux-tu joindre ta base pour tester, je regarderai ça en fin de soirée

Regardes aussi du côté de cette discussion :
formulaire-etat-bases-requete-analyse-croisee

A+