Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 3.4 Print a Message on a Report if Certain Conditions Are Met

3.4.1 Problem

On a letter that you mail
to all the customers on a mailing list, you want to print a message
on only some customers' letters (depending on, for
example, the customer's zip code, credit status, or
past orders). How do you make a text box print only when certain
conditions are met?

3.4.2 Solution

You can create an event procedure
that's called from the Format event of a report
section to make a single controlor an entire
sectionvisible or invisible depending on a condition you
specify. This solution shows you how to create a simple event
procedure that checks each report record for a certain condition and
then prints a message only if that condition is met.

Follow these steps to add an event
procedure to your report that prints a message only for certain rows:

1. Create a new report or open an existing report in design view. Add to
   the page header section any controls that you wish to show for only
   selected records. In the rptMailingByZipWithCondition sample, we
   included three labels and a rectangle control in the page header
   section.

2. While the cursor is still located in
   the page header section, select View Properties to view
   the section's properties sheet (if
   it's not already open).

3. Create a new event procedure for the section's
   Format event. (If you're unsure of how to do this,
   see the How Do I Create an Event Procedure? in the the preface of this book.)

4. Add to the Format event procedure an
   If...Then statement with the following basic
   structure:

   If (some condition) Then
   Me.Section(acPageHeader).Visible = True
   Else
   Me.Section(acPageHeader).Visible = False
   End If

   For example, in rptMailingByZipWithCondition, we added an event
   procedure that tests if the first two characters of the Zip Code
   field are equal to 98. The complete event procedure is shown here:

   Private Sub PageHeader0_Format(Cancel As Integer, _
   FormatCount As Integer)
   ' Set the visibility of the page header section,
   ' depending on whether or not the current
   ' zip code starts with "98".
   If Left(Me.ZipPostalCode, 2) = "98" Then
   Me.Section(acPageHeader).Visible = True
   Else
   Me.Section(acPageHeader).Visible = False
   End If
   End Sub

5. Save the report and preview it to see if the event procedure is
   working properly.

Load the rptMailingByZip report from

03-04.MDB .
This sample report, which is bound to the tblCompanyAddresses table,
is used to print a letter to customers who are sorted by zip code. It
includes a message in the page header that announces the
company's booth in an upcoming conference. The
message prints for all customers, even those outside the Seattle
area. Now load rptMailingByZipWithCondition to see an example of a
report that selectively prints a message. Notice that this version of
the report prints the message only for customers whose zip codes
begin with 98 (see Figure 3-8 and Figure 3-9).

Figure 3-8. An address whose zip code does not start with 98, with no message

Figure 3-9. An address whose zip code starts with 98, with the message

3.4.3 Discussion

The event procedure uses the
report's Section property and the
section's Visible property to make an entire section
visible or invisible when the report is formatted. Whether the
section is visible depends on its meeting the condition in the
If...Then expression. In our example, only zip
codes starting with 98 meet this condition, so the message about the
Seattle Expo will print only on pages for customers located in or
near Seattle.

Table 3-7 lists the values and constants you can
use in expressions to refer to the various sections on a form or
report. Group levels 3 through 10 (reports only) continue the
numbering scheme shown here, but have no corresponding VBA constants.

 acDetail

 acHeader

 acFooter

 acPageHeader

 acPageFooter

 acGroupLevel1Header

 acGroupLevel1Footer

 acGroupLevel2Header

 acGroupLevel2Footer

In the code, you'll find expressions like the
following:

Me.ZipPostalCode

and

Me.Section(acPageHeader)

In these expressions, the built-in object named Me always refers to
the form in which the code is running. (It's
actually slightly more complex than
this"Me" actually refers to
the class containing the code, not the form, but
that's a topic best left for a more advanced book.)
Whenever you see code that contains
"Me." you can be assured that the
code is referring to an object on the form, or a field provided by
the form's data source. You may also find code that
uses "Me!" syntax. For all intents
and purposes, this is equivalent to the
"Me." syntax, and you should simply
treat the two syntaxes the same. At this time, the
"Me." syntax is preferred because
it provides a very slight performance edge. In addition, the
"Me." is almost always
optionalyou'll see cases in this book in
which the code simply doesn't include this prefix
when referring to controls and fields provided by a form.

In the sample report, which prints one record per page, four controls
need to be turned on or off together: the label with the message, two
labels with Wingdings pointing-hand graphics, and a rectangle
surrounding the other controls. Placing all of these controls in one
section and making the section as a whole visible or invisible is
more efficient than making each control visible or invisible. Often,
however, you'll need to print a message on a report
that contains multiple records per page. For example, you might print
the word "Outstanding" alongside a
sales report when a salesperson has had more than $1 million in sales
for a year. In this case, you'll have to use code
that works with the Visible property of individual controls, such as
that shown here:

If Me.Sales >= 1000000 Then
Me.txtOutstanding.Visible = True
Else
Me.txtOutstanding.Visible = False
End If

If you look at rptMailingByZip or rptMailingByZipWithCondition in
design view, you may notice an odd expression as the ControlSource
property for the txtCityStateZip control in both reports:

=([City]+", ") & ([StateProvince]+"  ") & [ZipPostalCode]

Note that we have used both the +
and & concatenation operators in this expression. These two
operators have a subtle difference: When you use + and one of the
concatenated strings is Null, the whole expression
becomes Null; when you use &, the null part of
the expression is ignored. The effect caused by the + operator is
termed

null propagation , which you can
short-circuit by surrounding that part of the expression in
parentheses. The net effect of all this is that in the previous
expression, if City is Null, City

and the comma and space following it will drop
out of the expression. Likewise, if StateProvince is
Null, it

and the two spaces
to which it is concatenated will drop out of the expression.
Selective use of the + concatenation operator is both easier to read
and more efficient than using one or more

IIf
functions.

You may find it useful to collapse an If...Then
statement down into a single expression. For example, the code in the
sample report can be collapsed down to the following single
statement:

Me.Section(acPageHeader).Visible = (Left(Me.ZipPostalCode, 2) = "98")

The second code example could be collapsed into this single statement:

Me.txtOutStanding.Visible = (Me.Sales >= 1000000)

It's up to you to decide which syntax
you'd like to use. Some developers like the full
If...Then statement. Others like the compactness
of the single expression.