How to fix empy space on a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report with multiple subreports on them. All in all its about 3
pages. Some of the sub reports are blank sometimes because there is no data
these are at the end of the main report. So even if they are blank it still
creates a blank end page.

The question that I had is, is there a way to have the last page not show up
if there is no data in the subreports.
 
hi!

Someone just answered this one for me last week so I'll pass on what I
learned:
Right click the Detail section (or wherever the subreport is placed),
Properties, Events tab, OnFormat event, Code builder, use code similar to
this:
Me.subreportName.Report.Visible= Me.subreportName.Report.HasData

The subreport will only take up space if it has data, and this should
eliminate the blank pages at the end (or gaps in the report, in my case)
 
Perfect. Thanks

SusanV said:
hi!

Someone just answered this one for me last week so I'll pass on what I
learned:
Right click the Detail section (or wherever the subreport is placed),
Properties, Events tab, OnFormat event, Code builder, use code similar to
this:
Me.subreportName.Report.Visible= Me.subreportName.Report.HasData

The subreport will only take up space if it has data, and this should
eliminate the blank pages at the end (or gaps in the report, in my case)
Click to expand...
 
pokdbz said:
I have a report with multiple subreports on them. All in all its about 3
pages. Some of the sub reports are blank sometimes because there is no data
these are at the end of the main report. So even if they are blank it still
creates a blank end page.

The question that I had is, is there a way to have the last page not show up
if there is no data in the subreports.
Click to expand...


Generally, the subreport should be filtered by using the
Link Master/Child properties.

To answer you're question, the only place where you can set
a report's record source at runtime is in that report's Open
event. In the case of a subreport, it can only be done the
first time the subreport's Open event fires.
 
What was suggested didn't work for me I still have a blank page at the end.
I put the code in the OnFormat event on the main form. Any other suggestions?
 
Did you put it in the On Format of the SECTION where the subreport is
embedded? If the subreport is in a footer for example, you would put it in
the footer's on format. If in the detail, then in the detail sections on
format...
 
Marshall said:
Generally, the subreport should be filtered by using the
Link Master/Child properties.

To answer you're question, the only place where you can set
a report's record source at runtime is in that report's Open
event. In the case of a subreport, it can only be done the
first time the subreport's Open event fires.
Click to expand...


Sorry, somehow this was posted to the wrong thread.
 
