472,789 Members | 1,330 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,789 software developers and data experts.

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 2045
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,534 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,534 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

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

Similar topics

by: CSDunn | last post by:
Hello, I have a format issue on an Access 2000 ADP report that I am going to attempt to explain from a 'ten thousand foot view' : I have an Access 2000 ADP report that has a SQL Server 2000...
by: Climber | last post by:
Hello, I want to now how to do a dynamic report with Crystal Report (C# .Net), the number of colum and row are different each time because they depend of my data base. Thanks Climber
by: Peter Herath | last post by:
I want to create a dynamic report using a crosstab query...... pls someone look into my attached example database and help me out to do the report generation.... example is like dis...: there...
by: zoro25 | last post by:
Hi, I want to create a dynamic report and for that I'm using a very simple Combo Box (only one item) and I want to use this filter on my report. Here's the code I came up with: Private Sub...
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
by: Sep410 | last post by:
Hi all, I have to create a dynamic report in vb.net. I never had done something like this before.Users want to see tables name and when they choose the table they will select which fields should...
by: J360 | last post by:
I'm using VB in Access 2003 to generate a dynamic report. I first open the report in design view to set all the grouping levels etc. I then use with rpt .printer.orientation =...
by: Kim Norgren | last post by:
I hope I have followed the posting guidelines; apologies from a neophyte if not, and please correct me. I'm self-taught in VB and have reached my limit, so I'm looking for help. I am dealing with...
by: Maya16 | last post by:
I have created one application in access using VBA. In this project I am creating Dynamic Report. I am able to create and open report in development environment i.e. when the project is in accdb...
by: johny6685 | last post by:
Hi All, Please advise how to set a dynamic report to be tabular automatically Also, help me to add groupings to a dynamic report via VBA code. I Used the below code, but the problem with...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.