Dynamic Report

[Integrating]

*** 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.

[nico5038]

Two possible problems I can think of:
1: the number of columns and fields on the report aren't enough (guess you need to have 17 fields
2: the fieldnames "RT Plate" and "RT Pipe" contain spaces, please use "RT_Plate" and "RT_Pipe" to avoid "space-trouble".
Nic;o)

[Integrating]

That is where I am stuck, where are the number of columns and fields set?

[nico5038]

Check the description:
To start, doing this you need to place the fields "coded" in the report.
The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
The "detail" fields should be called "Col1", "Col2", "Col3", etc.
So for 17 columns you'll need these coded fields/controls from e.g. lblCol1 till lblCol17, etc.

Nic:o)

[Integrating]

Thanks, from what I understand that goes into the Report_Open event but if the report is being created every time where do I put this part of the script and how is it called?

[nico5038]

The "lblCol1", "lblCol2", "lblCol3", etc. need to be placed in the detail section of the report. The code in the On_Open event of the report, thus when you activate the report it will run.

[Integrating]

I'm sorry, I am feeling like a complete idiot. The report does not exist until the createautoreport event is run. So I can't use an on_open event unless I put it in the createautoreport code? I have tried putting it into my current code but am simply generating more errors. Clearly I am way out of my league here.

[nico5038]

The create autoreport function is new for me. Guess that's causing the confusion.
Guess the best is to:
1: Create the autoreport
2: Change the autoreport to hold the fieldnames as specified in my previous comment.
3: Add the code for renaming the fields dynamically.
As I guess that the auto report uses the fields of the qrDummy to generate the fields, this should hold the needed number of fields.

[nico5038]

Have tried the autoreport. It's a replacement for the code with lblCol1, etc. and delivers a report with the columns based on the column names of the query "qryDummy".
Why didn't you create a crosstable query in the editor and named it "qryDummy" to pass in the function call as described ?

[Integrating]

I did use "qryDummy" but I changed the name to "Matrix"
I have finally had some time to work on your suggestions but from what I have come up with it looks like it won't suit my requirement. The main problem I am trying to fix is that the number of columns will continue to increase. If I place the columns in the report then eventually there won't be enough columns so I will still need to manually update the report design/code anyway.
Thank you for your help and patience :)

[nico5038]

Thank you for your response.
I did learn the alternative dynamic report listed here, as I created and used only the "fixed" col1, etc. approach myself.
Just one final warning, MS Access does have limits. Microsoft states a max of 250 fields in a table, but having long fieldnames I've seen tables with some 90 fields crash. So just for having a growing number of fields from the crosstable query, you're application will eventually crash.
Just try on forehand where that limit is, so you can warn the user and prevent a database dump :-)

[Integrating]

Great advise, thank you :)

[NeoPa]

After all that (Moving & merging.), I want to say what a pleasure it is to see you posting again Nico. It's been a long time since I remember seeing one of your posts.

Wow. I just checked and it's nearly five years!!

Welcome back, and I hope you & yours are all well and surviving the current situation as well as possible.

All the best -Ade.

[nico5038]

Hi Ade,

Indeed a very long time :-)
Got triggered by an email from this old thread and took me a while to find out that there has been added a generated report to do the same as my function with preparated report.

I've been retired two years ago and spend the majority of my time now on the "VeleHanden.nl" platform (something like TheCrowd). Especially on projects that are transcripting and tagging handwritten text that's processed with the opensource tool "Transcribus".
The chronicles project handles chronicles from 1500 till 1850 and is a great way to learn how people lived in those days.

Just let me know when there's some Access asistence needed :-)

Regards,
Nico

[NeoPa]

1500 to 1850? May favourite time of the afternoon :-D

Access is still going strong here. Your presence is always welcome but don't worry about questions going unanswered.

Seriously, I love finding out about history. Enjoy it :-)