forcing a page break (AccessXP)

Topic

New Reply

I have a report that can have any number of records based on the user’s selections. We need to force a page break so that only two records appear per page. Unfortunately Sorting/Grouping does not work because of the nature of the report data. However, I was able to put a page break control in the detail section and switch it on and off with code as follows.

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intEvenPage As Integer
intEvenPage = Me.txtRecordCounter.Value Mod 2
If intEvenPage = 0 Then
Me![CondPgBreak].Visible = True
End If
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me.CondPgBreak.Visible = False
End Sub

Now the problem. Whenever there is an even number of records, we get a blank page on the end of the report. How can I eliminate that last blank page?

(editorial comment) This is why I dislike using the page break control. I start thrashing.
Thanks all,

Reply | Quote

Replies

I’m not sure why you’re using a groupheader to turn off your pagebreak when you can do it in the detail_format … particularly when you said that grouping/sorting didn’t work because of the nature of the data. So where is the groupheader coming from? You should be able to do it like this:

Replace this:

If intEvenPage = 0 Then
Me![CondPgBreak].Visible = True
End If

With this:

Me![CondPgBreak].Visible = (intEvenPage = 0 )

Your problem may be in whatever method you’re using to populate txtRecordCounter, but it’s only guesswork without more information. I used an even number of records and didn’t get an extra page at the end regardless.

Reply | Quote

Thanks Charlotte. As always, this board is a great help.
When I set the Group Header property to Keep Together Whole Group, I don’t get 2 records per page as required. This is why I am trying to force a page break after every 2 records.

Regarding using the group header to control page break, this is a carry over from Access 97 Help (search for “page break” and followed the instructions for VB Code) that I used in other Access97 reports.

My report is grouped by Client (Header-yes, Footer-no) and the detail section contains some fields and a subreport. The subreport contains a memo field in the detail section and no report or page headers or footers.

In the detail section of the main report, I have a text box (txtRecordCounter) whose control source =1 and Running Sum property set to “Over all”.

So, I am trying to force a page break after each 2 clients. I inserted the code you suggested and still am getting a blank page on the end if I select an even number of clients.

My next step is to evaluate the txtRecordCounter.value and if even, set [Pages] to [Pages]-1 or something like that. Not sure how this will work and always interested in other suggestions.

Many thanks,

Reply | Quote

I would not suggest you try setting [Pages]. That’s a read-only property of the report. You can manipulate Page, but it doesn’t seem useful to do so in this case.

I’m not clear on the grouping problem, though. Keep together with first detail sounds like it nakes more sense, at least it does if you have nore than 2 records for a group. You’re already controlling the number of records per page, so the “with whole group” setting seems superfluous.
Can you strip down a copy of your database to the tables, queries and report/subreports involved, prune the records down to a handful, remove any sensitive data, and post a zipped sample? It’s hard to recreate the problem without being able to see the actual mechanism.

Reply | Quote

Try the following: put a textbox in the Detail section.
Name: txtTotal
Control Source: =Count(*)
Visible: No

Replace the check

If intEvenPage = 0 Then

by

If intEvenPage = 0 And txtRecordCounter < txtTotal Then

In the last detail section, txtRecordCounter is equal to txtTotal, so the page break won't be made visible.

(Note: I got the idea from the Invoice report in the Developer Solutions database provided by Microsoft)

Reply | Quote