Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Practical Applications of Report Events and Properties

When developing reports, you should make sure that you can use the report in as many situations as possiblethat you build as much flexibility into the report as you can. Instead of managing several similar reports, making changes to each one whenever something changes, you can manage one report that handles different situations. Using the events and properties covered in this chapter will help you do just that. This might involve changing the report's RecordSource at runtime; using the same report to print summary data, detail data, or both; changing the print position; or even running a report based on a Crosstab query with unbound controls. All these aspects of report design are covered in the following sections.

Changing a Report's RecordSource

There are many times when you might want to change a report's RecordSource at runtime. By doing this, you can allow your users to alter the conditions for your report and transparently modify the query on which the report is based. The rptClientListing report in CHAP10.MDB has the code in Listing 10.15 in its Open event.

Listing 10.15 An Example of Using the Report Open Event to Modify a Report's RecordSource

This code begins by opening the frmClientListingCriteria form, if it isn't already loaded. It loads the form modally and waits for the user to select the report criteria (see Figure 10.11). After the user clicks to preview the report, the form sets its own Visible property to False. This causes execution to continue in the report, but leaves the form in memory so that its controls can be accessed with VBA code. The code evaluates the value of the form's optCriteria option button. Depending on which option button is selected, the report's RecordSource property is set to the appropriate query. The following code is placed in the Close event of the report:

Private Sub Report_Close()
DoCmd.Close acForm, "frmClientListingCriteria"
End Sub

This code closes the criteria form as the report is closing. The frmClientListingCriteria form has some code that's important to the processing of the report. It's found in the AfterUpdate event of the optCriteria option group (see Listing 10.16).

Listing 10.16 The AfterUpdate Event of the optCriteria Option Group

This code evaluates the value of the option group. It hides and shows the visibility of the cboCity and cboStateProv combo boxes, depending on which option button the user selects. The cboCity and cboStateProv combo boxes are then used as appropriate criteria for the queries that underlie the rptClientListing report.

The example shown in Listing 10.15 uses three stored queries to accomplish the task of switching the report's record source. An alternative to this technique is to programmatically set the RecordSource property of the report to the appropriate SQL statement. This technique is illustrated in Listing 10.17, and is found in rptClientListingAlternate on the sample code CD.

Listing 10.17 Using the Report Open Event to Modify a Report's RecordSource to the Appropriate SQL Statement

The example programmatically builds a SQL statement based on the option selected on the criteria form. It uses the cboCity and cboStateProv combo boxes to build the WHERE clause in the appropriate SQL strings.

Listing 10.18 shows my favorite alternative. It is somewhat of a compromise between the first two alternatives. You will find the code in frmClientListingCriteriaAlternate. Unlike the previous two examples, the criteria form drives this entire example. In other words, you will not find any code behind the report. Listing 10.18 looks like this:

Listing 10.18 The Code Behind the frmClientListingCriteriaAlternate Form

The code begins by determining which option button the user selected. Based on which option button the user selected, the code enters the appropriate branch of the Case statement to build the necessary Where clause. The code uses the Where clause as the WhereCondition parameter of the OpenReport method of the DoCmd object. The RecordSource of the report is always the same. It is the WhereCondition parameter that differentiates the data that appears in the report.

Working with Report Filters

The Filter and FilterOn properties allow you to set a report filter and to turn it on and off. Three possible scenarios can apply:

• No filter is in effect.

• The Filter property is set but is not in effect because the FilterOn property is set to False.

• The filter is in effect. This requires that the Filter property is set, and the FilterOn property is set to True.

You can set filtering properties either at design time or at runtime. This solution provides another alternative to the example provided in Listing 10.15. With this alternative, the RecordSource of the report is fixed. The Filter and FilterOn properties are used to display the appropriate data. Listing 10.19 provides an example. The code is found in rptClientListingFiltered on the sample code CD.

Listing 10.19 Using the Filter and FilterOn Properties

In the example, the RecordSource property of the report is the qryClients query. The query returns clients in all cities and all states. The example uses the Open event of the report to filter the data to the appropriate city or state.

CAUTION

