|
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.
| 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.
|
| Conclusion |
|
|
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?
|
|
