$Account.OrganizationName
The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
November 2009

Making Reports Do Double Duty -- Part Two

Last month, we looked at how to make a report title automatically change by using a text box that contained both static text and the contents of a data field. This month, we will continue the process of showing how reports can be dynamic by changing the underlying data.

In this Issue
  • Tip of the Month - Use Open Arguments to Manipulate Reports
  • The Data Behind the Report
  • Manipulate the Query, Rather than the Report
  • The Report Basics
  • The Code
  • Running and Testing
  • Conclusion

  • The Data Behind the Report

    Reports can have at least four different data sources:

    • A table.
    • A query.
    • A SQL statement.
    • No source at all.

    Tables and queries are fairly straightforward. Although a SQL statement is bit more complex, they have the advantage of accomplishing the same thing as a query without exposing the query, which might be inadvertently changed. The last option, no source at all, makes the most sense when the report is nothing but static text or the results of functions that the report might call.


    Manipulate the Query, Rather than the Report

    I've always been a big advocate of using SQL statements rather than queries, but to maximize the flexibility of a report, a query is a better source than a SQL statement. The primary reason is that a SQL statement within a report is more difficult to manipulate than a query, which exists independently of the report. To accomplish our goal of manipulating the report, we will use a query as our source.

    Conceptually, you can manipulate a query in code prior to opening the report. So, when opened, the report will use the new query that was put in place before the report was opened.


    The Report Basics

    For the purposes of this exercise, we will assume that our report is called rptCaseMgr, and that we have already structured the report to show the name of a case manager in the title. To see how to do this, see theOctober issue of Access Wizard.

    The particular structure used in the body of the report is not terribly important. What is critical is the data source for the report.

    In this case, you want to be sure that the data source is a query in your database. Because the query that we're going to use is dynamic, I make sure that I give it a generic name. I typically use qryUtilityReport.

    It makes most sense to build a query with real data, then use that query while designing the report. This is fairly straightforward. Create the query, then open a report in design view. Click on properties, then on the data tab, and choose the name of your query as a source of your report, as I've done below.


    The Code

    Now that we have a query in place to feed the report, the issue becomes manipulating the query. Like many objects in Microsoft Access, you can change the properties of a query not only manually, but also in code.

    Below is an example of how to accomplish this.

    Sub OpenCaseMgrReport(strCaseMgr As String)
    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String

    Set db = CurrentDb
    Set qdf = db.QueryDefs!qryUtilityReport
    strSQL = "SELECT tblDemoCaseMgr.CaseMgr, " & _ "tblDemoCaseMgr.Client " & _
    "FROM tblDemoCaseMgr " & _
    "WHERE (((tblDemoCaseMgr.CaseMgr)='" & strCaseMgr & "'));"
    qdf.SQL = strSQL

    DoCmd.OpenReport "rptCaseMgr", acViewPreview
    End Sub

    Let's pull this apart. Notice that our subroutine takes a variable on the way in.

    Sub OpenMyReport(strCaseMgr As String)

    This opening line requires an argument "strCaseMgr". We'll use this variable in our code to set the case manager for the report.

    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String


    This section sets our variables. The querydef variable refers to a query within Access. The string variable strSQL is what we will manipulate and ultimately set as the SQL behind our query.

    Set db = CurrentDb
    Set qdf = db.QueryDefs!qryUtilityReport


    These two lines establish the database, our current database, and the name of the query we are going to manipulate -- qryUtilityReport.

    The following line establishes our SQL statement:

    strSQL = "SELECT tblDemoCaseMgr.CaseMgr, " & _
    "tblDemoCaseMgr.Client " & _
    "FROM tblDemoCaseMgr " & _
    "WHERE (((tblDemoCaseMgr.CaseMgr)='" & strCaseMgr & "'));"

    This is typical concatenation, the heart of the action is in the statement:

    WHERE (((tblDemoCaseMgr.CaseMgr)='" & strCaseMgr & "'));"

    This is the criteria of the query, which states that the CaseMgr field must be equal to the variable passed into the routine. Note the use of single quotes before and after the double quotes surrounding strCaseMgr. This is necessary because the variable is a string variable and the single quotes are needed to indicate the text portion of the criteria.

    The next line, although short and simple, is powerful:

    qdf.SQL = strSQL

    This statement takes the SQL statement we constructed above and moves it into our query.

    After that, the code simply opens the report in a preview mode:

    DoCmd.OpenReport "rptCaseMgr", acViewPreview


    Running and Testing

    At this point, you will want to test the results. You can do it with code, such as the following:

    Sub OpenMaryReport()
    OpenCaseMgrReport "Mary Jones"
    End Sub

    Sub OpenAlReport()
    OpenCaseMgrReport "Al Dente"
    End Sub

    The first routine will pass in the variable "Mary Jones" as the Case Manager, while the second will pass in "Al Dente" as the Case Manager.

    It will be critical to test the routine before you put into production. The easiest way to do this is to first write code that manipulates the query and then, after the code has run successfully, open the query and inspect the results. Once you have that piece working properly, you can feed the query into your report.


    Conclusion

    We've shown you a very powerful technique this month that allows you to manipulate a query, which in turn is used as a source for a dynamic report. By using techniques such as this, we reduce the need to have similar objects in the database that need to be maintained separately.

    I urge you to experiment with techniques like this to make your life simpler.


    Tip of the Month - Use Open Arguments to Manipulate Reports

    If you are using Access 2003 or later, you have the option of sending an opening argument to reports. Since the early days of the Microsoft Access, forms have accepted open arguments that allow their behavior to respond to different requests on opening.

    With Access 2003 and later versions, opening arguments have been incorporated into reports. By using opening arguments, you can do much of what we did in this month via code, based on the opening arguments of reports.

    This can be a very powerful way to manipulate a report and cause it to be flexible on the fly.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

    Forward email

     

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886