Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

Dynamic Report

Report designing in MS-Access is very easy as we already know. Once we setup a procedure to run the macros/programs to prepare the data for a standard Report, all we need to do is to design the Report and add it to the Report List. Every time the User runs the report it is ready to Preview or Print. The Source data may change, based on the report period setting, but there will not be any change in the structure of the Report or source data or need any change on the Report design itself as all the standard labels, like main heading, the period indicator, Report prepared date, Page Numbers etc. are already taken care as part of the designing task.

We are going to discuss a Report that is not so easy to plan on the above rules or based on a fixed source data structure. The structure of the source data is not predictable or shall we say field names of the source data can change every time we run this Report. If the data structure changes every time then you cannot put the field names permanently on the report design as we normally do. Adding to the difficulty the number of fields for the report also may change.

When someone asks for something difficult we can easily say it is not possible and the user may swallow it too, provided if he/she doesn't know computer programming at all. In the Software world when someone says no to something, it simply means that he doesn't know how to do it, at least for the time being. I fall in this category too. But, then after saying that it keeps me disturbed and starts asking myself, can I write it off just like that or find a way somehow to do it. It may take a while to have a plan, get things organized to do it. But, there are times that we have to stick to the No answer too.

Let us get into the problem solving instead of beating around the bush. I was trying to be little philosophical. It is not that big as you are thinking by now, after listening to my bragging.

Before getting into the Report preparation part, first I will show you some sample lines of data and the Report Format that we need to deal with. The sample data is taken from two familiar Tables from the Northwind.mdb sample database; Employees and Orders, which we have already used in earlier examples.

In the above table, the Shipped Date range is from July 10, 1996 to May 6, 1998. Our task is to prepare a Report from the following Employee-wise, Year-wise, Month-wise formatted data shown below:

sample data table view

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

The Report contents should be for a period of 12 months and data selection criterion is set for the period Between 199607 and 199706 in the data selection Query of the Report. In the Details Section, the report fields are inserted. Report Footer controls are set with the Summary formula taking month-wise Totals. Field Heading Controls are defined with month-Year labels. So far so good, we can open the report in Preview or Print, no issues.

But, when the data selection criteria changes for a different period the Report will not run. It will show error on the first field that do not match with the fields already placed on the Report and refuse to open to Preview or Print.

An alternative method is to create a Report Table with Field Names like M1, M2 to M12 for holding January to December Data and use an Append Query to add the report data into this table and design Report using this permanent field Names. We must find a way to define the field header labels with some formula taking the values from the report period parameter. The user can create report data for full 12 months or lesser period for a particular year. Then we are putting conditions on the user asking him to select data for a particular year only, not to cross from one year to the other when entering criteria.

If he/she goes for a trial run with cross over period then the data for earlier period comes at the right end of the Report and later period at the beginning. The field headings are another issue to take care. Finally we cannot call it a good report or let us put it this way, is not a user-friendly report.

