Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 3.1 Create a Report with Line Numbers

3.1.1 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?

3.1.2 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

2. For the sample report, we named the control txtLineNumber.

3. 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).

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

4. 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.

Figure 3-2. The rptEvidenceByCase report includes line numbers

3.1.3 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.