Page Break based on Counter

[irishkenj]

I have a report that prints 4 records per page without page breaks. I would like it to break after every 6 records so I get a page with 4 than a page with 2, page with 4 then a page with 2, etc.

I think I need a counter and then tie the Page Break to it. All suggestions greatly appreciated!

Ken

 

[THWatson]

Ken
I didn't have time to take this further, but I took a brief run at something that I think can be expanded upon.

I put a text box in the Detail section, and gave it a value of 1. It is Running Sum.

Then, in the Format event for the Detail section I set it to ForceNewPage = 2 (after section) if the value of the running sum text box was 6.
This forced a second page. Trouble was that every record from thereon forced a new page too.

So I think you could Dim something like a myInt text box in the Report Header, and every time this arrived at a value of 6 a new page would be forced.

Or if you could establish a new Group every 6 records, and force a new page every time that group filled up, that would do it too.

Sorry for a partial reply, but I didn't have time to work more out.

Tom

 

[BoxHead]

Ken,

I put a field (myRc) in my query that numbers the records 1 through 6 based on my customer name which is a "no duplicates" field and my sorting field for the query/report.

myRc: 1+(DCount("[cname]","tblCust","[cname] < " & """" & [cname] & """") Mod 6)

In the report's detail format event, I used the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If myRc = 4 Or myRc = 6 Then
Me.Detail.ForceNewPage = 2
Else
Me.Detail.ForceNewPage = 0
End If
End Sub

It's important in this method that you have a unique field on which you're sorting so I'm not sure if it will work for your situation.


HTH




John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

Hi, Tom.

Looking further at your post, you can add the text with the running sum and a textbox that uses the mod statement to identify every sixth record:

tSum = 1 RunningSum = Over All
tRow = [tSum] Mod 6

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If tRow = 0 Then   'It's the sixth record
Me.Detail.ForceNewPage = 2
Else
Me.Detail.ForceNewPage = 0
End If
End Sub

Solves the sorting and uniqueness requirements from my first post.


HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

Hi, John...good catch!

There you go, Ken. Works like a charm. Two text boxes (tSum and tRow) as John as indicated.

Tom

 

[irishkenj]

Man -you guys are good! Thanks - many bonus points with the bride this evening!!