In this installment of his ongoing series, Dave looks at the part that reports play in developing your application. In addition to discussing the issues around development, this article looks at the most effective way to build your reports and some useful (though overlooked) properties.
Don't forget that properties exist not just for data objects, but for all objects. One common error that that newer users fall into is to set the Force New Page property on a group header to Break Before Section. This can lead to the Page Header being all alone on a page by itself as you get a page break before the first section prints. This problem can be eliminated by creating a group footer, right-clicking on the footer bar, selecting Properties, and setting the Force New Page property to Break After Section (see Figure 8). I sometimes find myself adding a group footer, not to display any data, but just to get the page breaks that I want.
That's a quick introduction to Access reporting. In this installment, I've touched on what makes a good report and given you some tips for designing effective reports. The key to remember when creating reports is that you want to provide useful, timely information to your users. A report that isn't read in its entirety is a waste of processing time and paper, so you want to design reports that directly meet your users' needs. And, of course, by checking for Garbage Out early in your development process, you can prevent Garbage In later on.
Your download file is called Irvine_Great_report.accdb and you can download it here Reports and Graphs>>
Sidebar: A Complete Access Application
This series of articles explores the creation of a development time management database, which can be used for tracking software development. The system's users can track program development, enter time spent on various tasks, record project "to do's" and system modifications, and then, to top it all off, feed the project information into MS Project to keep that important project plan up to date. However, while the application is certainly interesting, the focus of each article will be on using the tools that come with Microsoft Access, including database design, security, replication, converting to Access 2000, and upsizing to SQL Server.
Sidebar: A Two-Pass Report Writer
by Peter Vogel
Normally Access makes only one pass through your report, formatting each section and then printing it. As Access lays out a section of your report, it fires the Format event for the section. You can place code, for instance, in the report's Detail1_Format section to alter the contents of the Detail section before it's printed.
However, if you use the report's Pages property, Access turns into a "two-pass report writer." The Pages property contains the total number of pages that will be in the printed report. In order to determine this number, Access must format the entire report to figure out the number of pages that will be in the report. As Access does this, it fires Format events for each section in the report. With the whole report formatted and a value for the Pages property calculated, Access returns to the start of the report and processes it as normal, firing a Format event followed by a Print event for each section.
Usually this process is only interesting if you add the Pages property to your report and it suddenly takes much longer to print the first page of your report. However, in the Access Compuserve forum, there's a very neat report submitted by Donald E. Reid that uses this functionality to print a custom page number label on each page of the report.
In his report (which can be found in KOOLRP.ZIP in the Smart Access Bronze downloads), as Access makes its first pass through the report, Donald has code in a GroupHeader_Format event that stores what page the group header will print on. On the second pass through the report, Donald uses this information to print out on each page how many pages are used by the current group and what page number within that group is being printed.
For instance, the report begins by printing out four pages of sales to Argentina. The first page has a label that this page is "Page 1 of 4." After four pages of information about Argentina (labeled "Page 2 of 4," "Page 3 of 4," and so on), the report moves on to print information about Brazil. The first page of this section is labeled "Page 1 of 3" and so forth, through all of the sections of the report.
By storing the start of each section during the first pass, Donald calculates the number of pages in a section by calculating the difference between the start page of the current section and the next section. Determining what page is currently being printed is simply a matter of subtracting the current page from the start page of the section. The system isn't perfect, as Donald notes in his comments. If you open the report in Print Preview and page backward and forward in the report, the page numbering gets confused. However, if you just send the report to print, it works fine.
All in all, the report is a neat piece of coding that exploits a little-known feature of the Access report writer. The sample database that Donald has provided also includes a report that begins by printing a table of contents of the sections that it contains. I've been unable to contact Donald, but I'd like to thank him for sharing some neat code with the Access community.
Other Articles That Might Interest You