Do NOT LAST page break AFTER pg break

[DZ]

Hi

My report has 3 pages for each record, separated by page breaks. How can I
not print the area after the 2nd page break (page 3) if the value in a field
is null.

In other words I don't want to print the 3rd page if a field value is null

I've been experimenting with the following with different combination
MoveLayout
NextRecord
PrintSection

and I know that the following will not print the page


MoveLayout = False
NextRecord = True
PrintSection = False

The problem I can't solve is how the identify the page that I don't want
printed. The problem is that the page numbers become confusing when a page is
skipped.

I tried different variable for the page. I just can't get it.
I will credit any helpful responses

 

[DZ]

I'm just adding a note to clarify this problem.

I want most records to print all 3 pages.
except for records that certain a null value for a particular field. Then
for those exceptions (with the null value) I want to print only pg 1 and pg 2
but not pg 3.

thanks

I'm struggling with this.

 

[Marshall Barton]

I'm just adding a note to clarify this problem.

I want most records to print all 3 pages.
except for records that certain a null value for a particular field. Then
for those exceptions (with the null value) I want to print only pg 1 and pg 2
but not pg 3.

Don't fool around with any of that stuff.

You never did say what data and report section(s?) are on
the third page, but whatever it is, you only have to make
it/them invisible.

 

[DZ]

Marshall

Thanks for responding.

I'm still not getting it to work.
I tried you're suggestion. Is this what you meant?

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If IsNull(Me.AccountName) And [Page] Mod 3 = 0 Then
Me.Detail.Visible = False
Me.PageFooterSection.Visible = False
Me.PageHeaderSection.Visible = False
End If

If Not IsNull(Me.AccountName) Then
Me.Detail.Visible = True
Me.PageFooterSection.Visible = True
Me.PageHeaderSection.Visible = True
End If

End Sub

Let me clarify a little more.

The Report has Detail . page header and page footer Sections.
The Report's RecordSource contains a field called AccountName. A textbox
with AccountName as the controlSource is in the Detail section.
AccountName contains some null values. For the records that contain a null
value in the AccountName field, I want only page 1 and page 2 printed, but
not page 3. So some records will have 2 pages and some will have three,
depending on whether AccountName is null.

Thanks for any motre clarification on how to do this.

 

[Marshall Barton]

I'm still not getting it to work.
I tried you're suggestion. Is this what you meant?

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If IsNull(Me.AccountName) And [Page] Mod 3 = 0 Then
Me.Detail.Visible = False
Me.PageFooterSection.Visible = False
Me.PageHeaderSection.Visible = False
End If

If Not IsNull(Me.AccountName) Then
Me.Detail.Visible = True
Me.PageFooterSection.Visible = True
Me.PageHeaderSection.Visible = True
End If

End Sub

Let me clarify a little more.

The Report has Detail . page header and page footer Sections.
The Report's RecordSource contains a field called AccountName. A textbox
with AccountName as the controlSource is in the Detail section.
AccountName contains some null values. For the records that contain a null
value in the AccountName field, I want only page 1 and page 2 printed, but
not page 3. So some records will have 2 pages and some will have three,
depending on whether AccountName is null.

You are thinking about this in the wrong way. Pages are not
something that you show or not show. You need to think
about it as the **data** that you want to appeaar or not.

Your attempt at code inplies that you want to suppress all
the detal section. If that's really the case then hiding
the section is a convenient way to hide all the controls
within the section.

A page will not exist (inclufing the Page header/footer
sections) if there is nothing to display so you should not
need to hide the page header/footer,

With all that in mind, you only need to hide the detail
section. You do need to do this in the section's Format
event. The Print event is too late to affect the layout of
a section.

If I have interpreted your intentions correctly, the only
code you need is:

Me.Detail.Visible = Not IsNull(Me.AccountName)

 

[DZ]

Thanks again for the response.

Let me clarify more.

My attempts at code were probably misleading you. I was making a blind
attempt to get it ti work.