Listings 10.15, 10.17, and 10.18 are much more efficient than the code in Listing 10.19. In a client/server environment, such as Microsoft SQL Server, with the code in Listings 10.15, 10.17, and 10.18, only the requested data comes over the network wire. For example, only data for the requested city comes over the wire. On the other hand, the Filter property is applied

after the data comes over the wire. This means that, in the example, all clients come over the wire, and the filter for the requested City or State is applied at the workstation. Server-side filters are the exception to this. These filters are available with Access Data Projects (ADP files). ADP files are covered in

Alison Balter's Mastering Access 2002 Enterprise Development .

Working with the Report Sort Order

The OrderBy and OrderByOn properties are similar to the Filter and FilterOn properties. They allow you to apply a sort order to the report. As with filters, three scenarios apply:

• No sort is in effect.

• The OrderBy property is set but is not in effect because the OrderByOn property is set to False.

• The order is in effect. This requires that the OrderBy property is set, and the OrderByOn property is set to True.

You can set ordering properties either at design time or at runtime. The OrderBy and OrderByOn properties are used to determine the sort order of the report and whether the sort is in effect. Listing 10.20 provides an example. The code is found in rptClientListingSorted on the sample code CD.

Listing 10.20 Using the Report Open Event to Modify the Sort Order of a Report

The code appears in the Open event of the report. It evaluates which option button the user selected on the frmClientListingSortOrder form. It then sets the OrderBy property as appropriate and sets the OrderByOn property to True so that the OrderBy property takes effect.

CAUTION

The OrderBy property

augments, rather than replaces, the existing sort order of the report. If the OrderBy property is in conflict with the sort order of the report, the OrderBy property is ignored. For example, if the sort order in the Sorting and Grouping window is set to CompanyName and the OrderBy property is set to City combined with CompanyName, the OrderBy property is ignored.

Using the Same Report to Display Summary, Detail, or Both

Many programmers create three reports for their users: one that displays summary only, one that displays detail only, and another that displays both. This is unnecessary. Because you can hide and display report sections as necessary at runtime, you can create one report that meets all three needs. The rptClientBillingsByProject report included in the CHAP10.MDB database illustrates this point. Place the code shown in Listing 10.21 in the report's Open event.

Listing 10.21 Using the Report Open Event to Hide and Show Report Sections as Appropriate

The code begins by opening frmReportDateRange included in CHAP10.MDB (see Figure 10.12). The form has an option group asking users whether they want a Summary report, Detail report, or a report that contains both Summary and Detail. If the user selects Summary, the caption of the Report window and the lblTitle label are modified, and the Visible property of the Detail section is set to False. If the user selects Detail Only, the captions of the Report window and the lblTitle label are modified, and the Visible property of the Group Header and Footer sections is set to False. A control in the Detail section containing the company name is made visible. The CompanyName control is visible in the Detail section when the Detail Only report is printed, but it's invisible when the Summary and Detail report is printed. When Both is selected as the level of detail, no sections are hidden. The captions of the Report window and the lblTitle label are modified, and the CompanyName control is hidden.

The code behind the form's Preview button looks like Listing 10.22.

Listing 10.22 Code That Validates the Date Range Entered by the User

This code makes sure that both the beginning date and the ending date are filled in, and that the beginning date comes before the ending date. If both of these rules are fulfilled, the code sets the Visible property of the form to False. Otherwise, the code displays an appropriate error message.

Numbering Report Items

Many people are unaware how simple it is to number the items on a report. Figure 10.13 provides an example of a numbered report. This report is called rptClientListingNumbered, and is located on the sample code CD. The process of creating such a report is extremely simple. Figure 10.14 shows the Data properties of the txtNumbering text box. The Control Source property allows you to set the starting number for a report. The Running Sum property allows you to determine when the numbering is reset to the starting value. The Control Source property of the text box is set to =1, and the Running Sum property is set to Over All. The combination of these two properties causes the report to begin numbering with the number 1 and to continue the numbering throughout the report. Setting the Running Sum property to Over Group causes the numbering to reset itself at the beginning of each report grouping.

Printing Multiple Labels

