Force Page Break When 2 Column Subreport reaches 10 records 1

[iamdman]

Hello,

I have a report which contains a sub report. The sub report displays items from an invoice and it is set to display "QTY|DESCRIPTION|AMOUNT QTY|DESCRIPTION|AMOUNT" across the top (as you can see the header is being repeated in column 2 by fixing the sub-report height to only have enough room to show 5 records and letting the sub-report display columnar data down-then-across.) Once column 1 contains 5 records and column 2 contains 5 records I want the next 10 records to display on page 2 of the report, and so on. I tried putting a page break control in the detail section of the sub-report and using VBA to make the page break visible after 10 records but the report only shows one page, I never see any records after the tenth. Any ideas?

Thanks,

iAmDmAn

 

[iamdman]

bump

 

[dhookom]

You may need to add a column to your subreport that groups by 10 so the first 10 records have a GroupNum of 1, the next 10 have a GroupNum of 2, ....etc.

Then create a main report that has GroupNums of 1 through whatever in the record source. Set up the Link Master/Child properties to GroupNum.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[iamdman]

Can you elaborate on how to add the group by 10 column to the sub report?

 

[iamdman]

I just realized, I don't think your idea would work. The sub-report is already part of a master child relationship, there is no ability to add another one.

For example the sub-report is showing all invoice items for invoice id = 5. There are 20 invoice items associated with invoice id = 5, I want to show only 10 invoice items on each page.

It seems like a straightforward request but Access is not making it simple.

 

[dhookom]

You can have almost any number of fields in your link master/child properties. Adding a GroupNum would be possible.

To create a GroupNum in your subreport, you can use a DCount() in the record source query. For instance, if you wanted to group the Orders in Northwind by 10s withing each EmployeeID, the query would be:

SELECT Val(DCount("*","Orders","EmployeeID=" & [EmployeeID] & " AND OrderID <" & [OrderID]))\10 AS GroupNum, Orders.*
FROM Orders
ORDER BY Orders.EmployeeID;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[iamdman]

I see what you are getting at but my main report only has 1 record, ie. using your example, hypothetically:

Main report: EmployeeID = 5 (returns 1 record)

Sub report: All orders with employeeID = 5 (returns 25 records)

Now, I can use your DCount technique to add a groupnum to the sub report, that's done. So using this example you would see the first 10 orders with a groupnum of 0, second set would have a groupnum of 1, last five groupnum = 3.

Now how do I make use of this groupnum in the main report since it only has 1 record.

Thanks.

iAmDmAn

 

[dhookom]

Take my SQL and modify it so you get potentially more than one record:

SELECT Val(DCount("*","Orders","EmployeeID=" & [EmployeeID] & " AND OrderID <" & [OrderID]))\10 AS GroupNum, Orders.EmployeeID
FROM Orders
GROUP BY Val(DCount("*","Orders","EmployeeID=" & [EmployeeID] & " AND OrderID <" & [OrderID]))\10, EmployeeID
ORDER BY Orders.EmployeeID;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[iamdman]

Yes you are a genious thanks for your help. I also had to add an additional grouping level to the main report that grouped on GroupNum and forced a new page, this was in addition to the Link master/childs on groupnum.

Thanks again,

;-) only took me 3 days. haha.

iAmDmAn.