Conditional Page Breaks

[shank]

I need a page break after 5 records. I inserted a page break and made it invisible.

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Me![PageBreak62].Visible = False
End Sub

See below. Then I setup up the condition to make the page break visible. Results not as expected. I added a textbox [RecCounter] that counts each record 1, 2, 3, 4, etc. In my test example I have a total of 10 records.

1) I would expect the page break to work if >5. But I need > 4 for it to create a page break after 5 records. Just curious, why doesn't >5 work for 5 records.

2) The page breaks after 5 records, but all pages thereafter have 1 record per page because the [RecCounter] doesn't reset to count 1, 2, 3, etc. It's still counting the Running Sum Over All. I set it to Count Over the Group, but still it counts 0-10 on all 10 records. So, how do I get it to only count the records on that page?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me![RecCounter] > 4 Then
        Me![PageBreak62].Visible = True
    End If
End Sub

thanks!

[pbaldy]

Try this:

  If Me.RecCounter Mod 5 = 0 Then
    Me.PageBreak62.Visible = True
  Else
    Me.PageBreak62.Visible = False
  End If

[shank]

That worked. Thanks! Could you elaborate in layman's terms how that worked? I googled for Mod use in VB and still not quite understanding. Is my thinking in the below statements correct?

If Me.RecCounter Mod 5 = 0 Then

Assume RecCounter = 1, 2, 3, or 4
RecCounter/5 has a remainder which means not 0 Then PageBreak62 remains invisible, correct?

Assume RecCounter = 5
RecCounter/5 has no remainder which means 0 Then PageBreak62 is now visible, correct?

Assume RecCounter = 6, 7, 8, or 9
RecCounter/5 (repeatedly) has a remainder which means not 0 Then PageBreak62 remains invisible, correct?

Assume RecCounter = 10
RecCounter/5 (twice) has no remainder which means 0 Then PageBreak62 is now visible, correct?


thanks!

[pbaldy]

You've got it. If the number is evenly divisible by 5 there is no remainder. In that event the condition is met so the page break is visible, otherwise not.