Many times, users want to print multiple copies of the same label. The report's MoveLayout, NextRecord, PrintSection, and PrintCount properties help us to accomplish the task. The form shown in Figure 10.15 is called frmClientLabelCriteria and is found in CHAP10.MDB. It asks users to select a company and the number of labels they want to print for that company. The code for the Print Labels command button looks like Listing 10.23.

Listing 10.23 Code That Prints the lblClientMailingLabels Report for the Selected Company

Notice that the routine uses the company name selected from the combo box as a criterion to run the lblClientMailingLabels report. The Open event of lblClientMailingLabels appears in Listing 10.24.

Listing 10.24 The Open Event of lblClientMailingLabels

This code tests to make sure the frmClientLabelCriteria form is open. If it's not, the code displays a message and cancels the report. The Detail section's Print event, which compares the requested number of labels with the number of labels printed, is the key to the whole process (see Listing 10.25).

Listing 10.25 The Code in the Print Event

This code compares the PrintCount property to the number of labels the user wants to print. As long as the PrintCount is less than the number of labels requested, the record pointer is not advanced. This causes multiple labels to be printed for the same record.

Determining Where a Label Prints

Users often want to print several copies of the same label, but they might also want to print mailing labels in a specific position on the page. Users generally do this so that they can begin the print process on the first unused label. The frmClientLabelPosition form from CHAP10.MDB lets the user specify the first label location on which to print by designating the number of labels that the user wants to skip (see Figure 10.16). The Open event of lblClientMailLabelsSkip appears in Listing 10.26.

Listing 10.26 The Code in the Open Event of lblClientMailLabelsSkip

The code tests to make sure that the frmClientLabelPosition form is loaded. It also sets a private variable, mboolFirstLabel, equal to True. The Detail section's Print event, which suppresses printing until the correct number of labels is skipped, appears in Listing 10.27.

Listing 10.27 The Detail Print Event

This routine checks to see whether the PrintCount property of the report is less than or equal to the number of the labels to skip. It also checks to make sure that the mboolFirstLabel variable is equal to True. If both conditions are True, the report doesn't move to the next record and doesn't print anything. The print position is advanced. When the PrintCount becomes greater than the number of labels to skip, the mboolFirstLabel variable is set to False and printing proceeds as usual. If mboolFirstLabel is not set to False, the designated number of labels is skipped between each record. One additional event makes all this workthe Format event of the Report Header:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
'Set the mboolFirstLabel flag to True when the header
'formats for the first time
mboolFirstLabel = True
End Sub

The ReportHeader Format event sets mboolFirstLabel back to True. You must include this step in case the user previews and then prints the labels. If the mboolFirstLabel variable is not reset to True, the selected number of labels isn't skipped on the printout because the condition that skips the labels is never met.

Building a Report from a Crosstab Query

It's difficult to base a report on the results of a Crosstab query because its number of columns usually varies. Take a look at the example shown in Figure 10.17. Notice that the employee names appear across the top of the report as column headings, and the products are listed down the side of the report. This report is based on the Crosstab query called qxtabEmployeeSales, part of the CHAP10EX.MDB database found on the sample code CD (see Figure 10.18). The problem is that the number of employeesand, therefore, column headingscan vary. This report is coded to handle such an eventuality.

When the rptEmployeeSales (located in CHAP10EX.MDB) report runs, its Open event executes (see Listing 10.28).

Listing 10.28 Code That Obtains Criteria Information for the Report and Then Builds a Recordset That Underlies the Report

The Open event points a database object variable to the current database. It then checks to make sure the criteria form, frmEmployeeSalesDialogBox, is open. This form supplies the criteria for the qxtabEmployeeSales query that underlies the report. It opens the qxtabEmployeeSales query definition and passes it the parameters from the frmEmployeeSalesDialogBox criteria form. Next, it opens a recordset based on the query definition, using the criteria found on the frmEmployeeSalesDialogBox form. The number of columns returned from the Crosstab query is very important. The code stores this number in a Private variable called mintColumnCount, and uses it throughout the remaining functions to determine how many columns to fill with data.

