Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    24

    Unanswered: Pagebreak in subreport

    I thought I had this figured out, but apparently not...

    I have a main report in Access 2000 that is designed to print out onto a pre-fab triplicate hardcopy form. There is a subreport in the main report's Details section that can contain 1-many items, but this subreport only has a 2-inch space to print on the hardcopy -- so if there are more than 7 items to be listed in the subreport, I need a subreport page break that will force the main report to start a new page. This new page would need to include the main report's Header data again, and then have the subreport pick up where it left off -- but printing in the same location as it did on page 1.

    How do I make this work?

    P.S. I have now learned that subreport page breaks are ignored in Access. Go figure... But is there some other way to achieve the same result????
    Last edited by whill96205; 12-01-04 at 13:30.

  2. #2
    Join Date
    Jun 2004
    Posts
    24
    Just posting to keep this on first page a little longer....

  3. #3
    Join Date
    Jun 2004
    Posts
    24

    Arrow Solution

    Okay, I've got a solution! For anyone else curious as to how to make this work...

    First -- Subreport pagebreaks are not recognized in Access. So... I deleted my subreport. Then, I created a single new query which selects all the pertinent columns from the table that the main report was bound to, PLUS also selects the data that was in the query to which the subreport was bound. This new query also includes a GROUP BY statement to ensure that all the related items (that used to be in the subreport) are now grouped by OrderID, etc. (The text of my query is below.) I then bound the report to this new "super" query. The report's Header section contains all the old "main report" data (customer name, address, etc.). The report's Details section contains: 1) a text box to be used as a counter; 2) the pertinent data fields that relate to the one-to-many items that used to be in the subreport; and 3) a page break.

    The counter text box needs the following properties set:
    Control Source: =1
    Running Sum: Over All

    The Details section of the report needs the following OnFormat event:
    If Me![Counter] Mod X = 0 Then Me![PageBreak].Visible = True _
    Else: Me![PageBreak].Visible = False

    (where X = the number of rows that will fit on the hardcopy printout before the page break is needed). (Note: The height of the report's Details section will determine the line spacing of the items.)

    The report is then called from a command button on one of my Access forms, which opens the report with the following:
    DoCmd.OpenReport [Report_Name], acViewPreview, , "[Primary_Report_Field]=" & Me.[Primary_Key].Value

    Thus, the report is opened and the "subreport" items will be "grouped" under the Primary Key value of the form, which means that all of the Header data prints on the report page once, and all the "subreport" items print out as a list in the Details section. AND... the page break works, because there is no ACTUAL subreport anymore. (The additional pages will print all the report's Header information again, and the Details section will continue printing the itemized list wherever it left off on the prior page.)

    That's it!
    ----------------------------------------------------------

    Here's my query (for reference, the EbayNum, Description, LotNum, and WareHouse columns were the one's that used to be in my subreport):

    SELECT dbo.[ORDER].OrderID, dbo.[ORDER].CustomerName, dbo.[ORDER].CustPhone1, dbo.[ORDER].CustPhone2, dbo.[ORDER].PickupAddress, dbo.[ORDER].PickupCity, dbo.[ORDER].PickupState, dbo.[ORDER].PickupZIP, dbo.[ORDER].OrderDate, dbo.[ORDER].ShipQuote, dbo.[ORDER].Declared, dbo.[ORDER].ShipNotes, dbo.[ORDER].Type, dbo.[ORDER].ShipMethod, dbo.[ORDER].ShipDate, dbo.[ORDER].ShipTo, dbo.[ORDER].ShipAddress1, dbo.[ORDER].ShipAddress2, dbo.[ORDER].ShipPhone1, dbo.[ORDER].ShipPhone2, dbo.[ORDER].ShipPhone3, dbo.[ORDER].ShipCity, dbo.[ORDER].ShipState, dbo.[ORDER].ShipZIP, dbo.[ORDER-DETAIL].EbayNum, dbo.AUCTION.LotNum, dbo.LOT.Description, dbo.LOT.WarehouseLoc

    FROM dbo.[ORDER] LEFT OUTER JOIN
    dbo.[ORDER-DETAIL] ON dbo.[ORDER].OrderID = dbo.[ORDER-DETAIL].OrderID LEFT OUTER JOIN
    dbo.AUCTION ON dbo.[ORDER-DETAIL].EbayNum = dbo.AUCTION.EbayNum LEFT OUTER JOIN
    dbo.LOT ON dbo.AUCTION.LotNum = dbo.LOT.LotNum

    GROUP BY dbo.[ORDER].OrderID, dbo.[ORDER].CustomerName, dbo.[ORDER].CustPhone1, dbo.[ORDER].CustPhone2, dbo.[ORDER].PickupAddress, dbo.[ORDER].PickupCity, dbo.[ORDER].PickupState, dbo.[ORDER].PickupZIP, dbo.[ORDER].OrderDate, dbo.[ORDER].ShipQuote, dbo.[ORDER].Declared, dbo.[ORDER].ShipNotes, dbo.[ORDER].Type, dbo.[ORDER].ShipMethod, dbo.[ORDER].ShipDate, dbo.[ORDER].ShipTo, dbo.[ORDER].ShipAddress1, dbo.[ORDER].ShipAddress2, dbo.[ORDER].ShipPhone1, dbo.[ORDER].ShipPhone2, dbo.[ORDER].ShipPhone3, dbo.[ORDER].ShipCity, dbo.[ORDER].ShipState, dbo.[ORDER].ShipZIP, dbo.[ORDER-DETAIL].EbayNum, dbo.AUCTION.LotNum, dbo.LOT.Description, dbo.LOT.WarehouseLoc

    HAVING (dbo.[ORDER].Type = 'Order')

Posting Permissions

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