A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Hide Sub Report(s) when records are null



 
 
Thread Tools Display Modes
  #1  
Old May 24th, 2010, 03:38 AM posted to microsoft.public.access.reports
Bruce
external usenet poster
 
Posts: 374
Default Hide Sub Report(s) when records are null

I am running Access 2007 sp2 MSO. I have a report that has three (3) sub
reports. There are times when not all three sub reports have values to
display. Is there a way to code the given report to not be visible if there
are no records to report? The second part of this question would be if there
is a way to make the report invisible is it also possible to code so that the
space where the report would normally reside would not be taken with a blank
space (the foot print of where the report normally resides)?
--
Bruce
  #2  
Old May 24th, 2010, 04:14 AM posted to microsoft.public.access.reports
Allen Browne
external usenet poster
 
Posts: 11,706
Default Hide Sub Report(s) when records are null

Part 1:
If the main report is bound to a table/query, you can just cancel its NoData
event.

If the main report is unbound (merely a holder for the 3 subreports), you
would need to write some code in the Open event of the report to DLookup()
the 3 source queries for the 3 subreports to see if you get anything. If you
don't, then cancel Report_Open.

Part 2:
Set the CanShrink property to Yes for the subreport control, and also for
the section that holds the subreport.

Provided there is nothing else overlapping the subreport's vertical space,
it will then shrink if it has no data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Bruce" wrote in message
...
I am running Access 2007 sp2 MSO. I have a report that has three (3) sub
reports. There are times when not all three sub reports have values to
display. Is there a way to code the given report to not be visible if
there
are no records to report? The second part of this question would be if
there
is a way to make the report invisible is it also possible to code so that
the
space where the report would normally reside would not be taken with a
blank
space (the foot print of where the report normally resides)?
--
Bruce


  #3  
Old May 24th, 2010, 01:58 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Hide Sub Report(s) when records are null

You can use the HasData property of the sub-reports to control the visibility
of the sub-report control. In addition, you can set the can shrink property
of the section containing the sub-report control(s) to yes to recover the space.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Allen Browne wrote:
Part 1:
If the main report is bound to a table/query, you can just cancel its
NoData event.

If the main report is unbound (merely a holder for the 3 subreports),
you would need to write some code in the Open event of the report to
DLookup() the 3 source queries for the 3 subreports to see if you get
anything. If you don't, then cancel Report_Open.

Part 2:
Set the CanShrink property to Yes for the subreport control, and also
for the section that holds the subreport.

Provided there is nothing else overlapping the subreport's vertical
space, it will then shrink if it has no data.

  #4  
Old May 24th, 2010, 03:09 PM posted to microsoft.public.access.reports
Bruce
external usenet poster
 
Posts: 374
Default Hide Sub Report(s) when records are null

John,

I cannot locate where I would use the HasData property of the sub-report. I
am aware of the can shrink property and have that set to yes (also have the
can grow property set to yes). I have looked at each line in the properties
window but cannot locate where I set the HasData property. In fact I typed in
HasData into the help menu and there were no results found. Could you please
give me a little more assistance?
--
Bruce


"John Spencer" wrote:

You can use the HasData property of the sub-reports to control the visibility
of the sub-report control. In addition, you can set the can shrink property
of the section containing the sub-report control(s) to yes to recover the space.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Allen Browne wrote:
Part 1:
If the main report is bound to a table/query, you can just cancel its
NoData event.

If the main report is unbound (merely a holder for the 3 subreports),
you would need to write some code in the Open event of the report to
DLookup() the 3 source queries for the 3 subreports to see if you get
anything. If you don't, then cancel Report_Open.

Part 2:
Set the CanShrink property to Yes for the subreport control, and also
for the section that holds the subreport.

Provided there is nothing else overlapping the subreport's vertical
space, it will then shrink if it has no data.

.

  #5  
Old May 24th, 2010, 03:11 PM posted to microsoft.public.access.reports
Bruce
external usenet poster
 
Posts: 374
Default Hide Sub Report(s) when records are null

Allen,

The main report is bound to a query. In fact the three (3) other sub-reports
are bound to the same query. I have the sub-report can shrink set to yes for
both the space it occupies and the report itself.
--
Bruce


"Allen Browne" wrote:

Part 1:
If the main report is bound to a table/query, you can just cancel its NoData
event.

If the main report is unbound (merely a holder for the 3 subreports), you
would need to write some code in the Open event of the report to DLookup()
the 3 source queries for the 3 subreports to see if you get anything. If you
don't, then cancel Report_Open.

Part 2:
Set the CanShrink property to Yes for the subreport control, and also for
the section that holds the subreport.

Provided there is nothing else overlapping the subreport's vertical space,
it will then shrink if it has no data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Bruce" wrote in message
...
I am running Access 2007 sp2 MSO. I have a report that has three (3) sub
reports. There are times when not all three sub reports have values to
display. Is there a way to code the given report to not be visible if
there
are no records to report? The second part of this question would be if
there
is a way to make the report invisible is it also possible to code so that
the
space where the report would normally reside would not be taken with a
blank
space (the foot print of where the report normally resides)?
--
Bruce


.

  #6  
Old May 24th, 2010, 04:40 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Hide Sub Report(s) when records are null

In the section of the report that has the subreport, you would need some VBA
code like the following in that sections format event

IF NameOfSubReportControl.HasData Then
NameOfSubReportControl.Visible = True
ELSE
NameOfSubReportControl.Visible = False
END IF

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Bruce wrote:
John,

I cannot locate where I would use the HasData property of the sub-report. I
am aware of the can shrink property and have that set to yes (also have the
can grow property set to yes). I have looked at each line in the properties
window but cannot locate where I set the HasData property. In fact I typed in
HasData into the help menu and there were no results found. Could you please
give me a little more assistance?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.