NOTE

This book focuses on the use of ADO (ActiveX Data Objects) rather than DAO (Data Access Objects). You might wonder why this example uses DAO rather than ADO. The query that underlies this example is a Crosstab query. The ADO command object does not recognize Crosstab queries. It was therefore necessary to use DAO in this example.

Next, the Report Header Format event occurs. It moves to the first record in the recordset created during the Open event (see Listing 10.29). It also calls an InitVars routine shown in Listing 10.30.

Listing 10.29 The Report Header Format Routine

The InitVars routine initializes some variables used in the report.

Listing 10.30 The InitVars Routine

The mlngReportTotal variable is used for the report grand total (all products, all salespeople), and the mlngRgColumnTotal array contains the total for each salesperson. After the Report Header Format event occurs, the Page Header Format event takes place (see Listing 10.31.)

Listing 10.31 The Code in the Page Header Format Event that Inserts the Appropriate Column Headings and Hides the Appropriate Controls

The PageHeader Format event uses the names of the fields in the query results as column headings for the report. This essential routine is "smart" because, after it fills in all the column headings, it hides all the extra controls on the report.

Next, the Detail Section Format event, shown is Listing 10.32, occurs.

Listing 10.32 The Code in the Detail Section Format Event that Inserts Data from the Current Row into the Report and Hides the Appropriate Controls

The Detail Section Format event checks the recordset's EOF property to determine whether the last record in the query has already been read. If not, the section's FormatCount property is tested to see whether it's equal to 1. If so, each column in the current record of the recordset is read. The code fills each control in the Detail section with data from a column in the recordset, and any unused text boxes in the report's Detail section are hidden. Finally, the code moves to the next record in the recordset, readying the report to print the next line of detail. The xtabCnulls function, which converts Null values into zeros, is called each time the recordset underlying the report is read:

Private Function xtabCnulls(varX As Variant)
'Test if a value is null.
XtabCnulls = NZ(varX,0)
End Function

The xtabCnulls function evaluates each value sent to it to check whether the value is Null. If so, it returns zero from the function; otherwise, it returns the value passed to the function.

After the code executes the Detail Section Format event, it executes the Detail Section Print event (shown in Listing 10.33).

Listing 10.33 The Code in the Detail Section Print Event that Accumulates Column Totals and Prints Rows Totals

The Detail Section Print event generates the row total value, placing it in the last column of the report, accumulating column totals, and accumulating the mlngReportTotal value, which is the grand total for all columns and rows. It does this by making sure the PrintCount of the section is 1. If so, it resets the lngRowTotal variable to 0. Starting at column 2 (column 1 contains the product name), it begins accumulating a row total by looking at each control in the row, adding its value to lngRowTotal. As it traverses each column in the row, it also adds the value in each column to the appropriate element of the mlngRgColumnTotal private array, which maintains all the column totals for the report. It prints the row total and adds the row total to the report's grand total.

When the Retreat event occurs, the following code executes:

Private Sub Detail1_Retreat()
'Always back up to previous record when Detail section retreats.
mrstReport.MovePrevious
End Sub

This code forces the record pointer to move back to the previous record in the recordset. Finally, the report footer prints, which causes the Report Footer Print event, which prints the grand totals and hides the appropriate controls, to execute (see Listing 10.34).

Listing 10.34 The Code in the Report Footer Print Event

The Report Footer Print event loops through each control in the footer, populating each control with the appropriate element of the mlngRgColumnTotal array. This gives you the column totals for the report. Finally, the grand total is printed in the next available column. Any extra text boxes are hidden from display.

Printing the First and Last Page Entries in the Page Header

Another useful technique is printing the first and last entries from a page in the report's header. The rptCustomerPhoneList report, found in the CHAP10EX.MDB database located on the sample code CD, illustrates this (see Figure 10.19). The code for this report relies on Access making two passes through the report. During the first pass, a variable called gboolLastPage is equal to False. The gboolLastPage variable becomes True only when the Report Footer Format event is executed at the end of the first pass through the report. Keep this in mind as you review the code behind the report.

