Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Dynamic Report

Introduction

Report designing in MS-Access is very easy as we already know. Once we set up 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 in the Report design itself as all the standard labels, like the main heading, Report generated for which period, prepared date, Page Numbers, etc., are already taken care of as part of the initial report design 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. Even more difficult situation is to create Report Summary figures at Group-level and Report Footer level Grand-totals.

When someone asks for something difficult we can easily say it is not possible and the user may swallow it too, provided 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 into this category too. But, then after saying that it keeps me disturbed, and start 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, try something along those lines of ideas, and finally come up with something that works. Doesn't matter how many steps you have taken to achieve that. The final result is that matters. You can always go back and review the steps and refine them. But, there are times that we have to stick to the No answer too.

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

Report Preparation

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, and Month-wise formatted data shown below:

sample data table view

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

The Report contents should be for a period of 12 months and the data selection criterion is set for the period Between 199607 (July 1996) and 199706 (June 1997) 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, with no issues.

Report Data Selection Criteria

But, when the data selection criteria change for a different period the Report will not run. It will show an error on the first field that does not match with the fields already placed on the Report TextBoxes and refuses 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 a Report using these permanent field Names. We must find a way to define the field header labels with some formula taking the values of the report period parameter. The user can create report data for the full 12 months or a 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 cross-over a period, then the data for the earlier period comes at the right end of the Report and the later period in the beginning. The field headings are another issue to take care of.  Finally, we cannot call it a good report or let us put it this way, is not a user-friendly report.

Sample Data Preparation Queries

We found a remedy for this issue by introducing a small program on the Report VBA 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 the 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,
     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 after the other design a Report is shown below using FreightV_CrossQ Query Data Source.

  4. In the Detail Section of the Report create 13 Text Box Controls; make the leftmost one wider for the Employee's Name, and others month-1 to 12. Change the Name Property of the TextBoxes as M00, M01 to M12 from left to right (write two-digit numbers in the Name Property as 01, 02, etc., along with the prefix M). Leave the Control Source property empty.

  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 Header Section creates 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 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.

    Print Previewing 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 the 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


Download Demo Database


Share:

9 comments:

  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.

    ReplyDelete
  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?

    ReplyDelete
  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.

    Regards,

    ReplyDelete
  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.

    /oleg

    ReplyDelete
  5. add-in to previous append
    /oleg (oleg@nassu.pri.ee)

    ReplyDelete
  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 __________________ http://www.msaccesstips.com (Learn MS-Access Tips & [...]

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

    ReplyDelete
  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 __________________ http://www.msaccesstips.com (Learn MS-Access Tips & [...]

    ReplyDelete
  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.

    Thanks

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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