Pagebreak every 7 records

[weili0505]

Can anyone help me please!

I want to make a report in Access that can automatically paginate whenever 7 records are in displayed in 1 page (leaving the rest of the page blank).

Thanks in advance!

[hermhart]

In the Office Assistant search field enter Force New Page. Choose Force a new page in a report if a condition is met. That will lead you to instructions on how to do it either using a macro or VBA code. Depending on your skill level, that should get you on your way.

[xordevoreaux]

I didn't try this myself, but the text excerpted below from the MS Access 2000 help files should do the trick.

Only difference is that you would substitute 7 where you see 10 below.

Use Visual Basic to force a page break in a report if a condition is met

1. Open the report in Design view.

2. In the toolbox, click Page Break , and then click in the report section where you want a conditional page break.


3. Open the report's PageHeader_Format event procedure.


4. In the event procedure, add an assignment statement that sets the Visible property of the page break control to No.

For example, if the name of the control is CondPgBreak, add the following assignment statement:

Me![CondPgBreak].Visible = False


This hides the page break control when the report starts formatting each page, so the page doesn't break.

In the Format event procedure of the section where you placed the page break, add Visual Basic code that sets the Visible property to Yes when a condition is met.

For example, suppose you want a page break to occur in the detail section of the report if the value of the Counter control is 10 so that the first 10 records will print on the first page.

Add the following code to the Detail_Format event procedure:

If Me![Counter] = 10 Then
Me![CondPgBreak].Visible = True
End If

When the condition is met, the page breaks. After the page is broken, the event procedure attached to the page header hides the page break control until the condition is met again.

[hermhart]

Just this tweak:

You have to make a text box in the detail section called [Counter]. Set the properties of this text box as follows:
Name Counter
ControlSource =1
RunningSum OverGroup

Turn off the page header and footer and thus you will not use point 3 and the first line of code in point 4.

Now, instead of the 3 lines of code in point4, enter this:

If Me![Counter]/7=int(Me![Counter]/7) then
Me![CondPgBreak].visible = True
Else Me![CondPgBreak].visible = False
End if