How to Create a Report | Programming Microsoft Access 2000 (Microsoft Programming Series)

You can manually create a report in three ways:

Using the AutoReport wizard. This one-click technique is attractive because of its simplicity.

Using other wizards. The Report wizard saves you time, and the Label and Chart wizards help you tackle specialized design issues.

Using Design view. Creating a report manually in Design view offers maximum layout, formatting, sorting, and grouping flexibility.

Using the AutoReport Wizard

The AutoReport wizard available from the Database window toolbar is a columnar report. Text and numeric fields appear in text boxes, Yes/No data types appear in check boxes, and OLE Object data types (such as .bmp images) appear in bound object frames. The records display one after the other in a streaming format from the first to the last record.

You can also select a tabular style report in the New Report dialog box. Advanced and intermediate developers will find this a fast, convenient way to generate a subreport.

Using Other Wizards

Access offers a general Report wizard as well as specialized wizards for labels and charts. You should use wizards liberally for reports and other kinds of database objects. They can speed up your development efforts and lead to standardization that makes maintenance easier.

The Report wizard lets you specify your report much like the Simple Query wizard lets you design a query. (See Chapter 4.) It saves you from having to design a query as a preliminary step for your reports. The wizard also simplifies setting sorting, grouping, and formatting options for your custom reports. The Label and Chart wizards perform specialized functions. The FrontPage guestbook case study later in this chapter illustrates output from the Label wizard. The Chart wizard for reports works like the one for forms. Figure 6-1 shows a chart report in Print Preview mode. (To refresh your understanding of the Microsoft Graph 2000 Chart object, see the discussion in Chapter 5 about picking a subset of data to chart.)

click to view at full size.

Figure 6-1. This chart was prepared using the Chart wizard for reports.

Creating a Report Manually in Design View

You can create a report manually by double-clicking the Create Report In Design View option in the Database window. (First select Reports from the Objects collection.) This opens a blank form whose base properties reflect the entry in the Report Template text box on the Forms/Reports page of the Options dialog box. (You open this dialog box with the Tools_Options command.) The default is Normal, which displays a plain white page with no colors or special fonts for labels. If you have a report with property settings that you want to use as a template for all other reports in an Access project, you can enter the name of that report in the Report Template text box. The settings of that report will be the default settings for all new reports. Existing reports will remain unchanged. You can use the template to determine the number of sections that initially show on a report as well as the default settings for report section properties and their controls.

NOTE
You use the Form Template entry for forms much like you use the Report Template entry for reports.

After opening a new report instance based on a template, you can populate it with controls to build your report. It is important to understand that Access reports are banded. In the Normal template, for example, a Detail band appears between a pair of Page bands. Any contents in the top and bottom bands appear at the top and bottom of each report page. The Detail band repeats once for each record on a page. It derives successive values from the report's record source.

Another standard pair of bands that can surround a Detail section in a report are the Report Header and Report Footer bands. These bands appear just once at the beginning and end of each report. Use the View menu to turn on and off display of the Report and Page bands around the central Detail band.

You can resize any report section by dragging its boundaries. For example, to cause the Report Footer band not to occupy any space while you still have a Report Header section, drag the Report Footer band's lower bound so that it is flush with its top bound.

The report in Figure 6-1 contains a Page Header band and a Detail band. The Page Header section contains two headlines: My Sample Chart from and Programming MS Access 2000. The unbound object frame displays from the report's Detail band. Since the frame control is not bound to a record source, it occurs only once.

Report controls

Access reports typically contain a mix of text boxes and labels. The text boxes reflect the values of fields in the underlying record source, while the labels identify the names of the text boxes and other controls. Other controls that typically display field values in a report include check boxes and bound object frames. The frames are appropriate for holding graphic images in a table. You use image controls to hold unbound images that are not field values in a table.

A main report can also serve as a host for one or more subreports, which appear as controls on the main report. The Sales By Category report in the Northwind database (shown in Figure 6-2) contains a subreport that lists sales by product. The main report presents the same data graphically. The subreport appears as a table on the left. The chart uses the ActiveX Microsoft Graph 2000 Chart object in an unbound frame. The subreport and chart both appear in the CategoryName Header band, which causes the subreport and chart to select the products for the category name appearing in the band's header for each page. Any single report can have multiple nested group bands, such as regions within countries or product groups within company divisions.

click to view at full size.

Figure 6-2. A subreport and a chart on a page of the Sales By Category report. The products change within each category heading.

Code behind reports

Custom applications usually require manual reports with formatting tailored to the specific content. While Access offers a wide array of programmatic options, you'll typically use only a small number of them:

Automatically printing or previewing a report by invoking the DoCmd OpenReport method or hyperlinking to the report. (See the section titled "Switchboard Forms" in Chapter 5 for samples that you can adapt for reports.)

Making a report dynamic. You can update one or more properties for a Report object. For example, you can change the record source property for a report and the caption for a label in the report.

Enumerating the AllReports collection members to determine whether a report exists and is open.

Creating multiple instances of reports. (Reports with modules are class modules.)

Intermediate and advanced developers often work with report events, which you can use to dynamically control the content and formatting of a report. Use custom Open, Activate, Close, and Deactivate form events instead of the Initialize event and the Terminate event for each new instance of the class module associated with a report. When you print or preview a report, the Format and Print events occur for successive sections of the report. The Format and Print events are useful for dynamically altering content and formatting. When you set the Keep Together property to True, this can cause the Retreat event to occur selectively. Report sections split bands into matching but distinct elements. For example, the Report Header section is the first section when it is present, and the Report Footer is the last section when you have a Report band.