How To Create A MS Access Dynamic Report Based On A Dynamic Table

So in my other post, I demonstrated how to create a table dynamically with VBA.

Here’s the post
How To Create A Table In Access Using SQL And VBA

Now what if we need a report based on a table that is always changing, but is consistent in
its format?

Well we can send the data to Excel using VBA, or we can use an Access report and change the control source of the fields in the report to reflect the table’s new fields.

The report needed to stay in Access, but the field names would constantly be changing based on the months, and months don’t sort correctly (April, August … instead of January, February).

So the way I decided to handle it was to create a table in VBA, and then use vba to have ms access generate a dynamic report.

In the attached database I’m dynamically change my field name’s control source on the report.

Here is some helpful background… inspections are conducted on “SAA’s” (satellite accumulation units), and the data is logged in the database where a report can be generated based on data in the table (tblSAAInspections).

The goal is to produce the report called “rptSAAStatisticsTableQuestions”.

So here is a step by step:

1. We use this form to select the date for the report we want to create:

2. Here is the report based on the prior selection:

3. The table called “tblSAAQuestionStats_Report” serves as the record source for the report. Here’s what it looks like in it’s raw form:

Here is the code to get to this point of table creation (EVERYTHING BEFORE THE “If intCreateTable = True Then” LOGIC):


The task of populating the new table structure with data is accomplished using queries. I know that there are different ways of doing this, but I decided to keep it simple:

1. The query called “qrySAAInspectionsPerMonth”, is used to add 2 user defined functions which are in “basSAA” called “SAAFail” and ‘SAAFail_val”. This identifies SAA inspections that failed, and what question in “tblSAAInspectionQuestions” was the cause of its failure.

2. “qrySAAInspectionsPerMonth_xtab” limits our selection to just show the 12 months from the date selected on the form which is “Mar-2019”

The “12 Months Selection” is generated by this syntax:

So we just want the records that are failures and occurred in the past 12 months from the date selection.

3. “qrySAAInspectionsPerMonth_xtab2” – takes the query from step2, and puts it into a crosstab query format. I can not use this as the record source because the column headings can always change, and don’t sort correctly.

To get the “JEPRS Text” I use a domain function called “Dlookup”

Basically I want to know the question the inspection failed on, so it says “look in the [JEPRS Text] field of the [tblSAAInspectionQuestions] table for the [JEPRS Text] value where the ID is the same as the failed question”

I do this also for the ID.

4. Then “qrySAAInspectionsPerMonth_xtab3” appends the crosstab query to the correct fields in the table we created above.

Then we can view the newly generated report!

Since the table fields can change all the time, we need the report fields to by dynamic.

So we use VBA to produce dynamic report fields. Here’s is how simple it is:

When the report opens, set the control source automatically!

Use the Report_Open event as shown below:

That is let you control your report fields at run time.

You can download the database here:
MS Access Dynamic Report A Dynamic Table.accdb

Let me know if you have any questions.

dynamic table,MS Access Report ,create table dynamically with VBA,control source ,create table vba,dlookup
DoCmd.OpenReport.QueryDef, Parameters Collection,dynamic report fields,ms access dynamic reports

Free! Subscribe To Our YouTube Channel!

facebooktwittergoogle_plusredditpinterestlinkedinmailby feather
Tags: , , , , , , , , , , , ,


Call Erik Now