Conditional Page Break

[Kathy Webster]

I posted this in reports, but no one answered:

How can I create a page break before the subreport if and only if the
subreport has data?
Some records will have no data in the subreport, and I don't want an
unnecessary page break in those instances.
Thank you,
Kathy

 

[Marshall Barton]

I posted this in reports, but no one answered:

How can I create a page break before the subreport if and only if the
subreport has data?
Some records will have no data in the subreport, and I don't want an
unnecessary page break in those instances.

Cross posting to 6 newagroups is way excessive.

Use a line of code in the Format event procedure of the
section containing the page break and subreport:

Me.pgBreak.Visible = Me.HasData

 

[Kathy Webster]

Marshall,

Sorry about the multiple posts. It's hard to know which ones people actually
read with all the spam and stuff mixed in.

Since there are 2 subreports in the section, how would I write the code to
specify if I want the page break visible only if the SECOND subreport has
data?

 

[Marshall Barton]

Sorry, I answered the wrong question :-\

Use this instead:

Me.pgBreak.Visible = Me.subreportcontrol.Report.HasData

Change subreportcontrol to the name of the subreport control
on the main report (not necessarily the name of the report
object it displays).

 

[Doug M]

I don't know how you can do this with a subreport, but you could do this
with a report that includes grouping.

Create a query to combine your report level records with your subreport
records. In the relationship between the header table XXX and subrecord
table YYY make sure the relationship is the type "include all records from
table XXX and any related records from table YYY". Then build your report
on this query and add grouping on the record level ID field.

Then add an unbound field (called say "counter") on the group header with
the expression "=Count([YYY_ID])" in the datasource where YYY_ID is the key
field in the YYY table. This field will be 0 when there are no subrecords.
Add a page break (called say "newpage") at the bottom of the group header
(under the fields to be shown), and in the OnFormat event put for the group
header you add code like...

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If counter = 0 Then
newpage.Visible = False
Else
newpage.Visible = True
End If
End Sub

So the report should then show the header level record and if there are
subrecords there will be a page break then the subrecords are printed. If
there are no subrecords then no page break.

Doug

 

[Kathy Webster]

thank you!! it works great. i changed Me.pgBreak.Visible to the actual name
of the page break object: Me.pageBreak93.Visible and it worked perfectly.
Thanks!

Sorry, I answered the wrong question :-\

Use this instead:

Me.pgBreak.Visible = Me.subreportcontrol.Report.HasData

Change subreportcontrol to the name of the subreport control
on the main report (not necessarily the name of the report
object it displays).
--
Marsh
MVP [MS Access]

Sorry about the multiple posts. It's hard to know which ones people
actually
read with all the spam and stuff mixed in.

Since there are 2 subreports in the section, how would I write the code to
specify if I want the page break visible only if the SECOND subreport has
data?

 

[Doug M]

I don't know about doing this with a subreport, but you can accomplish
this with a grouped report.

Create a query between the master record table and the sub-record table,
then create the report on this query. Click on the sorting and grouping
button and set grouping on a field in the master record table.

In the group header area you put master record fields, and also add an
unbound field with an expression =Count([SubrecordID]) where SubrecordID is
a field in the subrecord table. You can set visible for this count field to
false if you don't want to show it. Also add a page break in the group
header below all the fields.

In the report detail add the fields for the subreport, then in the On
Format event of the report detail put something like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(SubrecordID) Then Cancel = True
End Sub

This prevents the detail from printing when there are no sub-records.

In the On Format event of the group header put something like:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If counter = 0 Then
newpage.Visible = False
Else
newpage.Visible = True
End If
End Sub

This will cause the newpage to render only if there are subrecords.

Doug

 

[saima]

@hotmail.com

Cross posting to 6 newagroups is way excessive.

Use a line of code in the Format event procedure of the
section containing the page break and subreport:

Me.pgBreak.Visible = Me.HasData

 

[saima]

@yahoo.com

Cross posting to 6 newagroups is way excessive.

Use a line of code in the Format event procedure of the
section containing the page break and subreport:

Me.pgBreak.Visible = Me.HasData

 

[saima]

gdfgfdgdfgdggd

I don't know how you can do this with a subreport, but you could do this
with a report that includes grouping.

Create a query to combine your report level records with your subreport
records. In the relationship between the header table XXX and subrecord
table YYY make sure the relationship is the type "include all records from
table XXX and any related records from table YYY". Then build your report
on this query and add grouping on the record level ID field.

Then add an unbound field (called say "counter") on the group header with
the expression "=Count([YYY_ID])" in the datasource where YYY_ID is the key
field in the YYY table. This field will be 0 when there are no subrecords.
Add a page break (called say "newpage") at the bottom of the group header
(under the fields to be shown), and in the OnFormat event put for the group
header you add code like...

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If counter = 0 Then
newpage.Visible = False
Else
newpage.Visible = True
End If
End Sub

So the report should then show the header level record and if there are
subrecords there will be a page break then the subrecords are printed. If
there are no subrecords then no page break.

Doug

I posted this in reports, but no one answered:

How can I create a page break before the subreport if and only if the
subreport has data?
Some records will have no data in the subreport, and I don't want an
unnecessary page break in those instances.
Thank you,
Kathy