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

 

[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