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

Events Available for Report Sections, and When to Use Them

Just as the report itself has events, so does each section of the report. The three section events are the Format event, Print event, and Retreat event, covered in the following sections.

The Format Event

The Format event happens after Access has selected the data to be included in a report section, but before it formats or prints the data. With the Format event, you can affect the layout of the section or calculate the results of data in the section, before the section actually prints. Listing 10.8 shows an example.

Listing 10.8 Using the Format Event to Affect the Report Layout

This code is found in the rptInvoice report included in the CHAP10EX.MDB database found on your sample code CD. The report has controls that track how many rows of detail records should be printed on each page. If the maximum number of rows has been reached, a control with the text Continued on Next Page… is visible. If the control is visible, the page break control is also made visible, and all the controls that display the detail for the report are hidden. The report is kept from advancing to the next record.

Another example of the Format event is found in the Page Header of the rptEmployeeSales report, found in the CHAP10EX.MDB database. Because the report is an unbound report whose controls are populated by using Visual Basic for Applications (VBA) code at runtime, the report needs to determine what's placed in the report header. This varies depending on the result of the Crosstab query on which the report is based. The code appears in Listing 10.9.

Listing 10.9 Using the Format Event to Populate Unbound Controls at Runtime

The code loops through each column of the recordset that results from executing the Crosstab query (in the Open event of the report). The code populates the controls in the report's Page Header with the name of each column in the query result. The final column header is set equal to Totals. Finally, any remaining (extra) text boxes are hidden. This is one of several examples in the chapter that covers the Format event.Chapter 14, "What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important?" If you are unfamiliar with DAO and ADO, you might want to review Chapter 14 before reviewing the examples.

TIP

By placing logic in the Format event of a report's Detail section, you can control what happens as each line of the Detail section is printed.

The Print Event

The code in the Print event executes when the data formats to print in the section, but before it's actually printed. The Print event occurs at the following times for different sections of the report:

• Detail Section :
  Just before the data is printed.

• Group Headers :
  Just before the Group Header is printed; the Group Header's Print event has access to both the Group Header and the first row of data in the group.

• Group Footers :
  Just before the Group Footer is printed; the Print event of the Group Footer has access to both the Group Footer and the last row of data in the group.

Listing 10.10 is in the Print event of the rptEmployeeSales report's Detail section; this report is included in the CHAP10EX.MDB database and is called from frmEmployeeSalesDialogBox.

Listing 10.10 Using the Print Event to Calculate Column and Row Totals

The code begins by evaluating the PrintCount property. If it's equal to 1, meaning this is the first time the Print event has occurred for the Detail section, the row total is set equal to 0. The code then loops through each control in the section, accumulating totals for each column of the report and a total for the row. After the loop has been exited, the routine places the row total in the appropriate control and adds the row total to the report's grand total. The report's Detail section is now ready to be printed.

NOTE

Many people are confused about when to place code in the Format event and when to place code in the Print event. If you're doing something that doesn't affect the page layout, you should use the Print event. However, if you're doing something that affects the report's physical appearance (the layout), use the Format event.

The Retreat Event

Sometimes Access needs to move back to a previous section when printing, such as when a group's Keep Together property is set to With First Detail or Whole in the Sorting and Grouping dialog box. Access needs to format the Group Header and the first detail record or, in the case of Whole, the entire group. It then determines whether it can fit the section on the current page. It retreats from the two sections, and then formats and prints them; a Retreat event occurs for each section. Here's an example of the Retreat event for a report's Detail section:

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

This code is placed in the Retreat event of the rptEmployeeSales report that's part of the CHAP10EX.MDB. Because the report is an unbound report, it needs to return to the previous record in the recordset whenever the Retreat event occurs.

CAUTION

Whenever you're working with an unbound report, you need to be careful that the record pointer remains synchronized with the report. For example, if the record pointer has been advanced and the Retreat event occurs, the record pointer must be moved back to the previous record.

Order of Section Events

Just as report events have an order, report sections also have an order of events. All the Format and Print events for each section happen after the report's Open and Activate events, but before the report's Close and Deactivate events. The sequence looks like this:

Open(Report)Activate(Report)Format(Report Section)
Print(Report Section)Close(Report)Deactivate(Report)