May 1999

Outline Lengthy Reports with a Table of Contents Page

95/97/2000

When you read a report with many records, it's easy to get lost in a torrent of data. In most cases, a table of contents that lists the page numbers for key groups, like the one shown in Figure A, would provide a much needed synopsis. Unfortunately, Access doesn't have a built-in feature that lets you create a table of contents.

Figure A: We'll use a simple DoCmd statement to append headings and page numbers into a blank table.

The Microsoft Knowledge Base does offer one solution, but it involves a complicated DAO procedure. Fortunately, there is an easier way, and in this article we'll show it to you. While our technique does involve VBA, if you've ever used SQL or the DoCmd action in a macro, you'll find it very familiar and easy to follow. Also, while we developed this technique in Access 97, you can use the same concepts, perhaps even the same keystrokes, in Access 95 and Access 2000.

Building a better TOC

In our example, we'll create a report that lists customers by region. We'll store the table of contents' page numbers and group headings in a separate table. Because Access doesn't know what data belongs where on a report until it formats each report section, we'll execute a SQL statement in a group header's Format event to insert page numbers into the contents table. Finally, when Access prints the customer list's last page, we'll trigger a DoCmd statement that opens a separate table of contents report.

Create the example

To create our reports, we'll need two tables--one to hold the list of customers for the main report, and the other for the table-of-contents data. Figure B shows the table with the list of customers. To create it, select the Tables tab in the Database window, then click New. Double-click Design View to open the table's design. Enter CustID as the first Field Name. Next, select AutoNumber for the Data Type, then click the Primary Key button (btnKey) on the toolbar to make this field the table's primary key. In the next blank Field Name type LName. Keep this field's Data Type as Text. However, in the Field Properties section, change the Field Size to 25. Also, in the Caption field, enter Last Name. For the next field in the table, enter Fname and choose Text as the field's Data Type. Change the Field Size to 25, and type First Name as the Caption. Finally, make the table's last field, Region, a Text field with a Field Size of 5. At this point, the table's design is complete, and we're ready to enter the customers' names shown in Figure B. To do so, click the Datasheet View button (btnDatasheet) on the toolbar. At the prompt, click Yes to save the table, and name it tblCustomers. Then, enter the records from Figure B. When you've finished, we'll create the table to hold the table-of-contents data.

Figure B: We'll generate a table of contents for a report based on this list of customers.

Figure C shows the tblContents table's Design view. Use the figure as a guide to create this table. The RptPage field is a simple Number field with an Integer field size. The Region field has a Text Data Type with a Field Size of 5. This field is the table's primary key; so, after you create it, click the Primary Key button on the toolbar. When you've finished, click the Save button (btnSave) and name the table tblContents.

Figure C: This table will hold the TOC's headings and page numbers.

Create the Delete query

Because the customer list report's Region sections will expand and contract depending on the number of customer records in our database, the page numbers may be different each time we run the report. Therefore, we'll want to clear out tblContents each time we run the report. We'll use a simple Delete query to do so.

To create the query, first select the Queries tab in the Database Window and click New. Double-click Design View to open the Query By Example grid. In the Show Table dialog box, select tblContents and click Add. After Access adds the table to the QBE, click Close. Next, drag the asterisk (*) from the tblContents' field list down into the QBE grid. Select Delete Query from the Query Type button's (btnDelete) dropdown list. Save the query as qryDelContents. At this point, your query should look like the one shown in Figure D. Now, we're ready to create our reports. We'll start with the table of contents.

Figure D: This query executes whenever you open the customer report.

Create the Table of Contents report

To begin, select the tblContents table in the Database window, and select Report from the New Object button's (btnReport) dropdown list. Then, double-click Report Wizard. Select both Region and RptPage to appear on the report and click Next twice. Choose RptPage from the dropdown box to sort the report by the page number. Click Next, and select a Tabular layout. Click Next twice more and name the report Table of Contents. Select the Modify The Report's Design check box, and click Finish. When you do, Access displays the report in Design view, as shown in Figure E.

Figure E: We'll modify this basic design to make it look more like the TOC shown in Figure A.

While this report serves the general purpose of a table of contents, it hardly looks like the TOCs with which we're familiar. If you want, you can modify the report's design as we did so it looks more like the one shown in Figure A. However, if you choose not to, it won't affect our technique. For our design, we dragged the title the full width of the report and gave it center alignment. The Page Header Labels and Detail section fields all have 12 point font sizes. To create the dotted line between the Region and RptPage fields, we added a regular line; then, in the Properties sheet, we set its Border Width to 3 pt and its Border Style to Sparse Dots. When you've finished modifying the report, click the Save button (btnSave). At this point, the report's design should look similar to the one shown in Figure F. Next, we'll create the customer report and add the VBA code to generate the data for the table of contents.

Figure F: We modified the report that the Report Wizard created.

A list of customers by region

