Create dynamic reports

[yamus]

Hello
I want to know how to create dynamic reports from scratch using VBA
I want to have absolute control from VBA code(Creating header and footer, formatting fields, etc...)
Thanks in advance

 

[MajP]

Adding / deleting reports and controls at run time is a HORRIBLE idea, if that is what you are asking. Modifying properties is not problem. What is the question?

 

[Ranman256]

you don't need vba.
create your report, put it in a table for users to choose.
use a form to filter the data or show all data.

 

[Pat Hartman]

Think about it this way - Access is a RAD (Rapid Application Development) tool and includes excellent Report Creating features. You are asking to reproduce them using VBA which is crippled in that it works with objects rather than bits.

Users cannot be allowed to create their own reports by modifying the FE. Allowing that will make your job a nightmare if you want to release a new version so we just don't do it. PERIOD. Giving them a poor substitute that saves designs to a common table isn't very satisfying either.

What is the purpose of doing this? Usually, I create a bunch of reports for clients and then I create a couple of different data exports. That way, if they need something one-off or quicker than I can get to it, they can use the exports to export the data to Excel where they are comfortable making data look nice. The exports are fixed as far as columns go, although making them variable wouldn't be too hard. The criteria is variable and I build the SQL string based on what criteria they choose.

 

[yamus]

Adding / deleting reports and controls at run time is a HORRIBLE idea, if that is what you are asking. Modifying properties is not problem. What is the question?

Create report programatically from scratch using VBA

 

[MajP]

Create report programatically from scratch using VBA

Yes as I said, stupid idea. What is the question? Can it be done? Yeah but will bloat your db and then likely corrupt it. Can you explain more than just one line?

application.create"object"

 

[yamus]

Yes as I said, stupid idea. What is the question? Can it be done? Yeah but will bloat your db and then likely corrupt it. Can you explain more than just one line?

application.create"object"

I have a recordset with multiple fields on which i want to build my report
The reason why i want to do it programmatically is because i want to split the table created in the report into multiple tables.

 

[MajP]

The reason why i want to do it programmatically is because i want to split the table created in the report into multiple tables.

Not sure what that means, but for sure you do not have to make a dynamic report to get what you need. If be dynamic you mean create objects that do not exist.

 

[jdraw]

As others have asked: Why are you trying to do this with vba? What is the "business rationale" for such an exercise.

You could review this material from Martin Green re Dynamic Reports.

 

[MajP]

Just to be clear, need to be careful on semantics. The link @jdraw posted uses the term "dynamic" to mean a simple filter and modified properties. As stated that is a common thing to do and very manageable. I think you are trying to use the term to mean "create" something that does not exist. That is technically doable, but not advised if your plan is to do this at runtime.
Bottom line you do not need to create a report or add / delete report objects. You just need to modify its properties at runtime.

 

[yamus]

I think i did not explain my purpose clearly and that's why i see most of you confused.
I have a saved query "qrySearch" with 4 parameters: Department, EquipementType, Brand, Model
I run search based on this query to get a (1) recordset to work with.
When it comes to report. I drag the query from the navigation Pane and drop it into the report.
As a result, I get a sub-report on the report.
Every things work fine. I get a report that displays All equipements in all departments
What i want to do is to display the list of equipements for each department indivdually. All in the saame report
My aim is to view multiple output tables on the same report. It's sort of splitting the recordset

 

[MajP]

That is simply done using "sorting and grouping". Group on Department. However with sorting and grouping this likely can be done without the subreport.

Create a grouped or summary report - Microsoft Support

Create a report in Access that groups data and displays subtotals, averages, and other aggregates.

support.microsoft.com