Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limit records that print on Report (2000)

    Hi,
    I have a report that has to look a certain way. It will have information at the top, list about 7 records (out of 25), then more information, then on the next page I want the rest of the 25 records to print. I'm not sure how to limit the first page of the report to only show 7 records. It will always be just 7 on the first page and the overflow should go on page 2.
    Thanks,
    Deb

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

    Re: Limit records that print on Report (2000)

    Put a text box in the detail section.
    - Set its Visible property to No (the text box may overlap or cover visible controls)
    - Set its Control Source property to =1
    - Set its Running Sum property to Over All
    - Set its Name property to txtCount

    Put a page break control at the bottom of the detail section, below all other controls.
    - Set its Name property to brkPage

    Create an On Format event procedure for the detail section:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.brkPage.Visible = (Me.txtCount = 7)
    End Sub

    This code will make the page break visible, and thereby active, only when the 7th record has been displayed.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit records that print on Report (2000)

    I'll give that a try, Thanks a ton Hans.

    Deb

  4. #4
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit records that print on Report (2000)

    Hi Hans,
    I'm doing something wrong, but I also don't think I gave enough information before. I've attached a copy of the database with the report for your review. I've attached a review file because I don't know if I can explain it properly. The way the report will be set up is, the bulk of the data will only be listed once and right now most of it is in the detail section. So in the detail section I have some data, then I have a subreport, then I have some more data. The data before and after the subreport can't change. The subreport is what I want to only list 7 records and then the rest will list on the 2nd page. I only want the subreport data to be listed on the 2nd page.
    I tried what you gave me on a dummy database and it worked great. So I know it's not the code. But I'm assuming because I'm using a subreport things have to be changed. I tried a few things, like moving the data that is after the subreport to the report footer section and calling the record count from the subreport to the main report, but I couldn't get anything to work.
    Thanks,
    Deb <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    Attached Files Attached Files

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

    Re: Limit records that print on Report (2000)

    It's a pity that you left out the most essential information from your original question. The effort spent on a reply that turns out to be irrelevant is wasted. I'm not sure what you want exactly:

    Situation A:
    some data from main report
    7 records from subreport
    --- page break ---
    remaining records from subreport
    rest of data from main report

    or

    Situation B ("I only want the subreport data to be listed on the 2nd page"):
    some data from main report
    7 records from subreport
    rest of data from main report
    --- page break ---
    remaining records from subreport

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit records that print on Report (2000)

    I apologize for that, when I posted my first message I didn't realize I needed a subreport. I only realized that after my first idea of course did not work. Then I figured I could just modify your code, but couldn't figure that out either. Situation B is what I would like.
    Deb
    <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

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

    Re: Limit records that print on Report (2000)

    You can't do this with one subreport. I made a copy of the subreport in the database window and placed that below the existing controls in the detail section.

    I put code in the On Format event of the Detail section of the first (original) subreport to cancel displaying records past the 7th one:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = (Me.calcPRReportLineNumber > 7)
    End Sub

    The other subreport has similar code, but with the opposite condition, in order to cancel all records up to and including the 7th one.
    Attached Files Attached Files

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit records that print on Report (2000)

    That worked great Hans. Thanks a ton for your help. The only problem is, when the records are less than 7 it still prints a page 2. is there a way to say if <=7 cancel and make it not visible so it won't print?
    Thanks,
    Deb

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

    Re: Limit records that print on Report (2000)

    Create an On Format event procedure for the Detail section of the main report rptPurchaseRequestForm:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.sfrmPRPartsSubreportRest.Visible = _
    (DCount("*", "tblParts", "lngVendorID=" & Me.lngVendorID) > 7)
    End Sub

    where sfrmPRPartsSubreportRest must be the name of the second subreport as a control on the main report. This is not necessarily the same as the name of the subreport in the database window. (In the database I attached earlier, the control name was something like Sub190)

    The code uses DCount to count the number of parts to be displayed for this vendor; the second subreport is only made visible of this number is over 7.

    Added: make sure that the Can Shrink property of the Detail section of the main report is set to Yes.

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit records that print on Report (2000)

    Thanks Hans,
    That worked great! You're the best!
    Thanks,
    Deb

Posting Permissions

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