counting records in a subreport

[lallen]

I have a report which has a subreport in the detail section. In the report
footer, I would like to print a count of all records printed in the
subreport. To do this, I created a variable which I increment in the Print
Event of the detail section of the subreport. I then print this variable in
the report footer. This works fine in print preview, and when I print the
whole report, but if I print only the last page, I get a count of only those
records on the last page. Also, if I print multiple copies, the count
increases on each copy. How do I get the report to count all the records,
only once?

 

[Marshall Barton]

I have a report which has a subreport in the detail section. In the report
footer, I would like to print a count of all records printed in the
subreport. To do this, I created a variable which I increment in the Print
Event of the detail section of the subreport. I then print this variable in
the report footer. This works fine in print preview, and when I print the
whole report, but if I print only the last page, I get a count of only those
records on the last page. Also, if I print multiple copies, the count
increases on each copy. How do I get the report to count all the records,
only once?

You can not use VBA code in an event procedure to count (or
accumulate any value) across multiple details.

The simplest way to count hthe details is to use a text box
in the report header/footer section with the expression
=Count(*)

If that isn't what you want, you coul try using a text box
in the detail section with its ControlSource set to =1 and
RunningSum set to Over All. The text box in the footer can
display the total count by referring to the running sum text
box.

 

[lallen]

Thanks Marshall. Unfortunately, neither of your suggestions will work, since
the data are grouped, and the sub-report is in the detail section.

I am going to look a t the possibility of removing the sub-report. I didn't
create this report, so I'm not sure why it was done this way.

 

[Marshall Barton]

Removing an unnecessary subreport is always a good thing.
However there's probably(?) a reason for it to be done that
way.

Actually, you can use a combination of both of my
suggestions to get the desired result. Use a text box
(named txtCount) in the subreport's header/footer section
with =Count(*) to count the details in each instance of the
subreport.

The main report's detail section can then use a text box
(named txtRunCnt) with expression:
=subreport.Report.txtCount
and RunningSum set to Over All. If there's a chance that an
instance of the subreport might not have any records, that
expression should be expanded to:
=IIf(subreport.Report.HasData, subreport.Report.txtCount, 0)

Finally, the main report footer text box can display the
total of all the subreport details with =txtRunCnt

 

[lallen]

Thanks Marshall. That works like a charm.

Removing an unnecessary subreport is always a good thing.
However there's probably(?) a reason for it to be done that
way.

Actually, you can use a combination of both of my
suggestions to get the desired result. Use a text box
(named txtCount) in the subreport's header/footer section
with =Count(*) to count the details in each instance of the
subreport.

The main report's detail section can then use a text box
(named txtRunCnt) with expression:
=subreport.Report.txtCount
and RunningSum set to Over All. If there's a chance that an
instance of the subreport might not have any records, that
expression should be expanded to:
=IIf(subreport.Report.HasData, subreport.Report.txtCount, 0)

Finally, the main report footer text box can display the
total of all the subreport details with =txtRunCnt
--
Marsh
MVP [MS Access]

Thanks Marshall. Unfortunately, neither of your suggestions will work, since
the data are grouped, and the sub-report is in the detail section.

I am going to look a t the possibility of removing the sub-report. I didn't
create this report, so I'm not sure why it was done this way.