How do i limit the number of records shown on a report?

[papic1972]

Hi all,

I have an 'invoice' style report that i'm designing at the moment. Therefore, for each record or 'invoice number' there will be a list of product items shown that the customer will be invoiced for.
Does anyone know how i can limit the number of items shown on a page before a new page is to be forced? I want to limit the report to show 10 items on a page. Therefore, if the customer is to be invoiced for 20 items, i want to display 10 items on each page.
Can someone please help!
Many thanks!

 

[dhookom]

The number of records displayed on a page should be based on
Paper Height -(Top Margin + Bottom Margin + headers + footers + (Detail Height * Number of Records))

If you want to limit the number of records, you can change section heights accordingly.

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]

 

[papic1972]

Hi dhookom,

Thanks for that. I understand what you are saying, however it doesn't entirely answer my question.
I want to be able to force a new page after 10 records. I also have some information in the page footer that needs to be shown on each page, hence the necessity to force a new page. Is there any way to do this?

 

[dhookom]

There are several methods for forcing a new page. I would add a page break control at the very bottom of the detail section. Name it "PgBrk". Add a text box to the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible = No
Then add code to the On Format event of the detail section:

    Me.PgBrk.Visible = Me.txtCount mod 10 = 0

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]

 

[papic1972]

Hi dhookom,

OK. Excuse my ignorance, but how do i add a page break control?

 

[papic1972]

Geez i'm dumb, i've worked out the page break!

 

[dhookom]

The same way you add any control to a report. Open it in design view, click the page break control in the tool box, click the report where you want the control, and move it into position.

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]

 

[playmkr278]

Hello,

I also have a similar issue and followed the instructions adding Me.PgBrk.Visible = Me.txtCount mod 10 = 0 to the detail OnFormat property. However, I get an error that says Microsoft Access can't find the macro 'Me.'

I'm I doing something wrong?

 

[playmkr278]

Also, adding a conditional page break, would the footer information still be okay for page 1?

I'm guessing yes, but just want to be sure.

 

[dhookom]

You probably tried to add "Code" to a property rather than in the code window. I don't know what you mean by "footer information".

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]