470,814 Members | 868 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,814 developers. It's quick & easy.

Dynamic Report

6 Nibble
*** 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?
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Public Sub CreateAutoReport(strSQL As String)
  5. Dim rpt         As Access.Report
  6. Dim rptReport As Access.Report
  7. Dim strCaption As String
  8. Dim lblReport   As Access.Label
  9. Dim txtReport   As Access.TextBox
  10. Dim ctrl                As Control
  11. Dim TextCol             As Boolean
  12. Dim TextWidth           As Integer
  13. Dim ColWidth            As Integer
  14. Dim FirstCol            As Boolean
  15. Dim FieldName           As Field
  16. Dim RS                  As Recordset
  18.     ColWidth = 0
  19.     TextWidth = 0
  20.     TextCol = True
  21.     FirstCol = True
  23.      CurrentDb.QueryDefs("Matrix").sql = strSQL
  25.      ' Open dummy query to invoke NewObjectAutoReport command on it
  26.      ' Put the report created to design view to make properties editable
  27.      With DoCmd
  28.          .OpenQuery "Matrix", acViewNormal
  29.          .RunCommand acCmdNewObjectAutoReport
  30.          .Close acQuery, "Matrix"
  31.          .RunCommand acCmdDesignView
  32.      End With
  34.      ' Get reference to just created report
  35.      For Each rpt In Reports
  36.          If rpt.RecordSource = "Matrix" Then Set rptReport = rpt
  37.      Next
  39.     'set printer stuff
  40.     rptReport.Printer.BottomMargin = 360
  41.     rptReport.Printer.LeftMargin = 360
  42.     rptReport.Printer.RightMargin = 360
  43.     rptReport.Printer.TopMargin = 360
  44.     rptReport.Printer.Orientation = acPRORLandscape
  46.      For Each ctrl In rptReport.Controls
  47. '
  48.         Select Case ctrl.ControlType
  49.             Case acTextBox
  50.                 If ctrl.Section = 0 Then
  51.                     ctrl.FontWeight = 400
  52.                     ctrl.FontSize = 9
  53.                     ctrl.Height = 350
  54.                     ctrl.Width = 1800
  55.                     ctrl.Top = 400
  56.                 End If
  58.             Case acLabel
  59.                 If ctrl.Section = 0 Then
  60.                     ctrl.FontSize = 9
  61.                     ctrl.FontWeight = 400
  62.                     ctrl.Height = 350
  63.                     ctrl.Width = 1800
  64.                 End If
  65.         End Select
  67.     Next ctrl
  70.      DoCmd.RunCommand acCmdReportView
  72.      Set rptReport = Nothing
  73.      Set rpt = Nothing
  75. End Sub
My query - Matrix is this:
Expand|Select|Wrap|Line Numbers
  1. CreateAutoReport "TRANSFORM Max([CTrainingLevel]) & Chr(13) & Chr(10) & Max([CSignatory]) & Chr(13) & Chr(10) & Max([CSigAuthDate]) & Chr(13) & Chr(10) & First([AutorisedBy]) AS TheValue " & _
  2.     "SELECT qry_IB_Competency_Data.Name AS Name, " & _
  3.     "qry_IB_Competency_Data.Position, qry_IB_Competency_Data.StartDate, " & _
  4.     "'Training Level' & Chr(13) & Chr(10) & 'Signatory' & Chr(13) & Chr(10) & 'Authorisation Date' & Chr(13) & Chr(10) & 'Authorised By' AS Data " & _
  5.     "From qry_IB_Competency_Data " & _
  6.     "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' " & _
  7.     "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.
Jan 25 '21 #1
14 1523
3,080 Expert 2GB
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".
Jan 25 '21 #2
6 Nibble
That is where I am stuck, where are the number of columns and fields set?
Jan 25 '21 #3
3,080 Expert 2GB
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.

Jan 25 '21 #4
6 Nibble
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?
Jan 31 '21 #5
3,080 Expert 2GB
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.
Jan 31 '21 #6
6 Nibble
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.
Jan 31 '21 #7
3,080 Expert 2GB
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.
Feb 1 '21 #8
3,080 Expert 2GB
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 ?
Feb 1 '21 #9
6 Nibble
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 :)
Feb 8 '21 #10
3,080 Expert 2GB
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 :-)
Feb 8 '21 #11
6 Nibble
Great advise, thank you :)
Feb 8 '21 #12
32,311 Expert Mod 16PB
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.
Feb 13 '21 #13
3,080 Expert 2GB
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 :-)

Feb 13 '21 #14
32,311 Expert Mod 16PB
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 :-)
Feb 13 '21 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by CSDunn | last post: by
3 posts views Thread by Climber | last post: by
2 posts views Thread by zoro25 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.