The code snippet you gave me hides the entire Detail section. That's not
what I want.

I want to to suppress(not format, not print, hide etc Not sure of the
terminalogy.) the area of the Detail section after the 2nd page break (in the
detail section). That would be every 3rd page of the detail section for every
record if AccountName is null. If AccountName is not null, then a want all 3
pages of the detail.

So some records would have 3 pages of the Detail formatted/printed. Some
records would have 2 pages of the Detail formatted/printed.... depending on
whether AccountName is null or not.

Thanks again for your responses

 

[Marshall Barton]

Let me clarify more.

My attempts at code were probably misleading you. I was making a blind
attempt to get it ti work.

The code snippet you gave me hides the entire Detail section. That's not
what I want.

I want to to suppress(not format, not print, hide etc Not sure of the
terminalogy.) the area of the Detail section after the 2nd page break (in the
detail section). That would be every 3rd page of the detail section for every
record if AccountName is null. If AccountName is not null, then a want all 3
pages of the detail.

So some records would have 3 pages of the Detail formatted/printed. Some
records would have 2 pages of the Detail formatted/printed.... depending on
whether AccountName is null or not.

You're still thinking in the wrong direction. You must do
something (hide) the controls that you don't want to appear.
If those are the only things that spill over to a third
page, then there won't be that third page.

That kind of manipulation **must** be done in the section's
Format event procedure.

Because, making a text box invisble only leaves a blank area
in the section, it is important that both the text boxes and
the section have their CanShrink property set to Yes.

A relatively straightforward way to identify the controls
(including the second page break) to make invisible is to
set their Tag property to something like HIDE. Then you can
use code like this:

Dim ctl As Control
For Each ctl In Me.Section(0).Controls
If ctl.Tag = "HIDE" Then
ctl.Visible = Not IsNull(Me.AccountName)
End If
Next ctl

Any text boxes that are both CanShrink and nnvisible will
shrink the section and if enough of them do that then the
section will not need the extra page.

There are probably some labels and or white space between
the text boxes that do not the CanShrink property so if you
get a third, but blank, page, we may have to use an obscure
but simple trick.

 

[DZ]

Thanks again for helping me through this.
To try and isolate your idea, I now have only a single text box on page 3 of
the Detail and only a single section, the Detail.

I followed your instructions and set the Tag properties of the text box
AccountName to "HIDE". Then I used the following code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Detail.Controls
If ctl.Tag = "HIDE" Then
ctl.Visible = Not IsNull(Me.AccountName)
End If
Next ctl

End Sub

I am now getting a blank 3rd page. I set the border of the text box to 2
line weight to verify that it's invisible and shrunken.

As an experiment, I moved the AccountName text box up beyond the 2nd page
break so that it's on page 2 instead of page 3 and I still get the blank 3rd
page.

DZ

 

[John Spencer]

If you have a page break control on the report, then you can set its
visible property to true or false. When the page break control is not
visible, it will not cause a new page.

Perhaps when you hide the controls, you also need to set the visible
property of the page break.

Of course, if you aren't using a page break control, then ignore this
bit of advice.

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

 

[Marshall Barton]

To try and isolate your idea, I now have only a single text box on page 3 of
the Detail and only a single section, the Detail.

I followed your instructions and set the Tag properties of the text box
AccountName to "HIDE". Then I used the following code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Detail.Controls
If ctl.Tag = "HIDE" Then
ctl.Visible = Not IsNull(Me.AccountName)
End If
Next ctl

End Sub

I am now getting a blank 3rd page. I set the border of the text box to 2
line weight to verify that it's invisible and shrunken.

As an experiment, I moved the AccountName text box up beyond the 2nd page
break so that it's on page 2 instead of page 3 and I still get the blank 3rd
page.

What John said about making the second page break control
invisible. I intended to say that the second page break
control should also have HIDE in its Tag property.

I also tried to warn you about leaving blank areas in the
part of the section that appears on the third page. If you
can not simply eliminate the extra space between the page
break and the text box and below the text box, then please
explain in more detail.

 

