Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    forcing a page break (AccessXP)

    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. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Thanks all,

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: forcing a page break (AccessXP)

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I used an even number of records and didn't get an extra page at the end regardless.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: forcing a page break (AccessXP)

    Thanks Charlotte. As always, this board is a great help. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    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 <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> and always interested in other suggestions.

    Many thanks,

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: forcing a page break (AccessXP)

    I would not suggest you try setting <!t>[Pages]<!/t>. 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.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: forcing a page break (AccessXP)

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •