Chapter 3. Reports

You may devote days, weeks, or even months of work to designing tables and queries and writing the macros and code to put an application together, but along with your application’s forms, its reports are the application. Because of this, you’ll want make them as clear and attractive as possible.

The first solution in this chapter shows you how to do something that should be (and is) easy: printing a report with line numbers. Next, you’ll learn how to print the value of query parameters on a report based on a parameter query and how to create an attractive multiple-column report.

The next group of solutions will teach you how to use Visual Basic for Applications (VBA) code and macros to print a message on a report only if certain conditions are met, how to create telephone-book-style page-range indicators, how to print a bar graph on a report using rectangle controls, and how to calculate page totals.

Next, you’ll employ more challenging VBA code to work around the limitations of the CanGrow/CanShrink properties and prevent blank rows on reports by combining an entire address into a single expression for a mailing-label report. You’ll see how to suppress printing a report if there are no records to print. Using an event procedure run from the report’s Format event, you’ll learn how to print one set of headers and footers on odd pages and another (mirror-image) set on even pages. Then you will learn how to use the Line method to draw lines or rectangles on a report—in this case, to make a line the same height as a variable-height text box. Next, you’ll learn how to alternate gray bars on every other row of the report.

The final three solutions in this chapter show you how to tie a report’s recordset to the filtered recordset of a form, how to prevent your report from breaking at an inappropriate place (such as right after a group header), and finally, in the most complex solution in this chapter, how to modify a report’s grouping and sorting fields on the fly.

Create a Report with Line Numbers

Problem

You have a legal report that has a list of items in the detail section. You’re required to sequentially number each item in the list. You thought about using an AutoNumber field, but this won’t work because you want the number to reset itself for each group and you often want to print the items in a different order from how you entered them. Is there an easy way to create on the fly report line numbers that pertain only to the data printed on the report?

Solution

Yes, there is an easy way to do this that makes use of an underused property of a text box, RunningSum. This solution shows you how to add line numbers to your report by creating an unbound text box based on a simple calculation and adjusting the RunningSum property of this control.

To create line numbers on your own reports, follow these steps:

  1. Create a new report or open an existing report in design mode. Add an unbound text box control to the detail section with the following ControlSource setting:

    =1

    For the sample report, we named the control txtLineNo.

  2. Change the RunningSum property for the control from the default of No to either Over Group or Over All. We chose Over Group for the sample report (see Figure 3-1).

    The RunningSum property can be set to No, Over Group, or Over All

    Figure 3-1. The RunningSum property can be set to No, Over Group, or Over All

  3. Save the report and preview it to confirm that it now includes sequential line numbers.

To see an example of this solution, open 03-01.MDB. Run the rptEvidenceByCase report in preview view (see Figure 3-2). This report prints out a list of all evidence items, grouped by CaseId. Notice the line number field on the left side of the report, which resets to zero at the start of each group.

The rptEvidenceByCase report includes line numbers

Figure 3-2. The rptEvidenceByCase report includes line numbers

Discussion

Setting the ControlSource of the line number control to =1 tells Access to print a constant of 1 for all records. This is what would happen if you didn’t also adjust the RunningSum property of the control.

Setting the RunningSum property to Over Group or Over All tells Access to print the value of the first record as it would normally (in this case, to print 1) but, for the second record, to take the value of the first record and add it to the value of the second record, printing the cumulative total instead of the value it would normally print (in this case, 2). For the third record, Access adds the value of the second record (which is really a sum of the first and second records’ values) to the value of the third record (in this case, 3). This accumulation of values continues until the end of the report (if you set RunningSum to Over All) or until the beginning of the next group (if you set RunningSum to Over Group).

You can use RunningSum to accumulate nonconstant values, too. For example, if you want a running total of the weight of evidence items in the rptEvidenceByCase report for each record, you can add a second Weight text box control to the right of the existing Weight control, making the second control identical to the first but this time setting RunningSum to Over Group. You’ll also find the RunningSum property useful for financial reports for which you’d like to include a cumulative year-to-date column.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.149.214.32