Insert Page Break Using Code

[TheSearcher]

My report has a detail section consisting of 35 questions. I want to insert a page break after questions 20, 25 and 30. How can I accomplish this? FYI - There is no grouping on the report.

Thanks,
TS

 

[sneuberg]

I don't have much expertise in Access reports but since your thread wasn't getting any response I thought I'd give this problem a shot. I found an idea in this thread that might work in your case if modified a bit. You can see what I've come up with in the attached database. In that thread it says

It uses a text box with control source: =1, and running sum: over group, called txtRowCount, which can be hidden

I don't understand what that means so in my implementation I added an expression to the record source query of the report (qryReportRecordSource in the attached database) named CurrentRow which is:

CurrentRow: DCount("*","[Table1]","[ID] <" & [ID]) + 1

This sequentially numbers the records in the order of ID. If in your case the field that determines the order is a text field your would need to add single quotes to this. Let say the fields name is QuestionNumber then this expression would look like

CurrentRow: DCount("*","[Table1]","QuestionNumber] <'" & [QuestionNumber] & "'") + 1

Note: If you understand what they are doing in that thread that might be a better way of doing this.

The CurrentRow expression was added to the report as a hidden textbox. Then in the On Format event of the Detail section I added the following code.

If Me.CurrentRow = 20 Or Me.CurrentRow = 25 Or Me.CurrentRow = 30 Then
    Detail.ForceNewPage = 2   ' after section
Else
    Detail.ForceNewPage = 0   ' none
End If

To get to this event you open the report in design view, click on the detail bar to select it, open the Property Sheet (if not open already),in the Event tab click on On Format and click on the ellipsis on the right.

 

[HiTechCoach]

[TIP] Page Break Control

There is a page break control that works well for this. Use the visible property to turn on and off.

 

[sneuberg]

[TIP] Page Break Control

There is a page break control that works well for this. Use the visible property to turn on and off.

How do you use it to get page breaks at records 20, 25, and 30?

 

[HiTechCoach]

How do you use it to get page breaks at records 20, 25, and 30?

I took this original post:

My report has a detail section consisting of 35 questions.
TS

to mean that the detail section has 35 controls to hold the questions. Not 35 separate detail records.

If that is true then you simple place multiple page break controls where you want a new page to start.

 

[sneuberg]

I took this original post:



to mean that the detail section has 35 controls to hold the questions. Not 35 separate detail records.

If that is true then you simple place multiple page break controls where you want a new page to start.

I suppose the OP could have 35 unbound controls with the questions but wouldn't that be a bit weird?

 

[HiTechCoach]

I suppose the OP could have 35 unbound controls with the questions but wouldn't that be a bit weird?

I would say a "bit" un-normalized for transaction data entry.

Also the fact there is no grouping makes me think this is data coming from a single record in the detail section.


Reporting is easier with de-normalized / flattened /spreadsheet data.

The data could be coming from Excel or some other source. Access may not be used for the data entry.

FWIW: I do lots of reporting with Access from sources I didn't create or have control over. Sometimes I deal with exports into CSV or Excel format from other applications. I use this as the back end (data source) for my reports. . I don’t worry about normalizing the data. That way I don’t have to turn around and create a query to flatten it back out like it was originally.

 

[TheSearcher]

Thank you snueberg! I just took the last snippet of code that you provided and made it work for me.

 

[GinaWhipp]

[TIP] Page Break Control

There is a page break control that works well for this. Use the visible property to turn on and off.

I know this is an old post. Just came to say I recently (today) kept trying to set the height of the Report Footer with a Page Break knowing it was going to be tricky. THEN I come across this and realize, right, I just need to control the Page Break. Some days I just need more coffee.

 

[Pat Hartman]

What is special about 20, 25, and 30? Could you have other breaks? Perhaps you need to add the concept of a group. rows 1-20 belong to group1, 21-25 belong to group2, 26-30 belong to group3. This would require another table to define groups. Then as you add each item, you assign it to a group using a combo. So if you later decide to add another question, you can add #31 to group1 without having to resequence all the items. The report would add a page break on change in the group value. No code would be required to implement this and it is infinitely flexible.