Yes I do have the code under the detail section where the subreprorts are.
On all of the subreports I have data coming in like this:
=Switch([PHQ_01]=0,"Not at all",[PHQ_01]=1,"Several days",[PHQ_01]=2,"More
than half of the days",[PHQ_01]=3,"Nearly every day")
Could this affect the problem?

Also here is what I have for the code under the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Adherence_Pull.Report.Visible = Me.Adherence_Pull.Report.HasData
Me.Alcohol_Pull.Report.Visible = Me.Alcohol_Pull.Report.HasData
Me.Concerns_Pull.Report.Visible = Me.Concerns_Pull.Report.HasData
Me.CoreMania_Pull.Report.Visible = Me.CoreMania_Pull.Report.HasData
Me.CoreSuicide_Pull.Report.Visible = Me.CoreSuicide_Pull.Report.HasData
Me.Drug_Pull.Report.Visible = Me.Drug_Pull.Report.HasData
Me.ManiaSet_Pull.Report.Visible = Me.ManiaSet_Pull.Report.HasData
Me.PHQ_Pull.Report.Visible = Me.PHQ_Pull.Report.HasData
Me.Psychosis_Pull.Report.Visible = Me.Psychosis_Pull.Report.HasData
Me.SF_Pull.Report.Visible = Me.SF_Pull.Report.HasData
Me.Smoking_Pull.Report.Visible = Me.Smoking_Pull.Report.HasData
Me.SubstanceSet_Pull.Report.Visible = Me.SubstanceSet_Pull.Report.HasData
Me.SuicideSet_Pull.Report.Visible = Me.SuicideSet_Pull.Report.HasData
End Sub
 
It looks to me as though your record source for your subforms doesn't have a
possible null value - no matter what case your switch pulls up there's a
value?

pokdbz said:
Yes I do have the code under the detail section where the subreprorts are.
On all of the subreports I have data coming in like this:
=Switch([PHQ_01]=0,"Not at all",[PHQ_01]=1,"Several days",[PHQ_01]=2,"More
than half of the days",[PHQ_01]=3,"Nearly every day")
Could this affect the problem?

Also here is what I have for the code under the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Adherence_Pull.Report.Visible = Me.Adherence_Pull.Report.HasData
Me.Alcohol_Pull.Report.Visible = Me.Alcohol_Pull.Report.HasData
Me.Concerns_Pull.Report.Visible = Me.Concerns_Pull.Report.HasData
Me.CoreMania_Pull.Report.Visible = Me.CoreMania_Pull.Report.HasData
Me.CoreSuicide_Pull.Report.Visible = Me.CoreSuicide_Pull.Report.HasData
Me.Drug_Pull.Report.Visible = Me.Drug_Pull.Report.HasData
Me.ManiaSet_Pull.Report.Visible = Me.ManiaSet_Pull.Report.HasData
Me.PHQ_Pull.Report.Visible = Me.PHQ_Pull.Report.HasData
Me.Psychosis_Pull.Report.Visible = Me.Psychosis_Pull.Report.HasData
Me.SF_Pull.Report.Visible = Me.SF_Pull.Report.HasData
Me.Smoking_Pull.Report.Visible = Me.Smoking_Pull.Report.HasData
Me.SubstanceSet_Pull.Report.Visible = Me.SubstanceSet_Pull.Report.HasData
Me.SuicideSet_Pull.Report.Visible = Me.SuicideSet_Pull.Report.HasData
End Sub


pokdbz said:
I have a report with multiple subreports on them. All in all its about 3
pages. Some of the sub reports are blank sometimes because there is no
data
these are at the end of the main report. So even if they are blank it
still
creates a blank end page.

The question that I had is, is there a way to have the last page not show
up
if there is no data in the subreports.
Click to expand...
Click to expand...
 
Subreports - sorry!

SusanV said:
It looks to me as though your record source for your subforms doesn't have
a possible null value - no matter what case your switch pulls up there's a
value?

pokdbz said:
Yes I do have the code under the detail section where the subreprorts
are.
On all of the subreports I have data coming in like this:
=Switch([PHQ_01]=0,"Not at all",[PHQ_01]=1,"Several
days",[PHQ_01]=2,"More
than half of the days",[PHQ_01]=3,"Nearly every day")
Could this affect the problem?

Also here is what I have for the code under the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Adherence_Pull.Report.Visible = Me.Adherence_Pull.Report.HasData
Me.Alcohol_Pull.Report.Visible = Me.Alcohol_Pull.Report.HasData
Me.Concerns_Pull.Report.Visible = Me.Concerns_Pull.Report.HasData
Me.CoreMania_Pull.Report.Visible = Me.CoreMania_Pull.Report.HasData
Me.CoreSuicide_Pull.Report.Visible = Me.CoreSuicide_Pull.Report.HasData
Me.Drug_Pull.Report.Visible = Me.Drug_Pull.Report.HasData
Me.ManiaSet_Pull.Report.Visible = Me.ManiaSet_Pull.Report.HasData
Me.PHQ_Pull.Report.Visible = Me.PHQ_Pull.Report.HasData
Me.Psychosis_Pull.Report.Visible = Me.Psychosis_Pull.Report.HasData
Me.SF_Pull.Report.Visible = Me.SF_Pull.Report.HasData
Me.Smoking_Pull.Report.Visible = Me.Smoking_Pull.Report.HasData
Me.SubstanceSet_Pull.Report.Visible = Me.SubstanceSet_Pull.Report.HasData
Me.SuicideSet_Pull.Report.Visible = Me.SuicideSet_Pull.Report.HasData
End Sub


pokdbz said:
I have a report with multiple subreports on them. All in all its about
3
pages. Some of the sub reports are blank sometimes because there is no
data
these are at the end of the main report. So even if they are blank it
still
creates a blank end page.

The question that I had is, is there a way to have the last page not
show up
if there is no data in the subreports.
Click to expand...
Click to expand...
Click to expand...
 
Ok so I put each statement in the subreports details
How do I change this statement for the subreport:
Me.CoreSuicide_Pull.Report.Visible = Me.CoreSuicide_Pull.Report.HasData
it is giving an error in its current form.

SusanV said:
Subreports - sorry!

SusanV said:
It looks to me as though your record source for your subforms doesn't have
a possible null value - no matter what case your switch pulls up there's a
value?

pokdbz said:
Yes I do have the code under the detail section where the subreprorts
are.
On all of the subreports I have data coming in like this:
=Switch([PHQ_01]=0,"Not at all",[PHQ_01]=1,"Several
days",[PHQ_01]=2,"More
than half of the days",[PHQ_01]=3,"Nearly every day")
Could this affect the problem?

Also here is what I have for the code under the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Adherence_Pull.Report.Visible = Me.Adherence_Pull.Report.HasData
Me.Alcohol_Pull.Report.Visible = Me.Alcohol_Pull.Report.HasData
Me.Concerns_Pull.Report.Visible = Me.Concerns_Pull.Report.HasData
Me.CoreMania_Pull.Report.Visible = Me.CoreMania_Pull.Report.HasData
Me.CoreSuicide_Pull.Report.Visible = Me.CoreSuicide_Pull.Report.HasData
Me.Drug_Pull.Report.Visible = Me.Drug_Pull.Report.HasData
Me.ManiaSet_Pull.Report.Visible = Me.ManiaSet_Pull.Report.HasData
Me.PHQ_Pull.Report.Visible = Me.PHQ_Pull.Report.HasData
Me.Psychosis_Pull.Report.Visible = Me.Psychosis_Pull.Report.HasData
Me.SF_Pull.Report.Visible = Me.SF_Pull.Report.HasData
Me.Smoking_Pull.Report.Visible = Me.Smoking_Pull.Report.HasData
Me.SubstanceSet_Pull.Report.Visible = Me.SubstanceSet_Pull.Report.HasData
Me.SuicideSet_Pull.Report.Visible = Me.SuicideSet_Pull.Report.HasData
End Sub


:

I have a report with multiple subreports on them. All in all its about
3
pages. Some of the sub reports are blank sometimes because there is no
data
these are at the end of the main report. So even if they are blank it
still
creates a blank end page.

The question that I had is, is there a way to have the last page not
show up
if there is no data in the subreports.
Click to expand...
Click to expand...
Click to expand...
 
Sorry you misunderstood what i meant. The VBA should be in the OnFormat of
the section of the MAIN report where the subreport is. HOWEVER if the SOURCE
of the subreport is such that it will NEVER contain all null fields, then
the condition will never be met, so the subreport will never be set to
Visible = False.

Sorry but I'm not sure why you are using the Switch as the recordsource...
or how that might affect things


pokdbz said:
Ok so I put each statement in the subreports details
How do I change this statement for the subreport:
Me.CoreSuicide_Pull.Report.Visible = Me.CoreSuicide_Pull.Report.HasData
it is giving an error in its current form.

SusanV said:
Subreports - sorry!

SusanV said:
It looks to me as though your record source for your subforms doesn't
have
a possible null value - no matter what case your switch pulls up
there's a
value?

Yes I do have the code under the detail section where the subreprorts
are.
On all of the subreports I have data coming in like this:
=Switch([PHQ_01]=0,"Not at all",[PHQ_01]=1,"Several
days",[PHQ_01]=2,"More
than half of the days",[PHQ_01]=3,"Nearly every day")
Could this affect the problem?

Also here is what I have for the code under the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Adherence_Pull.Report.Visible = Me.Adherence_Pull.Report.HasData
Me.Alcohol_Pull.Report.Visible = Me.Alcohol_Pull.Report.HasData
Me.Concerns_Pull.Report.Visible = Me.Concerns_Pull.Report.HasData
Me.CoreMania_Pull.Report.Visible = Me.CoreMania_Pull.Report.HasData
Me.CoreSuicide_Pull.Report.Visible =
Me.CoreSuicide_Pull.Report.HasData
Me.Drug_Pull.Report.Visible = Me.Drug_Pull.Report.HasData
Me.ManiaSet_Pull.Report.Visible = Me.ManiaSet_Pull.Report.HasData
Me.PHQ_Pull.Report.Visible = Me.PHQ_Pull.Report.HasData
Me.Psychosis_Pull.Report.Visible = Me.Psychosis_Pull.Report.HasData
Me.SF_Pull.Report.Visible = Me.SF_Pull.Report.HasData
Me.Smoking_Pull.Report.Visible = Me.Smoking_Pull.Report.HasData
Me.SubstanceSet_Pull.Report.Visible =
Me.SubstanceSet_Pull.Report.HasData
Me.SuicideSet_Pull.Report.Visible = Me.SuicideSet_Pull.Report.HasData
End Sub


:

I have a report with multiple subreports on them. All in all its
about
3
pages. Some of the sub reports are blank sometimes because there is
no
data
these are at the end of the main report. So even if they are blank
it
still
creates a blank end page.

The question that I had is, is there a way to have the last page not
show up
if there is no data in the subreports.
Click to expand...
Click to expand...
Click to expand...
 
I'm not familiar with the Switch function, does it have the capability to do an "Else" value which like Select Case?

Select Case Expression
Case Expression1
field1 = "Yes"
Case Expression2
field1 = "No"
Case Expression3
field1 = "Maybe"
Case Else
field1 = ""
End Select

Case Else being your possible null value. With the Switch as you have ti written, there would "ALWAYS" be a value associated with your RecordSource for the subreport...

Maybe I'm confused, I don't know. But without the recordsopurce being NO DATA, Me.Visible = False jsut isn't going to happen...


SusanV said:
Sorry you misunderstood what i meant. The VBA should be in the OnFormat of
the section of the MAIN report where the subreport is. HOWEVER if the SOURCE
of the subreport is such that it will NEVER contain all null fields, then
the condition will never be met, so the subreport will never be set to
Visible = False.

Sorry but I'm not sure why you are using the Switch as the recordsource...
or how that might affect things


pokdbz said:
Ok so I put each statement in the subreports details
How do I change this statement for the subreport:
Me.CoreSuicide_Pull.Report.Visible = Me.CoreSuicide_Pull.Report.HasData
it is giving an error in its current form.

SusanV said:
Subreports - sorry!

It looks to me as though your record source for your subforms doesn't
have
a possible null value - no matter what case your switch pulls up
there's a
value?

Yes I do have the code under the detail section where the subreprorts
are.
On all of the subreports I have data coming in like this:
=Switch([PHQ_01]=0,"Not at all",[PHQ_01]=1,"Several
days",[PHQ_01]=2,"More
than half of the days",[PHQ_01]=3,"Nearly every day")
Could this affect the problem?

Also here is what I have for the code under the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Adherence_Pull.Report.Visible = Me.Adherence_Pull.Report.HasData
Me.Alcohol_Pull.Report.Visible = Me.Alcohol_Pull.Report.HasData
Me.Concerns_Pull.Report.Visible = Me.Concerns_Pull.Report.HasData
Me.CoreMania_Pull.Report.Visible = Me.CoreMania_Pull.Report.HasData
Me.CoreSuicide_Pull.Report.Visible =
Me.CoreSuicide_Pull.Report.HasData
Me.Drug_Pull.Report.Visible = Me.Drug_Pull.Report.HasData
Me.ManiaSet_Pull.Report.Visible = Me.ManiaSet_Pull.Report.HasData
Me.PHQ_Pull.Report.Visible = Me.PHQ_Pull.Report.HasData
Me.Psychosis_Pull.Report.Visible = Me.Psychosis_Pull.Report.HasData
Me.SF_Pull.Report.Visible = Me.SF_Pull.Report.HasData
Me.Smoking_Pull.Report.Visible = Me.Smoking_Pull.Report.HasData
Me.SubstanceSet_Pull.Report.Visible =
Me.SubstanceSet_Pull.Report.HasData
Me.SuicideSet_Pull.Report.Visible = Me.SuicideSet_Pull.Report.HasData
End Sub


:

I have a report with multiple subreports on them. All in all its
about
3
pages. Some of the sub reports are blank sometimes because there is
no
data
these are at the end of the main report. So even if they are blank
it
still
creates a blank end page.

The question that I had is, is there a way to have the last page not
show up
if there is no data in the subreports.
Click to expand...
Click to expand...
Click to expand...
 
The switch statement will not produce a "" or null. When I put the statement
on the subreport I was getting an error:
Me.Smoking_Pull.Report.Visible = Me.Smoking_Pull.Report.HasData
= me.smoking_pull.report

SusanV said:
I'm not familiar with the Switch function, does it have the capability to do an "Else" value which like Select Case?

Select Case Expression
Case Expression1
field1 = "Yes"
Case Expression2
field1 = "No"
Case Expression3
field1 = "Maybe"
Case Else
field1 = ""
End Select

Case Else being your possible null value. With the Switch as you have ti written, there would "ALWAYS" be a value associated with your RecordSource for the subreport...

Maybe I'm confused, I don't know. But without the recordsopurce being NO DATA, Me.Visible = False jsut isn't going to happen...


SusanV said:
Sorry you misunderstood what i meant. The VBA should be in the OnFormat of
the section of the MAIN report where the subreport is. HOWEVER if the SOURCE
of the subreport is such that it will NEVER contain all null fields, then
the condition will never be met, so the subreport will never be set to
Visible = False.

Sorry but I'm not sure why you are using the Switch as the recordsource...
or how that might affect things


pokdbz said:
Ok so I put each statement in the subreports details
How do I change this statement for the subreport:
Me.CoreSuicide_Pull.Report.Visible = Me.CoreSuicide_Pull.Report.HasData
it is giving an error in its current form.

:

Subreports - sorry!

It looks to me as though your record source for your subforms doesn't
have
a possible null value - no matter what case your switch pulls up
there's a
value?

Yes I do have the code under the detail section where the subreprorts
are.
On all of the subreports I have data coming in like this:
=Switch([PHQ_01]=0,"Not at all",[PHQ_01]=1,"Several
days",[PHQ_01]=2,"More
than half of the days",[PHQ_01]=3,"Nearly every day")
Could this affect the problem?

Also here is what I have for the code under the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Adherence_Pull.Report.Visible = Me.Adherence_Pull.Report.HasData
Me.Alcohol_Pull.Report.Visible = Me.Alcohol_Pull.Report.HasData
Me.Concerns_Pull.Report.Visible = Me.Concerns_Pull.Report.HasData
Me.CoreMania_Pull.Report.Visible = Me.CoreMania_Pull.Report.HasData
Me.CoreSuicide_Pull.Report.Visible =
Me.CoreSuicide_Pull.Report.HasData
Me.Drug_Pull.Report.Visible = Me.Drug_Pull.Report.HasData
Me.ManiaSet_Pull.Report.Visible = Me.ManiaSet_Pull.Report.HasData
Me.PHQ_Pull.Report.Visible = Me.PHQ_Pull.Report.HasData
Me.Psychosis_Pull.Report.Visible = Me.Psychosis_Pull.Report.HasData
Me.SF_Pull.Report.Visible = Me.SF_Pull.Report.HasData
Me.Smoking_Pull.Report.Visible = Me.Smoking_Pull.Report.HasData
Me.SubstanceSet_Pull.Report.Visible =
Me.SubstanceSet_Pull.Report.HasData
Me.SuicideSet_Pull.Report.Visible = Me.SuicideSet_Pull.Report.HasData
End Sub


:

I have a report with multiple subreports on them. All in all its
about
3
pages. Some of the sub reports are blank sometimes because there is
no
data
these are at the end of the main report. So even if they are blank
it
still
creates a blank end page.

The question that I had is, is there a way to have the last page not
show up
if there is no data in the subreports.
Click to expand...
Click to expand...
Click to expand...
 
The statement should not be on the subreport it should be on the MAIN report
in the SECTION where the subreport is embedded. And again, if your switch is
ALWAYS going to produce a result, then you're never going to be at a HASDATA
= FALSE status, therefore the subreport is ALWAYS going to be visible.

If you want to use this functionality, you will have to redefine your
recordsource property for the subreport in a way that will allow
Subreport.hasdata = FALSE

I don't know how I can possibly make this any clearer, sorry if you still
don't understand.


pokdbz said:
The switch statement will not produce a "" or null. When I put the
statement
on the subreport I was getting an error:
Me.Smoking_Pull.Report.Visible = Me.Smoking_Pull.Report.HasData
= me.smoking_pull.report

SusanV said:
I'm not familiar with the Switch function, does it have the capability to
do an "Else" value which like Select Case?

Select Case Expression
Case Expression1
field1 = "Yes"
Case Expression2
field1 = "No"
Case Expression3
field1 = "Maybe"
Case Else
field1 = ""
End Select

Case Else being your possible null value. With the Switch as you have ti
written, there would "ALWAYS" be a value associated with your
RecordSource for the subreport...

Maybe I'm confused, I don't know. But without the recordsopurce being NO
DATA, Me.Visible = False jsut isn't going to happen...


SusanV said:
Sorry you misunderstood what i meant. The VBA should be in the OnFormat
of
the section of the MAIN report where the subreport is. HOWEVER if the
SOURCE
of the subreport is such that it will NEVER contain all null fields,
then
the condition will never be met, so the subreport will never be set to
Visible = False.

Sorry but I'm not sure why you are using the Switch as the
recordsource...
or how that might affect things


Ok so I put each statement in the subreports details
How do I change this statement for the subreport:
Me.CoreSuicide_Pull.Report.Visible =
Me.CoreSuicide_Pull.Report.HasData
it is giving an error in its current form.

:

Subreports - sorry!

It looks to me as though your record source for your subforms
doesn't
have
a possible null value - no matter what case your switch pulls up
there's a
value?

Yes I do have the code under the detail section where the
subreprorts
are.
On all of the subreports I have data coming in like this:
=Switch([PHQ_01]=0,"Not at all",[PHQ_01]=1,"Several
days",[PHQ_01]=2,"More
than half of the days",[PHQ_01]=3,"Nearly every day")
Could this affect the problem?

Also here is what I have for the code under the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As
Integer)
Me.Adherence_Pull.Report.Visible =
Me.Adherence_Pull.Report.HasData
Me.Alcohol_Pull.Report.Visible = Me.Alcohol_Pull.Report.HasData
Me.Concerns_Pull.Report.Visible = Me.Concerns_Pull.Report.HasData
Me.CoreMania_Pull.Report.Visible =
Me.CoreMania_Pull.Report.HasData
Me.CoreSuicide_Pull.Report.Visible =
Me.CoreSuicide_Pull.Report.HasData
Me.Drug_Pull.Report.Visible = Me.Drug_Pull.Report.HasData
Me.ManiaSet_Pull.Report.Visible = Me.ManiaSet_Pull.Report.HasData
Me.PHQ_Pull.Report.Visible = Me.PHQ_Pull.Report.HasData
Me.Psychosis_Pull.Report.Visible =
Me.Psychosis_Pull.Report.HasData
Me.SF_Pull.Report.Visible = Me.SF_Pull.Report.HasData
Me.Smoking_Pull.Report.Visible = Me.Smoking_Pull.Report.HasData
Me.SubstanceSet_Pull.Report.Visible =
Me.SubstanceSet_Pull.Report.HasData
Me.SuicideSet_Pull.Report.Visible =
Me.SuicideSet_Pull.Report.HasData
End Sub


:

I have a report with multiple subreports on them. All in all its
about
3
pages. Some of the sub reports are blank sometimes because there
is
no
data
these are at the end of the main report. So even if they are
blank
it
still
creates a blank end page.

The question that I had is, is there a way to have the last page
not
show up
if there is no data in the subreports.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top