Dynamic Report

Introduction.

Designing reports in Microsoft Access is quite straightforward, as we already know. Once a process is in place—using macros or programs to prepare the source data for a standard report—the rest becomes routine. You simply design the report and add it to the report list. After that, every time a user runs the report, it’s ready for preview or printing.

Although the underlying data may change based on parameters such as the reporting period, the report structure itself remains unchanged. There is no need to redesign the report or modify its data source, since all standard elements—such as the main heading, reporting period, preparation date, and page numbers—are already incorporated during the initial design phase.

We are now going to explore a type of report that cannot be planned using the usual rules or based on a fixed data structure. In this case, the structure of the source data is unpredictable—field names may change every time the report is generated. When the data structure is dynamic, you cannot permanently place field names in the report design, as is typically done.

To make matters more challenging, the number of fields included in the report may also vary. An even more complex requirement is calculating summary values—both at the group level and as grand totals in the report footer—when the structure and content of the data are constantly changing.

When someone asks for something difficult, the easy way out is to say it's not possible—and many users might accept that, especially if they’re unfamiliar with programming. But in the software world, when someone says “no,” it often just means they don’t yet know how to do it. I’ve been there myself. Still, once I say it can’t be done, the thought nags at me. I start asking: Can I really let it go, or is there a way to make it work?

Often, it just takes time—time to think, plan, organize, test a few ideas, and gradually build something functional. The number of steps doesn’t matter; what matters is the final result. Once it works, I can always go back and refine it. That said, there are situations where we must honestly accept the limits and stick to “no”—but only after we’ve explored every possibility.

Let’s get down to problem-solving instead of going in circles. I was just being a little philosophical earlier. Honestly, the task isn’t as big as it might sound now, especially after all that bragging.

Report Preparation.

Before diving into the Report preparation, let me first show you some sample data and the Report format we’ll be working with. The sample records are taken from two familiar tables in the Northwind.mdb database—Employees and Orders, which we’ve already used in earlier examples.

In the above table, the Shipped Date ranges from July 10, 1996, to May 6, 1998. Our task is to prepare a report formatted as shown below, organized Employee-wise, Year-wise, and Month-wise.

sample data table view

Click to Enlarge

When a sample Report is designed using the above data as a source, it looks like the image given below.

Click to Enlarge

The report is designed to display data for 12 months, with the data selection criteria set between 199607 (July 1996) and 199706 (June 1997) in the report’s underlying query. The Detail section includes the necessary fields, while the Report Footer contains controls configured with summary formulas to calculate month-wise totals. The field headings are labeled using month-year formats for clarity. Up to this point, everything functions smoothly — the report opens in Print Preview or prints without any issues.

Report Data Selection Criteria

However, when the data selection criteria are changed to target a different period, the report fails to run. It throws an error at the first field that does not match any of the existing TextBox control sources on the report layout and refuses to open in Print Preview or Print mode. This occurs because the field names in the source query no longer align with those hard-coded in the report design, making it unsuitable for handling dynamic or varying data structures.

An alternative approach is to create a Report Table with fixed field names such as M1, M2, through M12, representing data for January to December. An Append Query can be used to populate this table with the desired report data. The report itself can then be designed using these permanent field names, ensuring a consistent structure.

To dynamically reflect the reporting period in the field headers, we need a way to define header labels using formulas based on the report period parameters. However, this method introduces a constraint: the user must generate report data within a single calendar year—either for the full 12 months or a shorter period—without spanning across multiple years. This limitation ensures that the field mappings remain consistent and avoids mismatches during report generation.

If the user attempts a trial run with a date range that crosses over a calendar year, the data for the earlier months will appear at the right end of the report, while the data for the later months will be placed at the beginning. This reversal disrupts the logical flow of the report. Additionally, dynamically setting the field headings to correctly reflect the shifted months becomes another challenge to manage.

In the end, we cannot consider this a well-structured report—or to put it plainly, it is not user-friendly. The lack of alignment between the data layout and the user's expectations diminishes the overall usability of the report.

Sample Data Preparation Queries.

We found a practical remedy for this issue by introducing a small VBA routine in the report’s code module. This routine dynamically adjusts the final layout of the report based on the structure of the underlying source data. It is executed automatically each time the report is opened for preview or print, ensuring the design always aligns with the current dataset, regardless of how the fields or reporting period vary.

1. To get started with the program, import the Employees and Orders tables from the Northwind.mdb sample database, if they are not already available in your project. If you're unsure about the location of the Northwind database, refer to the Saving Data on Forms, Not in the Table page for detailed guidance on where to find it.

2. Copy and paste the following SQL string into the SQL View of a new query, and save the query with the name indicated below:

   Query Name: FreightValueQ0
   
   SELECT [FirstName] & " " & [LastName] AS EmpName,
    Val(Format([ShippedDate],"yyyymm")) AS yyyymm,
    Orders.Freight
   FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
   WHERE (((Val(Format([ShippedDate],"yyyymm"))) Between 199607 And 199706));
   
   Query Name : FreightV_CrossQ
   TRANSFORM Sum(FreightValueQ0.Freight) AS SumOfFreight
   SELECT FreightValueQ0.EmpName
   FROM FreightValueQ0
   WHERE (((FreightValueQ0.yyyymm)<>""))
   GROUP BY FreightValueQ0.EmpName
   PIVOT FreightValueQ0.yyyymm;

   Report Design Task