To build the list of customers, switch back to the Database window and select tblCustomers. Use the steps we outlined above to open the Report Wizard. Place the LName, FName, and Region fields on the report and click Next. Choose to group the report by Region, and then click Next again. Select LName from the dropdown box to sort each group by the customers' last names. Click Next three more times. Name the report rptCustomers, select Modify The Report's Design, and click Finish to exit the Report Wizard. Access builds the new report, as shown in Figure G. We'll also make a few adjustments to this report before we delve into VBA.

Figure G: When you've finished building the report with the Report Wizard, the Design view should look like ours.

First, change the report's title label from rptCustomers to Customer Report by Region. Next, click on the ruler to the left of the Region textbox and drag the cursor down into the Detail section past the other two controls. When you release the mouse button, Access selects the Region, LName, and FName controls. Increase their Font Size to 12, then click the Bold button (btnBold) on the toolbar. Resize the controls to accommodate the larger font size. Now, to better illustrate our table of contents, let's expand the Detail section so that our customer list extends over several pages. To do so, grab the Page Footer section bar and drag it down an inch. When you've finished, the report's Design view should look similar to the one shown in Figure H.

Figure H: We expanded the report's Detail section to better illustrate the Table of Contents technique.

Now, as a further modification, let's make each Region start on a new report page. To do so, click the Sorting and Grouping button (btnSort) on the toolbar. When Access displays the Sorting And Grouping dialog box, change the Region's Group Footer property from No to Yes. When you do so, Access creates a new Region footer. Next, close the dialog box and return to Design view. Double-click on the Region Footer section bar that we just created to open its Properties sheet. Change the Force New Page property from None to After Section. Now Access will start a new page after each Region. At this point, we've completed our report's design. After you save it, we'll add the code that generates the TOC data.

The TOC technique's three parts

The code we'll add next to the customer list report accomplishes three main tasks. First, it uses qryDelContents to delete any existing data in the tblContents table. As previously stated, we do this because page numbers will change as the number of records in the database decreases or increases. As its second step, the technique appends a new record to tblContents, consisting of the region and the page number, whenever Access formats the Region Group Header. Lastly, the code opens the Table of Contents report whenever we view or print the customer list report's last page.

Attach the code

To add the VBA command that executes the qryDelContents query, while still in rptCustomer's Design view, click the Code button (btnCode). When you do, Access opens the report's Module window. We'll run qryDelContents in the report's Open event. Select Report from the Object (upper-left) dropdown box. Access automatically inserts the beginning and ending Sub statements for the Open event, which is exactly what we want. At the insertion point, enter

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDelContents"
DoCmd.SetWarnings True

Dim mySql As String
mySql = "Insert Into tblContents Values ('" _
	& Region & "', " & Page & ")"

DoCmd.SetWarnings False
DoCmd.RunSQL mySql
DoCmd.SetWarnings True

Finally, we'll add the VBA commands to open the table of contents. To do so, select PageFooter from the Object dropdown list. By default, Access inserts Sub statements for the Format event. We want to use the Print event, though, so select Print from the Procedure dropdown list. Enter the remaining code from Listing A.

Listing A: The PageFooter Print event

Private Sub PageFooter_Print(Cancel As Integer, FormatCount As Integer) 
If Page = Pages Then

	'Check to see if Table of Contents is 
	`already open
	If SysCmd(acSysCmdGetObjectState, acReport, "Table of Contents") Then
		` If so, then close it.
			DoCmd.Close acReport, "Table of Contents"
	End If

	DoCmd.OpenReport "Table of Contents", acViewPreview

End If

That's it! Click the Save button to save all of your hard work. Then, close the Module window and you'll be ready to see the code and the table of contents in action.

Our report and its contents

At this point, click the Print Preview button. When you do, Access opens the report, which triggers the Open event. This event, in turn, executes the qryDelContents Delete query which clears out the tblContents table and prepares it to receive new page information.

As Access formats each Region's Group Header section, it uses the SQL string, mySQL, to insert the current region and the page number into tblContents. Access may or may not format a group section more than once. Normally, this would result in the same region being appended to tblContents twice. However, the primary key we set up in tblContents prevents this from happening. Access simply rejects any duplicate record that the code tries to append.

When Access prints the last page, whether to a printer or in preview mode, VBA uses the SysCmd command to determine the status of the Table of Contents report. If it's already open, we want it to reflect the latest changes, so the code closes it. As it's final step, the procedure opens the Table of Contents report with the latest page numbers.

Minor adjustments

You could easily create a macro to run qryDelContents. However, to keep things consistent, we chose to use VBA in our example. We also used the built-in acPreview constant in the DoCmd.OpenReport statement to preview Table of Contents. If you want Access to automatically print the TOC instead, delete the acPreview constant so that the DoCmd statement reads as follows:

DoCmd.OpenReport "Table of Contents"

Conclusion

A table of contents page is a great addition to any report. It provides a concise outline for lengthy reports. Previously, however, the procedure for creating one involved complicated DAO coding. In this article, we've shown you a much easier way to create these handy report additions.

Copyright © 1999, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.