2.2 Highlight the Current Field in Data-Entry Forms

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 control or an entire section visible 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 figs/u2192.gif 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 Section P.5.5 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

figs/acb_0308.gif

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

figs/acb_0309.gif

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.

 

Table 3-7. Values used to identify form and report sections in expressions

Setting

VBA constant

Description

0

acDetail

Detail section

1

acHeader

Form or report header section

2

acFooter

Form or report footer section

3

acPageHeader

Form or report page header section

4

acPageFooter

Form or report page footer section

5

acGroupLevel1Header

Group level 1 header section (reports only)

6

acGroupLevel1Footer

Group level 1 footer section (reports only)

7

acGroupLevel2Header

Group level 2 header section (reports only)

8

acGroupLevel2Footer

Group level 2 footer section (reports only)

 

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.



Access Cookbook
Access Data Analysis Cookbook (Cookbooks)
ISBN: 0596101228
EAN: 2147483647
Year: 2005
Pages: 174

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net