Access Cookbook - Ken Getz [78]
Table 3-12. Properties that affect where a page or column breaks
Property set
Property name
Effect
Report
GrpKeepTogether
Controls whether groups in a report that have their KeepTogether property set to Whole Group or With First Detail will be kept together by page or by column.
Group
KeepTogether
When set to Whole Group or With First Detail, Access attempts to keep all of the sections of a group (header, footer, and detail) on the same page (or column).
Section
KeepTogether
When set to Whole Group or With First Detail, Access attempts to keep the whole section on the same page (or column).
ForceNewPage
Tells Access to force a new page never, before, after, or before and after the section.
NewRow or NewCol
Similar to ForceNewPage, except this property tells Access to force a new row or column never, before, after, or before and after the section. If you select "Across, then Down" in the Column Layout option in the Layout tab of the Page Setup dialog, a new row is started; if you select "Down, then Across", a new column is started.
RepeatSection
When set to Yes, Access will repeat this section at the top of the next page (or column) when the group spans more than one page (or column).
3.15. Customize a Report's Grouping and Sorting at Runtime
Problem
You have a report that has several different grouping and sorting fields that you need to rearrange every time you run the report. To do this, you've created five or six different versions of the same report, changing only the order of the fields and which fields are sorted or grouped. This is a maintenance nightmare, especially when you want to change some aspect of the report, which means having to change all the variants of this same report. Is there any easier way to do this in Access?
Solution
You can manipulate most aspects of a report's design using VBA code. This solution shows you how to programmatically open a report in design mode and manipulate several properties of controls and groups. Using this technique and a driving form, you can create a single report that can be customized using different sorting and grouping fields every time it is run.
Load 03-15.MDB and open frm_rptCompaniesSetup, which is shown in Figure 3-36.
Figure 3-36. The frm_rptCompaniesSetup form is used to set up the rptCompanies report
Select a grouping field and zero, one, two, or three other fields for the report (any or all of which can be sorted). When you're done, press the Preview or Print button and a report matching the chosen sorting/grouping fields will be previewed or printed for you. A sample report using the settings from Figure 3-36 is shown in Figure 3-37.
Figure 3-37. The rptCompanies report is customized every time it is run
To create a customizable report of your own, follow these steps:
Identify the table or query on which the report will be based. In our example, the report is based on the tblCompanies table. Decide which of the fields in this table or query you wish to allow to be selected, grouped, or sorted. In the sample database, we decided to use all of the fields from tblCompanies.
Create a table with one field, ReportFieldName, with a data type of Text. Make this field the primary key of the table. Save the table—in the example, we named it zstbl_rptCompaniesFields—and switch to datasheet view, adding a record for each field identified in Step 1.
Create a new unbound form. Add one unbound combo box for each field you want to be able to customize at runtime. For example, in the frm_rptCompaniesSetup form, we allow for one grouping field and up to three sorting fields (see Figure 3-36). The names of the combo box fields and their RowSource properties are listed in Table 3-13. All other properties are set to the default values.
Change "zstbl_rptCompaniesFields" to the name of the table from Step 2. Change "frm_rptCompaniesSetup" to the name of your form. Create additional combo boxes as needed,