Using Reports in Access Projects

3 4

Reports in Access projects are most efficient if you use views in which only the required columns of data or stored procedures that use parameters and return results are selected. You can use the Input Parameters property to supply parameters to a stored procedurethat's used as a report's record source, possibly with a form as the interface for entering the values for the parameters.

To illustrate this technique, let's add a combo box on the NorthwindCS main menu to let the user select a company code and then create a simple report based on the Customers table, filtered by the combo box selection.

Follow the steps below to set up the combo box for filtering reports:

1. Choose Help, Sample Databases, Northwind Sample Access Project to open the NorthwindCS project. (The first time you open this project, you will get a message asking if you want to create this project; accept the message and the project will be created automatically.)
2. If the splash screen with the Northwind logo appears, close it.
3. The Northwind main menu should appear next; close it too.
4. In the Database window, click Forms and select the Main Switchboard form; press Ctrl+C to copy the form, and Ctrl+V to paste it to a name such as "Main Switchboard Original" (so you can revert to the original form afterward, if desired).
5. Open the Main Switchboard form in Design view.
6. Drag the bottom of the form down to provide about an inch more room on the form.
7. Click the Combo Box tool in the Toolbox and place a combo box control on the form.
8. Click Cancel to close the Combo Box Wizard.
9. (Optional) Adjust the size and font of the combo box and its attached label as desired.
10. Enter "Select a Company:" as the caption of the combo box's label.
11. Open the Combo box's properties sheet.
12. Name the combo box cboSelectCompany.
13. Select Customers as the combo box's Row Source.
14. Enter 2 for the Column Count value.
15. Enter 0.75";2.5" as the Column Widths value.
16. Enter 4" as the List Width value.

Figure 19-41 shows the combo box with its list dropped down to select a company; after the selection is made, only the short company code shows in the combo box.

Figure 19-41. Select a company for filtering reports in the Select Company combo box on the Northwind main menu.

Next, follow the steps below to create a simple report to use the company selection made in the combo box on the main menu:

1. Click Reports in the Database window, and click New to open the New Report dialog box.
2. Select Report Wizard as the report type, and select Customers as the data source for the new report; Click OK.
3. Proceed through the Report Wizard, setting up the report as desired (see Chapter 7, "Using Reports to Print Data," for more details on working with the Report Wizard).
4. (Optional) Fine-tune the report's design as desired.
5. When the report is done, switch it to Design view and open its Properties sheet.
6. Set the report's Input Parameters property to the following value:

    CustomerID varchar=Forms![Main Switchboard]![cboSelectCompany] 

The report is filtered for the selected customer when it's opened, as shown in Figure 19-42. Note, however, that although you only see the group heading for the selected company, all the records are returned.

Figure 19-42. A report is filtered by an input parameter picked up from a combo box on a form.