*** Admin Edit ***
This message was added to an existing article (
Create Dynamic Report using VBA) which is not allowed. I've moved it for you but please remember next time to create your own thread for your questions. It's perfectly acceptable to post links to existing questions or articles - but posting your question within another thread is never acceptable.
Original Question
I have used the above scripts and put together a script that creates my crosstab report dynamically which is awesome but I can only get a maximum of 15 columns. No doubt I have missed some important piece of code but after all my hours of researching I can't quite figure out where I have gone wrong (except that I am not advanced and trying to piece together code...) At this stage my query requires 18 columns but that can increase. My SQL is at the bottom of this query.
Also, is it possible to apply conditional formatting within the code?
- Option Compare Database
-
Option Explicit
-
-
Public Sub CreateAutoReport(strSQL As String)
-
Dim rpt As Access.Report
-
Dim rptReport As Access.Report
-
Dim strCaption As String
-
Dim lblReport As Access.Label
-
Dim txtReport As Access.TextBox
-
Dim ctrl As Control
-
Dim TextCol As Boolean
-
Dim TextWidth As Integer
-
Dim ColWidth As Integer
-
Dim FirstCol As Boolean
-
Dim FieldName As Field
-
Dim RS As Recordset
-
-
ColWidth = 0
-
TextWidth = 0
-
TextCol = True
-
FirstCol = True
-
-
CurrentDb.QueryDefs("Matrix").sql = strSQL
-
-
' Open dummy query to invoke NewObjectAutoReport command on it
-
' Put the report created to design view to make properties editable
-
With DoCmd
-
.OpenQuery "Matrix", acViewNormal
-
.RunCommand acCmdNewObjectAutoReport
-
.Close acQuery, "Matrix"
-
.RunCommand acCmdDesignView
-
End With
-
-
' Get reference to just created report
-
For Each rpt In Reports
-
If rpt.RecordSource = "Matrix" Then Set rptReport = rpt
-
Next
-
-
'set printer stuff
-
rptReport.Printer.BottomMargin = 360
-
rptReport.Printer.LeftMargin = 360
-
rptReport.Printer.RightMargin = 360
-
rptReport.Printer.TopMargin = 360
-
rptReport.Printer.Orientation = acPRORLandscape
-
-
For Each ctrl In rptReport.Controls
-
'
-
Select Case ctrl.ControlType
-
Case acTextBox
-
If ctrl.Section = 0 Then
-
ctrl.FontWeight = 400
-
ctrl.FontSize = 9
-
ctrl.Height = 350
-
ctrl.Width = 1800
-
ctrl.Top = 400
-
End If
-
-
Case acLabel
-
If ctrl.Section = 0 Then
-
ctrl.FontSize = 9
-
ctrl.FontWeight = 400
-
ctrl.Height = 350
-
ctrl.Width = 1800
-
End If
-
End Select
-
-
Next ctrl
-
-
-
DoCmd.RunCommand acCmdReportView
-
-
Set rptReport = Nothing
-
Set rpt = Nothing
-
-
End Sub
My query - Matrix is this:
- CreateAutoReport "TRANSFORM Max([CTrainingLevel]) & Chr(13) & Chr(10) & Max([CSignatory]) & Chr(13) & Chr(10) & Max([CSigAuthDate]) & Chr(13) & Chr(10) & First([AutorisedBy]) AS TheValue " & _
-
"SELECT qry_IB_Competency_Data.Name AS Name, " & _
-
"qry_IB_Competency_Data.Position, qry_IB_Competency_Data.StartDate, " & _
-
"'Training Level' & Chr(13) & Chr(10) & 'Signatory' & Chr(13) & Chr(10) & 'Authorisation Date' & Chr(13) & Chr(10) & 'Authorised By' AS Data " & _
-
"From qry_IB_Competency_Data " & _
-
"GROUP BY qry_IB_Competency_Data.Name, qry_IB_Competency_Data.Position, qry_IB_Competency_Data.StartDate, 'Training Level' & Chr(13) & Chr(10) & 'Signatory' & Chr(13) & Chr(10) & 'Authorisation Date' & Chr(13) & Chr(10) & 'Authorised By' " & _
-
"PIVOT qry_IB_Competency_Data.Discipline; "
There should be two more columns at the end for RT Plate and RT Pipe. At first I thought it was an issue of fitting on the page but have played around with the sizes but it didn't bring in the missing columns. I am unable to attach a copy of the report as it stands.
Any help/guidance is much appreciated.
