Page Break if next value of field in previous record is not consec

[Sarah]

I have a report used to display box numbers by category (record series). I
have the report grouped by RSNo, then sorted by BoxNo with a page break
forced when a new RSNo occurs. Additionally, however, there should be a new
page if the box numbers within the RS are not consecutive. For example, if
the box number in RS1 are 1235, 1236, 1237, 1240, 1241, and 1242 I should end
up with two pages. The first page should have 1235, 1236 and 1237 and the
second page should have 1240, 1241, and 1242. I thought I could do this
using VB and an if...then...else statement such that
If BoxNo.CurrentValue (which I don't know how to get) = BoxNo.NextValue
(again, I don't know how to get or even if it is possible) +1
Then
ForceNewPage=0
Else
ForceNewPage=2
End If

However, since I don't know how to get those values, or even if it is
possible, I have not been able to get this report to work. Does anyone know
how I can do this either using this method or some other method?

Thanks in advance,
Sarah

 

[Pampas58 via AccessMonster.com]

Sarah,

It seems that for the most part you know what you want to do. Here's what I
did to get the value of the control just prior to print.

1) Opened the Report Properties
2) Went to EVENT in the Detail Section
3) In the EVENT for ON PRINT I put the following code MsgBox [Control Name
Here]

It gave me the field just before it printed, so... if you store the old
number, and compare it to the current number - 1. If you set up a counter
and ignore the first record print, then the PageFeed should work.

Hope this helps,

Pampas58

 

[Sarah]

I figured out a way to do this. It may not be the best way, but it worked.
I thought I would share it in case anyone else needed to do something similar.

I used a query to find the next box number by taking the current box number
and adding one ([BoxNo]+1). Then I combined it with my original query and
added the RSNumbers table again (RSNumbers_1) and connected my new query to
it. So I have my original box and all its information (including RS) and
then the next box number and its RS. Then I wrote the following formula:

NewPage:Iif(IsNull([NewRS]),"No",Iif([RsNo]=[NewRS],"No","Yes"))

I added the is null portion so that the last box, which doesn't have a rs
for the next box because it doesn't exist, returns No.

Then in the on format for the details portion of the report, I put the
following:

If Me.NewPage=No Then
Me.Details.ForceNewPage=0
Else
Me.Details.ForceNewPage=1
End If

This worked great.

Sarah