The first routine that affects the report processing is the Page Header Format event routine shown in Listing 10.35.

Listing 10.35 The Code in the Page Header Format Event that Updates the Appropriate Text Boxes with the First and Last Entries on the Page

The Page Header Format routine tests to see whether the gboolLastPage variable is equal to True. During the first pass through the report, the gboolLastPage variable is equal to False. During the second pass, the txtFirstEntry and txtLastEntry text boxes (both of which appear in the report's header) are populated with data. The txtFirstEntry text box is filled with the value in the txtCompanyName control of the current record (the first record on the page), and the txtLastEntry text box is populated with the appropriate element number from the CustomerPhoneList array. Each element of the CustomerPhoneList array is populated by the Format event of the Page Footer for that page during the first pass through the report.

Next, the Page Footer Format event, which populates the array with the last entry on a page, is executed (see Listing 10.36).

Listing 10.36 The Code in the Page Footer Format Event

The Page Footer Format event determines whether the gboolLastPage variable is equal to False. If so (which it is during the first pass through the report), the code redimensions the gstrLast array to add an element. The value from the txtCompanyName control of the last record on the page is stored in the new element of the gstrLast array. This value eventually appears in the Page Header of that page as the last company name that appears on the page. Finally, the Report Footer Format event executes as shown in Listing 10.37. This event inserts data from the last row in the recordset into the last element of the array.

Listing 10.37 The Code in the Report Footer Format Event

The Report Footer routine sets the gboolLastPage variable equal to True and opens a recordset based on the Customers table. This is the recordset on which the report is based. It moves to the last record in the recordset and adds the CompanyName value from the recordset's last record in an additional element of the array.

Now the first pass of the report has finished. As the user moves to each page of the report during a print preview, or as each page is printed to the printer, the Format event executes for the Page Header. The company name from the first record on the page is placed in the txtFirstEntry control, and the appropriate element from the gstrLast array is placed in the txtLastEntry control.

Creating a Multifact Crosstab Report

By nature, Crosstab queries are limited because they don't allow you to place multiple rows of data in the result. For example, you can't display months as column headings and then show the minimum, average, and maximum sales for each employee as row headings. The rptSalesAverages report, found in the CHAP10EX database and shown in Figure 10.20, solves this problem.

Each time the Format event of the Page Header executes, the variable mboolPrintWhat is reset to False:

Private Sub PageHeader1_Format(Cancel As Integer, FormatCount As Integer)
'At top of page, initialize mboolPrintWhat variable to False
mboolPrintWhat = False
End Sub

After the Page Header Format event executes, the Group Header Format event launches, as shown in Listing 10.38.

Listing 10.38 The Code in the Group Header Format Event Used to Hide and Show the Appropriate Controls

The first time the Format event for the LastName Group Header (GroupHeader2) executes, the value of the mboolPrintWhat variable is equal to False. The txtSalesPersonLastName and the txtFirstName controls are made visible, and the lblMinimum, lblAverage, and lblMaximum controls are hidden. The mboolPrintWhat variable is set to True, and movement to the next record is suppressed by setting the value of the NextRecord property to False.

The second time the Format event for the LastName Group Header executes, the code hides the txtSalespersonLastName and txtFirstName controls. The code makes the lblMinimum, lblAverage, and lblMaximum controls visible, and sets the value of the mboolPrintWhat variable to False.

The only other code for the report, shown in Listing 10.39, is in the Format event of the Shipped Date Header (GroupHeader3).

Listing 10.39 The Code in the Group Header Format Event Used to Determine When Printing Occurs

This code compares the report's Left property to the result of an expression. The Left property is the amount that the current section is offset from the page's left edge. This number is compared with the value in the txtLeftMargin control added to the current month plus one, and then it's multiplied by the value in the txtColumnWidth control. If this expression evaluates to True, the code sets the NextRecord and PrintSection properties of the report to False. This causes the printer to move to the next printing position, but to remain on the same record and not print anything, which forces a blank space in the report. You might wonder what the complicated expression is all about. Simply put, it's an algorithm that makes sure printing occurs, and that Access moves to the next record only when the data is ready to print.