[DZ]

Marsh

I set the Tag of the PageBreak control to "HIDE".

I am still getting a blank page 3.

Thanks

 

[DZ]

John

Thanks for your response.

I set the Tag of the PageBreak control to "HIDE".
I am still getting a blank page 3 for records where AccountName ios null.

I used the following code.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Detail.Controls
If ctl.Tag = "HIDE" Then
ctl.Visible = Not IsNull(Me.AccountName)
End If
Next ctl

End sub

To give you a liitle background, here is a complete (I hope) description of
what I am trying to do.

Access 2003

My Report has Detail, pageheader and pagefooter Sections.

I want the report to have maximum of 3 pages of Detail section, for most
records,

and 2 pages of Detail section for some records depending on the conditions

described below in the next paragraph. I placed 2 page break controls in
detail

section to create a default number of 3 pages for the Detail... But if the
condition

described below exists, I want only 2 pages of Detail.


The Report's RecordSource contains a field called AccountName.
A textbox with AccountName as the ControlSource is in the Detail section.
AccountName contains some null values. For the records that contain a null
value in the AccountName field, I want only page 1 and page 2 formatted and
printed, but not page 3. So some records will have 2 pages and some will have
three, depending on whether AccountName is null.

Regards
Angelo DiBraccio
DZ

 

[Marshall Barton]

I set the Tag of the PageBreak control to "HIDE".

I am still getting a blank page 3.

What about the vertical white space after the second page
break and before the text box and the space after the text
box to the bottom of the detail section? I suspect that
your third page contains nothing but these two blank areas.

Did you try moving the text box up to just below the second
page break and also moving the bottom of the detail section
up to the bottom of the text box as I suggested in my
previous reply?

If you need to have that white space, it might help you see
what's going on if you make the detail section's back color
something other than white. Then add a Label control so
that its bottom is just barely above the second page break
and set it's back color to yet another color.

 

[DZ]

Marsh

Yes. It worked when I make sure there is no white space between controls or
the ends or beginnings of the detail section.

I can't thank you enough for your patience and resourcefulness.


On a different note, do you know any way to overcome the 22" limit on the
detail length. I think the last page might be a little too short. I havn't
received the final layout design from the project manager so I don't know
what the dimensions will be yet. I tried moving the 2nd page break up to
steal a little space from page 2 and add the extra space to page 3. That
helped a little.

DZ

 

[Marshall Barton]

Yes. It worked when I make sure there is no white space between controls or
the ends or beginnings of the detail section.

I can't thank you enough for your patience and resourcefulness.

OK, that demonstrates that we have a viable approach. When
you get around to adding back the other text boxes to the
"third page" area of the detail section, there are a couple
of possible ways to deal with any white space or labels
between the text boxes.

On a different note, do you know any way to overcome the 22" limit on the
detail length. I think the last page might be a little too short. I havn't
received the final layout design from the project manager so I don't know
what the dimensions will be yet. I tried moving the 2nd page break up to
steal a little space from page 2 and add the extra space to page 3. That
helped a little.

The easiest is to split your detail data over three
sections. This can be done by creating a group on the
detail record's sort field (primary key?) Then you will
only get one group header and footer per detail record. You
can then place the "page 1" controls in the header, the
"page 2" controls in the detail section and the "page 3"
controls in the footer.

This arrangement might also simplify hiding the third page.
You could just make the footer section invisible with out
the complexities of hiding the individual controls and
dealing with the white space. You may even be able to use
the section's ForceNewPage property instead of page break
controls.

 

[DZ]

Marshall

Yes. That's fabulous !

I did what you said. I got rid of the PageBreak Controls. I set the Details
CanGrow back to false so that I now have control of the exact size.

I used the following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control

If IsNull(Me.AccountName) Then
Me.GroupFooter1.Visible = False
Else
Me.GroupFooter1.Visible = True
End If

End Sub