The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
October 2009

Avoid Redundancy: Don't Have Two Reports When You Can Have Only One

I frequently encounter users with Access Applications that have nearly identical reports with only minor differences. For instance, they have a report showing the case management load for Mary Jones and another report showing the case management load for Al Dente.

The structure of the report is exactly the same; there are only two differences. The first is the person's name; the second is the data that feeds the report. In the first case it's for Mary Jones in the second it's for Al Dente.

This month, we'll talk about how to change the title of the report on the fly, so that the person's name appears as appropriate.

Next month, we'll show how to change the data source for a report, thereby allowing the report to serve multiple purposes.

In this Issue
  • Tip of the Month - Think About Generic Objects Rather Than Single-Use Objects
  • Assumptions
  • The Technique: Combine Text with a Field
  • Conclusion
  • Trap of the Month - Beware Default Control Names

  • Assumptions

    For purposes of this exercise, we are going to assume that the structure for each case manager's report is exactly the same. Further, we'll assume that one of the fields in the query (or SQL statement) behind the report is called CaseMgr. We'll also assume that the title of the report should read: Case Report for [case manager name], as contained the in the CaseMgr field.

    For the purposes of our report, we'll have a text box in the Report Header section that will contain the title of the report.

    The Technique: Combine Text with a Field

    To get what we want, we will use the concatenation trick that we've used in the past to combine both text and the results of a field.

    To make this happen, place a text box in the page header section, and choose properties. Click on the data tab, and enter the following line in the control source:

    ="Case Report for " & [CaseMgr]

    Note that we start off with an equal sign. This tells Access that it will be using something that could be made as simple as a text box, but potentially more complex. We then follow it with our static text in quotes, followed by the ampersand sign and then the name of our field in brackets.

    When the report is opened, it will read the first record. Then, it will write our static text in our text box followed by the contents of whatever is in the CaseMgr field, which gives us just what we want.

    If the data behind the report has Mary Jones, our report will read Case Report for Mary Jones; if the data contains Al Dente, the report will reflect his name.


    This month, I've showed you a trick that will come in handy many times when creating reports: Using both static text and the results of a field in a text box.

    With this approach, you can have a dynamic text box that has both fixed text as well as the contents of a field in your report. When you implement this technique you reduce the number of reports in your application by having each one do double and triple duty to meet your needs.

    Trap of the Month - Beware Default Control Names

    When using a formula in a report as part of a text box, as we did this month, it's critical that the name of the field not match the name of a control in your report.

    For instance, if you have a field in your data source that's being used in a report called CaseMgr and a control on your report, called CaseMgr, using CaseMgr in a formula will result in an error message in your report.

    To solve this problem, make sure that the text box in your report is named something along the lines of txtCaseMgr, rather than simply CaseMgr (note that by default Access will name your text box the same name as your data field.) With this approach, you'll be able to reference the field in your report in both the formula and have it be the source of another control in your report.

    Tip of the Month - Think About Generic Objects Rather Than Single-Use Objects

    This month, we explored a technique that allowed us to use the same report for more than one purpose. As you build Access applications, this type of event will occur again and again. If you tend to use almost identical objects - be they reports, queries, forms or even full applications - that are just slightly different from one another, then you have an opportunity to reduce the number of objects in your database and simplify your life.

    Over the next few months, we will be looking at different ways to accomplish this, from the very simple case, as in today's Access Wizard, to much more complex uses.

    With this approach, if you want to make a change you only have to do it once, rather than many times, thereby reducing your effort. What could be better than that?

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

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