We found a remedy for this issue by introducing a small program on the Report VB Module to take care of the final stage designing task based on the source data structure, every time we open it to preview or print.

  1. To get prepared for the Program, Import the Tables Employees and Orders from the Northwind.mdb sample database, if you don't have them. If you don't know the location of the sample database visit the Page Saving Data on Forms not in table for location references.
  2. Copy and paste the following SQL String into new Query's SQL Window and save it with the Names as indicated below:
    Query Name: FreightValueQ0
    SELECT [FirstName] & " " & [LastName] AS EmpName,
     Val(Format([ShippedDate],"yyyymm")) AS yyyymm,
    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;

  3. After creating the above Queries one after the other design a Report as shown below using FreightV_CrossQ Query as Data Source.

  4. In the Detail Section of the Report create 13 Text Box Controls; make the leftmost one slightly bigger for the Employee's Name and others for month-1 to month-12. Change the Name Property of the Text Boxes as M00, M01 to M12 from left to right (write two digit numbers in the Name Property as 01, 02 etc. along with prefix M). Leave the Control Source property empty.
  5. In the Report Footer Section create 13 Text Box controls and Name them from T00, T01 to T12. Leave the Control Source Property empty.
  6. In Page Header Section create 13 Label Controls and name them as 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 Save from File menu and save the Report with the name FreightVal_Rpt.
  9. Select Code from View menu to display the VB Module of the Report.
  10. Copy and Paste the following Code into the VB Module:

    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
    '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
    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)
            ctrl.ControlSource = "=SUM([" & fsort(j) & "])"
        End If
    'Header labels
    If j = 0 Then
        Me("L" & Format(j, "00")).Caption = "Employee Name"
        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
    Exit Sub
    MsgBox Err.Description, , "Report_0pen()"
    Resume Report_Open_Exit
    End Sub

  11. After copying the code minimize the VB Window, display the Property Sheet of the Report and check whether the On Open property is marked with the [Event Procedure] Value. If it is not, then the code we have pasted is not correctly loaded into this event. Select [Event Procedure] from the drop down list and open the Code module, cut the Code (except the top and bottom lines that we have pasted from the Web page) and paste the Code within the newly opened Empty Report_Open() . . . End Sub lines, remove the orphaned lines, and save the Report.
  12. Open the Report in Print Preview. Your Report must be loaded with the Values from the Source Query with correct heading Labels and the Report Footer Summary.
  13. Open the First Query in design view and change the Criteria values in different ranges, taking care that the Range of values are not exceeding 12 months (it can be less than 12), and try out the Report.

If the selected period exceeds 12 months the Report will open with the data that fits into the maximum number of fields, after showing a message indicating that the selected period exceeds 12 months.

If the selected period is less than 12 months then the rightmost controls will be empty. In either case the Report will open and we can view the contents.

Download Demo Database



  1. Thanks for the post.

    I was recently tasked with updating some old legacy Access reports, and they were hoping to do what this article clearly outlines. It is safe to say that you saved me several hours of research and trail and error, seeing as how my experiance in Access is limited.

    Good luck in the future.

  2. Hi there

    I am using Access 2003. I followed your instructions but I only get the header (the months in the right format), footer (the total for the month) and in the details section only the list of employee names. There are no values for each name. How do I get that to be displayed as well.

    I tried other sample codes from Northwind but also end up with the same problem. What am I doign wrong?

  3. Please forward your Database in Zipped form (if it is small enough) so that I can have a look at it. You can find my e-mail address at the left panel.

    In the meantime I shall create a sample database of Dynamic Report and upload into the Website so that you can download it as well.


  4. Hi

    I have done once a report for sawmill. Their request were to get specification of sawn material (columns by length).
    I gathered data with crosstab query and dynamically modified report. More or less same approach as used in this track, but I created and positioned dynamically "length columns" (used for selection criteria ao called "numerical" column names).
    But it workin well, so actually it is possible even position columns and in this way adjust report with variable amount of columns to left.


  5. add-in to previous append
    /oleg (

  6. [...] may take a look at the following link for an example of Cross-Tab Queries and Dynamic Report: LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Dynamic Report __________________ (Learn MS-Access Tips & [...]

  7. [...] LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Dynamic Report [...]

  8. [...] can provide, but take a look at it. Perhaps it can give you ideas to approach your problem: LEARN MS-ACCESS TIPS AND TRICKS - Dynamic Report __________________ (Learn MS-Access Tips & [...]

  9. Hi,

    How can I handle a crosstab query which returns more columns then will fit on one line?

    Same as if the user had selected 24 months.

    I this case I can not just throw away this data.




Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


Forms How Tos MS-Access Security Functions Reports Animations msaccess animation msaccess forms Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards msaccess graphs msaccess reporttricks ms-access functions msaccessprocess security advanced Access Security DOS Commands Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Array Auto-Number Command Button Join Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess functions msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Copy Custom Functions Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Rich Text Sequence SetFocus Summary Tab-Page Time Difference Union Query User Users Variables Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Important Notice

Dear Readers, Very sorry to inform you that I am finding it difficult to renew my contract with the Hostgator Hosting Plans to continue prov...


Blog Archive

Recent Posts