3. After creating the above queries one by one, design a Report based on the FreightV_CrossQ query as its data source. A sample layout of the Report design is shown below.

   

   Click to Enlarge

4. In the Detail section of the Report, create 13 Text Box controls. Make the leftmost Text Box wider to accommodate the Employee’s Name, and use the remaining 12 boxes for monthly data (Month 1 to Month 12). Rename the Text Boxes using the Name property as follows: M00 for the Employee Name field, and M01 through M12 For the monthly fields, moving left to right. Ensure that the Control Source property of all these Text Boxes remains empty at this stage.

5. In the Report Footer Section, create 13 TextBox controls and name them from T00, T01 to T12. Leave the Control Source Property empty.

6. In the Header Section, create 13 Label Controls, name them L00, L01 to L12, and leave the Caption Property empty.

7. Create a Label at the Top and set the Caption property value to EMPLOYEE-WISE FREIGHT VALUE LISTING.

8. While the Report is still in design view, select the Save option from the File menu and save the Report with the name FreightVal_Rpt.

9. Select Code from the View menu to display the VBA Module of the Report.

10. Copy and Paste the following Code into the VBA Module:

    Automating Report Control's Data Reference Setting.

    Private Sub Report_Open(Cancel As Integer)
    Dim db As Database, Qrydef As QueryDef, fldcount As Integer
    Dim rpt As Report, j As Integer, k As Integer
    Dim fldname As String, ctrl As Control, dtsrl As Date
    Dim strlbl As String, fsort() As String
    
    On Error GoTo Report_Open_Err
    
    Set db = CurrentDb
    Set Qrydef = db.QueryDefs("FreightV_CrossQ")
    fldcount = Qrydef.Fields.Count - 1
    
    If fldcount > 12 Then
       MsgBox "Report Period exceeding 12 months will not appear on the Report."
       fldcount = 12
    End If
    
    Set rpt = Me
    
    ReDim fsort(0 To fldcount) As String
    For j = 0 To fldcount
        fldname = Qrydef.Fields(j).Name
        fsort(j) = fldname
    Next
    
    'Sort Field names in Ascending Order
    For j = 1 To fldcount - 1
        For k = j + 1 To fldcount
            If fsort(k) < fsort(j) Then
                fsort(j) = fsort(k)
            End If
        Next
    Next
    
    For j = 0 To fldcount
    'Monthwise Data    
    Set ctrl = rpt.Controls("M" & Format(j, "00"))
        ctrl.ControlSource = fsort(j)
        Set ctrl = rpt.Controls("T" & Format(j, "00"))
        If j = 0 Then
            ctrl.ControlSource = "=" & Chr$(34) & " TOTAL = " & Chr$(34)
        Else
            ctrl.ControlSource = "=SUM([" & fsort(j) & "])"
        End If
    
    'Header labels
    If j = 0 Then
        Me("L" & Format(j, "00")).Caption = "Employee Name"
    Else
        dtsrl = DateSerial(Mid(fsort(j), 1, 4), Right(fsort(j), 2), 1)
        strlbl = Format(dtsrl, "mmm-yy")
        Me("L" & Format(j, "00")).Caption = strlbl
    End If
    Next
    
    Report_Open_Exit:
    Exit Sub
    
    Report_Open_Err:
    MsgBox Err.Description, , "Report_0pen()"
    Resume Report_Open_Exit
    End Sub

11. After copying the code, minimize the VBA Editor window and open the Property Sheet of the Report. Check whether the On Open property is set to [Event Procedure]. If it is not, the code has not been correctly linked to the event. In that case, select [Event Procedure] from the drop-down list, then click the Build button (...) to open the code window.

    In the code module that appears, you will see an empty Report_Open() subroutine. Cut the code you previously pasted (excluding the top and bottom lines that were copied from the web page), and insert it within this Report_Open() ... End Sub block. Finally, remove any orphaned or duplicate lines, save the changes, and close the editor.

    Print Previewing the Report.

12. Open the Report in Print Preview. The Report should display the values from the source Query, with the correct heading labels and the Report Footer summary calculated accurately.

13. Open the first Query in Design View and modify the criteria to set different date ranges, ensuring that the selected range does not exceed 12 months (a shorter period is acceptable). Then, run the Report to verify the output.

If the selected period exceeds 12 months, the Report will display a message indicating that the selection exceeds the allowed range. It will then proceed to open with the data that fits within the maximum number of available fields.

If the selected period is less than 12 months, the unused rightmost controls will remain blank. In either case, the Report will still open, allowing the user to view the available contents.

Download Demo Database.

Download Demo Database

• Highlighting Reports
• Saving Data on Forms Not in a Table
• Edit Data in a Zoomed Control - Access 2003
• Custom Menus and Tool Bars-2 - Access 2003
• Custom Menus and Tool Bars -Access 2003