ms access 2007 setting report page breaks using VBA

dogdays

Active Member
Joined
Apr 28, 2008
Messages
431
I have created a report that lists donor names and their individual donations, and the total # donations and Total amount for the donor as well as over all totals.
The report has the following sections:
Report header -blank
Page header - has name of report
tSortName Header - has the name and address of the donor
Detail - list individual donations
tSortName footer - has total # donations and Total amount this donor
Page header - blank
Report footer - has total number of all donations and total amount of of all donations

The group sort and total entries are
group on tSortName
sort by tSortName

I want to force a page break after the tSortName footer prints.
tia Jack
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
Your title suggests you want to do this in code, but I'm going to guess that you haven't tried setting this in design. No need for code if this does what you need:
In design view, in the Group, Sort and Total window at the bottom of the screen, click on the "Group on tSortName" bar and click on More> and at the end of the resulting options you should see a grouping option. Check that it is set according to what you need. Then on the Format tab of the properties sheet for the grouping section, set the "force new page" option you want. Probably "after section".
 

dogdays

Active Member
Joined
Apr 28, 2008
Messages
431
Thanks for the reply. I am using VBA because the user, at run time, will choose the columns to group on and sort on.
I should have mentioned this in the first post.

Jack
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
OK, report headers and footers have what's called a format event. I have only ever used them once, and quite recently, so I am no means an expert on the subject. I suspect that when the report opens, the user choices have been passed to the report opening event, so if you declare these choices as variables in the declarations section for the report (not the declarations section for an event), you can refer to them in the format event for any footer or header. I'm thinking that you can test for which sections have been chosen to group by and write code to implement the page break property for that section.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,312
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top