Subreport with no records

  • Thread starter Thread starter Darrell
  • Start date Start date
D

Darrell

I have a main report whose role in life is to contain 3 subreports and
display totals for each of these subreports and grand totals summing all
3. There are page break controls forcing page breaks between each subreport.

So, the structure is something like this:

MAIN REPORT
Main Report Header
Subtotal for subreport 1: ___
Subtotal for subreport 2: ___
Subtotal for subreport 3: ___

Grand Total: ___

SUBREPORT 1
page break
SUBREPORT 2
page break
SUBREPORT 3

Users can specify criteria which restrict the rows in the respective
record sources for all the reports. My problem arises when the user
selects criteria which returns no records for at least 1 of the
subreports. This results in 2 problems.

First, the affected subreport simply does not show up on the report at
all, leaving only a blank page forced by the page break control. Second,
both the subtotal control and the grand total control on the main form
are blank, like this...

MAIN REPORT
Main Report Header
Subtotal for subreport 1: ___
Subtotal for subreport 2: ___
Subtotal for subreport 3: Blank

Grand Total: Blank

SUBREPORT 1
page break
SUBREPORT 2
page break

Can someone help me with a conceptual approach to this? I need to have
the page break be somehow rendered ineffective and the total controls on
the main report show zero rather than blank. The NoData event for the
affected subreport does not fire.

Thanks in advance for any and all help.

Darrell
 
Darrell said:
I have a main report whose role in life is to contain 3 subreports and
display totals for each of these subreports and grand totals summing all
3. There are page break controls forcing page breaks between each subreport.

So, the structure is something like this:

MAIN REPORT
Main Report Header
Subtotal for subreport 1: ___
Subtotal for subreport 2: ___
Subtotal for subreport 3: ___

Grand Total: ___

SUBREPORT 1
page break
SUBREPORT 2
page break
SUBREPORT 3

Users can specify criteria which restrict the rows in the respective
record sources for all the reports. My problem arises when the user
selects criteria which returns no records for at least 1 of the
subreports. This results in 2 problems.

First, the affected subreport simply does not show up on the report at
all, leaving only a blank page forced by the page break control. Second,
both the subtotal control and the grand total control on the main form
are blank, like this...

MAIN REPORT
Main Report Header
Subtotal for subreport 1: ___
Subtotal for subreport 2: ___
Subtotal for subreport 3: Blank

Grand Total: Blank

SUBREPORT 1
page break
SUBREPORT 2
page break

Can someone help me with a conceptual approach to this? I need to have
the page break be somehow rendered ineffective and the total controls on
the main report show zero rather than blank. The NoData event for the
affected subreport does not fire.
Click to expand...


Check VBA Help on the HasData property.

You can use/suppress the page break control for subreportX
by using a line of code in the the section's Format event
procedure:

Me.pgBreakX.Visible = Me.subreportX.Report.HasData

The subtotal lines can display a zero instead of nothing by
using an expression in the subtotalX text box:


=IIf(subreportX.Report.HasData,subreportX.Report.totaltextbox,0)

The grand total text box expession should then be just:
=subtotal1+subtotal2+subtotal3
 
Marshall said:
Check VBA Help on the HasData property.

You can use/suppress the page break control for subreportX
by using a line of code in the the section's Format event
procedure:

Me.pgBreakX.Visible = Me.subreportX.Report.HasData

The subtotal lines can display a zero instead of nothing by
using an expression in the subtotalX text box:


=IIf(subreportX.Report.HasData,subreportX.Report.totaltextbox,0)

The grand total text box expession should then be just:
=subtotal1+subtotal2+subtotal3
Click to expand...
Excellent! Thank you! I believe you have addressed all of my issues. I
will now go apply these and let you know my success rate.

Thank you again,

Darrell
 
Marshall said:
Check VBA Help on the HasData property.

You can use/suppress the page break control for subreportX
by using a line of code in the the section's Format event
procedure:

Me.pgBreakX.Visible = Me.subreportX.Report.HasData

The subtotal lines can display a zero instead of nothing by
using an expression in the subtotalX text box:


=IIf(subreportX.Report.HasData,subreportX.Report.totaltextbox,0)

The grand total text box expession should then be just:
=subtotal1+subtotal2+subtotal3
Click to expand...
Hmmm... the Me.pgBreakX.Visible = Me.subreportX.Report.HasData executes
flawlessly and the visible property of the page break is dutifully set
to false. But... it does not seem to have the desired effect. There is
still a blank page for the subreport which has no data and thus does not
appear at all.

The expression
"=IIf(subreportX.Report.HasData,subreportX.Report.totaltextbox,0)" seems
to work perfectly.
 
Darrell said:
Hmmm... the Me.pgBreakX.Visible = Me.subreportX.Report.HasData executes
flawlessly and the visible property of the page break is dutifully set
to false. But... it does not seem to have the desired effect. There is
still a blank page for the subreport which has no data and thus does not
appear at all.
Click to expand...


