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

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.

Answer 1

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

Answer 2

Hi. Are you talking about adding blank rows because you're trying to mimic an existing paper form?

Answer 3

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