none
How to display fixed number of records per page in a report RRS feed

  • Question

  • Hi All,

    I'm using the Northwind Traders database as an example to create my own. I have tweaked the database to what I want and its almost perfect to my requirement. One problem I have is the display of rows to fill up a landscape sheet (which I will print twice as customer copy and company copy) on one sheet of paper. So if there is one order id with 4 order details of that order id I need to display the 4 plus an additional 6 blank order details to fill up the space. If 3 then 7. If 11 then 10 on one page with 1 on and 9 blanks.

    Please let me know what information is required to help in this question so I can provide it. I can upload the database as well as it is if required.

    Saturday, October 3, 2015 11:08 AM

Answers

  • So, you would like to display each order on its own page? You can use a combination of grouping and a page break control to do this.  See here for more information:

    https://support.office.com/en-us/article/Insert-a-page-break-control-in-an-Access-report-86D2EA7D-7AA6-44F2-8507-23134A9A2E15



    Saturday, October 3, 2015 2:03 PM
  • 1.  Add a table Counters, with a single column Counter, to the database and give it values from 1 to at least 10.

    2.  Create a query along the following lines:

    SELECT [Order Details].ID, [Order Details].[Order ID], [Product Name],
    Quantity,[Unit Price], [Quantity]*[Unit Price] AS TotalPrice
    FROM Products INNER JOIN [Order Details]
    ON Products.ID = [Order Details].[Product ID]
    UNION ALL
    SELECT NULL,[Order ID],NULL,NULL,NULL,NULL
    FROM [Order Details], Counters
    WHERE Counter <10
    ORDER BY [Order ID],[Product Name] DESC;

    This uses the Access 2007 version of Northwind, so the number of columns returned might differ in other  versions.  Base a subreport on the query, linking it to the parent report on Order ID.  In the above the ORDER BY clause is only used for illustrative purposes if you open the query directly.  As reports ignore the ODER BY clause you will need to order the rows by means of the report's internal sorting and grouping mechanism.

    The query will return at least 10 rows per Order ID.  To suppress the redundant rows, in the subreport add a text box control with a ControlSource of  =1 and a RunningSum property of 'over all'.  Set the text box's Visible property to False if you don't wish to show the item nubreing in the report.  In the subreport's Detail section's Format event procedure set the return value of the Cancel argument to TRUE when the hidden control's value reaches 10:

    Cancel = (Me.YourHiddenTextBox > 10)

    Ken Sheridan, Stafford, England

    Saturday, October 3, 2015 5:01 PM

All replies

  • So, you would like to display each order on its own page? You can use a combination of grouping and a page break control to do this.  See here for more information:

    https://support.office.com/en-us/article/Insert-a-page-break-control-in-an-Access-report-86D2EA7D-7AA6-44F2-8507-23134A9A2E15



    Saturday, October 3, 2015 2:03 PM
  • Hi. Are you talking about adding blank rows because you're trying to mimic an existing paper form?
    Saturday, October 3, 2015 4:56 PM
  • 1.  Add a table Counters, with a single column Counter, to the database and give it values from 1 to at least 10.

    2.  Create a query along the following lines:

    SELECT [Order Details].ID, [Order Details].[Order ID], [Product Name],
    Quantity,[Unit Price], [Quantity]*[Unit Price] AS TotalPrice
    FROM Products INNER JOIN [Order Details]
    ON Products.ID = [Order Details].[Product ID]
    UNION ALL
    SELECT NULL,[Order ID],NULL,NULL,NULL,NULL
    FROM [Order Details], Counters
    WHERE Counter <10
    ORDER BY [Order ID],[Product Name] DESC;

    This uses the Access 2007 version of Northwind, so the number of columns returned might differ in other  versions.  Base a subreport on the query, linking it to the parent report on Order ID.  In the above the ORDER BY clause is only used for illustrative purposes if you open the query directly.  As reports ignore the ODER BY clause you will need to order the rows by means of the report's internal sorting and grouping mechanism.

    The query will return at least 10 rows per Order ID.  To suppress the redundant rows, in the subreport add a text box control with a ControlSource of  =1 and a RunningSum property of 'over all'.  Set the text box's Visible property to False if you don't wish to show the item nubreing in the report.  In the subreport's Detail section's Format event procedure set the return value of the Cancel argument to TRUE when the hidden control's value reaches 10:

    Cancel = (Me.YourHiddenTextBox > 10)

    Ken Sheridan, Stafford, England

    Saturday, October 3, 2015 5:01 PM