The blanl page is not coming from a subreport with no data.
There may be something else on the main report such as
another page break or a section with its ForceNewPage
property set.

Also check the ForceNewPage property in each section in all
the subreports.
 
Marshall said:
The blanl page is not coming from a subreport with no data.
There may be something else on the main report such as
another page break or a section with its ForceNewPage
property set.

Also check the ForceNewPage property in each section in all
the subreports.
Click to expand...
I give... All ForceNewPage settings are None in both the main report and
the subreport. Ahhh, wait... I just tightened up the spacing between the
subreports and the page break controls (there was perhaps a tenth of an
inch between each) and the page break went away. Not sure I understand
exactly why that spacing would make the difference since the previous
subreport ended close to an inch before the end of its last physical
page, but... it works. Thanks.

Now, I noticed one other anomaly since my first reply. The subtotal on
the main report for the last (3rd) subreport is not matching the total
on the subreport. For one set of data, the numbers are 4 on the
subreport and 1 on the main report. For another, the numbers are 31 and
26 respectively.

Sorry. I'm just stumped. The structure for arriving at both the total on
the subreport and the corresponding subtotal on the main report is
identical to that on the first and second subreports, and they seem to
be working perfectly. So, I don't know where to look for solutions.
 
Darrell said:
I just tightened up the spacing between the
subreports and the page break controls (there was perhaps a tenth of an
inch between each) and the page break went away. Not sure I understand
exactly why that spacing would make the difference since the previous
subreport ended close to an inch before the end of its last physical
page, but... it works.
Click to expand...

Also make sure that there is no blank space both before and
after each subreport. I.e. each page break has the same
value in the Top property as its associated subreport and
the Top plus Height of the previous subreport. The bottom
of the section also needs to be up tight against the bottom
of the third subreport.
Now, I noticed one other anomaly since my first reply. The subtotal on
the main report for the last (3rd) subreport is not matching the total
on the subreport. For one set of data, the numbers are 4 on the
subreport and 1 on the main report. For another, the numbers are 31 and
26 respectively.
Click to expand...

The main report **must** reference the subreport's Report
Footer section text box. The result you are seeing implies
that you are using a running sum text box in the detail
section and the main report is referencing the running sum
text box instead of the report footer text box that displays
the total.

If a subreport is calculating its total using an expression
like =Sum(...), then the total text box can be in either the
subreport's Report Header or Footer section.
 
Marshall said:
Also make sure that there is no blank space both before and
after each subreport. I.e. each page break has the same
value in the Top property as its associated subreport and
the Top plus Height of the previous subreport. The bottom
of the section also needs to be up tight against the bottom
of the third subreport.


The main report **must** reference the subreport's Report
Footer section text box. The result you are seeing implies
that you are using a running sum text box in the detail
section and the main report is referencing the running sum
text box instead of the report footer text box that displays
the total.

If a subreport is calculating its total using an expression
like =Sum(...), then the total text box can be in either the
subreport's Report Header or Footer section.
Click to expand...
Marsh,

Sorry about the delay in re-engaging. I'm doing this part time after a
full time job and I don't always get back to the group as quickly as I'd
like.

The reference chain goes like this:
There is a column in the subreport record source whose value is simply
1. Then, there is a text box in a group footer whose control source is
that column and whose Running Sum property is set to Over All. Next is a
text box in the subreport footer section whose control source is set to
the name of the group footer text box and whose Running Sum property is
set to No. Finally, there is a text box in the main report header
section whose control source is set to the text box in the subreport's
footer (this text box's Running Sum is also set to No).

The strange thing is that this exact setup works for subreports 1 and 2.

Darrell
 
Darrell said:
Sorry about the delay in re-engaging. I'm doing this part time after a
full time job and I don't always get back to the group as quickly as I'd
like.

The reference chain goes like this:
There is a column in the subreport record source whose value is simply
1. Then, there is a text box in a group footer whose control source is
that column and whose Running Sum property is set to Over All. Next is a
text box in the subreport footer section whose control source is set to
the name of the group footer text box and whose Running Sum property is
set to No. Finally, there is a text box in the main report header
section whose control source is set to the text box in the subreport's
footer (this text box's Running Sum is also set to No).

The strange thing is that this exact setup works for subreports 1 and 2.
Click to expand...


Sorry about the response lag time. Someone/something
hijacked my ISP account and I've been locked out of
email/newsgroups.

Your subreport arrangement sounds logical and appears to be
working so I'm pretty sure the issue is in the main report.

I don't see how the main report's Report Header section can
get the value from a subreport's report footer section
(unless the subreport is also in the report header section).
I would think the main report text box should be in the
section that contains the subreport. Generally, trying to
get a report to display a value before the value is
calculated can be a problem.
 
Back
Top