Subreports with blank pages

[Gary]

I have an unbound main report that contains four unrelated subreports.
Sometimes there is no data for one of the subreports depending on the
criteria the user enters. When I preview the main report, Access displays
(and prints) a blank page for the subreport that has no data. Is there a way
to get access to not display (or print) the blank page and skip the subreport
if there is no data?

 

[Marshall Barton]

I have an unbound main report that contains four unrelated subreports.
Sometimes there is no data for one of the subreports depending on the
criteria the user enters. When I preview the main report, Access displays
(and prints) a blank page for the subreport that has no data. Is there a way
to get access to not display (or print) the blank page and skip the subreport
if there is no data?

Sure, but we need to know what you are doing to get each
subreport on a separate page.

Whatever, it is the key is to suppress it by checking each
subreport's HasData property.

 

[Gary]

Sure, but we need to know what you are doing to get each
subreport on a separate page.

Whatever, it is the key is to suppress it by checking each
subreport's HasData property.

I created a main report and used four groupings using the formula =1 to
create for group footers. I then placed each seperate subreport in one of
the group footers. There are now four group footers, each footer containing
one subreport. If I go under the properties for each subreport, I have
located a property field called On No Data. Do I need to write an
expression to get the main report to skip the subreport if the subreport is
blank?

 

[Marshall Barton]

I created a main report and used four groupings using the formula =1 to
create for group footers. I then placed each seperate subreport in one of
the group footers. There are now four group footers, each footer containing
one subreport. If I go under the properties for each subreport, I have
located a property field called On No Data. Do I need to write an
expression to get the main report to skip the subreport if the subreport is
blank?

No. I don't see how the NoData event will help here.

You still haven't explained what you have done to get each
section's subreport on a new page. Since you are putting
each subreport in a separate section, I will guess that you
are using the section's ForceNewPage property (instead of
using PageBreak controls).

In that case, you can cancel the entire section containing a
subreport by adding this line to each section's Format
event:
Cancel = Not Me.subreportcontrol.Report.HasData

 

[Gary]

I created a main report and used four groupings using the formula =1 to
create for group footers. I then placed each seperate subreport in one of
the group footers. There are now four group footers, each footer containing
one subreport. If I go under the properties for each subreport, I have
located a property field called On No Data. Do I need to write an
expression to get the main report to skip the subreport if the subreport is
blank?

No. I don't see how the NoData event will help here.

You still haven't explained what you have done to get each
section's subreport on a new page. Since you are putting
each subreport in a separate section, I will guess that you
are using the section's ForceNewPage property (instead of
using PageBreak controls).

In that case, you can cancel the entire section containing a
subreport by adding this line to each section's Format
event:
Cancel = Not Me.subreportcontrol.Report.HasData

--
Marsh
MVP [MS Access]

Please understand that I am not a professional computer programer, I am learning as I go. Your guess was correct, I am using the ForceNewPage property, and have it set to After Section so a new page is started when Access moves on to the next subreport. I am currently going into the OnFormat property and using the code builder in an attempt to use the Visual basic formula you included in your previous response. Since I am still a novice, I do not understand the "Not Me.subreportcontrol" section of the code. Is the "Not Me" section the actual subreport itselt? If so, how do I get visual basic to recognize the subreport?

 

[Marshall Barton]

programer, I am learning as I go. Your guess was correct,
I am using the ForceNewPage property, and have it set to
After Section so a new page is started when Access moves
on to the next subreport. I am currently going into the
OnFormat property and using the code builder in an attempt
to use the Visual basic formula you included in your previous
response. Since I am still a novice, I do not understand the
"Not Me.subreportcontrol" section of the code. Is the "Not Me"
section the actual subreport itselt? If so, how do I get visual
basic to recognize the subreport?

Not is a logical operator (like And and Or), it just changes
True to False and vice-versa.

Me refers to the report's code module and is only used to
indicate that the next term (subreportcontrol) is a control
on the report instead of a VBA variable. It is not
essential to use Me, but it is a good programming practice.

subreportcontrol is just a place holder I used to indicate
where you should put the name of the subreport **control**.
Note that the name of the subreport control might be
different from the name of the report object it will
display.

The Report property returns the report object in the
subreport control. This is necessary so you can refer to
the properties (HasData) of the actual report instance
(instead of the properties of the control).

All you need to do is Copy/Paste my suggested line of code
into the section's Format event procedure and replace the
subreportcontrol term with the name of the subreport control
in the section.

 

[Gary]

Not is a logical operator (like And and Or), it just changes
True to False and vice-versa.

Me refers to the report's code module and is only used to
indicate that the next term (subreportcontrol) is a control
on the report instead of a VBA variable. It is not
essential to use Me, but it is a good programming practice.

subreportcontrol is just a place holder I used to indicate
where you should put the name of the subreport **control**.
Note that the name of the subreport control might be
different from the name of the report object it will
display.

The Report property returns the report object in the
subreport control. This is necessary so you can refer to
the properties (HasData) of the actual report instance
(instead of the properties of the control).

All you need to do is Copy/Paste my suggested line of code
into the section's Format event procedure and replace the
subreportcontrol term with the name of the subreport control
in the section.

Thank you for the explanation. I used the formula in the format event
procedure in all for group footers. When I was typing the formulas a drop
down box appeared and I believe I have selected the correct controls. How do
I confirm that I have the correct controls? Is there a way to quickly lookup
this information in Access? The formula seems to work if the second and
third subreports contain no information, but I am still getting blank pages
if the first or fourth (last) subreport contain no information. Do I have to
change an additional function?

 

[Gary]

Thank you for the explanation. I used the formula in the format event
procedure in all for group footers. When I was typing the formulas a drop
down box appeared and I believe I have selected the correct controls. How do
I confirm that I have the correct controls? Is there a way to quickly lookup
this information in Access? The formula seems to work if the second and
third subreports contain no information, but I am still getting blank pages
if the first or fourth (last) subreport contain no information. Do I have to
change an additional function?

Please disregard my previous questions. The problem was not with the
formula, buth rather with the height of each section. I had enlarged each
footer to roughly 1.5 while I was working on the master report. Once I
shorted each footer in the design view, the problem resolved itself. Thank
you for your assistance.