This is Lesson 28 of Access 2013.
In this lesson, we will discuss Page Setup and
learn how to control page breaks.
I'm throwing in a little VBA too.
Hi, this is Crystal.
Go to the Design View of the report, r_CustomerServicesByYear.
Drop the choices for Margins in the Page Size group
of the REPORT DESIGN TOOLS, PAGE SETUP ribbon.
If you are not using the default paper size,
drop the list of choices under Size in the Page Size group
My margins are set to 0.25 inches,
which is the Narrow setting under Margins.
Many printers cannot print this close to the edges of the page.
Click the Page Setup icon in the Page Layout group.
Change the Margins to 0.5 inches for
Top, Bottom, Left, and Right.
With 8-1/2 x 11 paper, this means you will be able to
print 7.5 inches across a page in Portrait mode.
If your data is wider, notice you can
change the layout to Landscape.
On the Page tab of Print Options, you can also
set the Size, including a User Defined size.
If your printer has more than one paper tray,
and you want to use a different one, change the Source.
If you format the report
for a specific printer instead of default,
then if you share this database with someone
who does not have the same print driver,
they will have to change this option.
On the Columns tab, you can change the number of Columns.
When you look at the Print Preview,
notice how the page does not break when the year changes.
Each section has a property called Force New Page,
which defines how pages will break.
Change Force New Page
for the YearWork Header to Before Section.
When you look at the Print Preview
and flip through the pages, there is a page break
before the section each time the year changes.
If you also set Force New Page to Before Section
for the Customer Header, the first page only has the year,
which is not desireable.
Instead, set Force New Page to After Section
in the Customer Footer.
This solves the problem of the first page
but now the last page has just summary information.
Clearly, there is room on the next-to-last page
for the summary information.
This is a know issue with Access
and no property you can set to avoid it.
We can, however, use a little VBA,
Visual Basic for Applications, to solve this issue.
Change Force New Page for the Customer Footer,
and Header, section back to None.
When you press F1 in the ForceNewPage property,
you can pop up Help.
Here is what Help says for the ForceNewPage property.
None is 0 (zero) and Before Section is 1.
Select the Customer Header section
and copy the Name to the clipboard.
For me, this is GroupHeader1.
Note that your sections may be named different,
so be sure to use your Customer Header section name
in what we are going to do next.
We do not want a page break before the Customer Header
if it is on the same page as the YearWork Header section.
Click on the Event tab of the Property Sheet
and then click on the YearWork Header.
Type [ in the On Format event, which selects [Event Procedure].
Then press Ctrl-F2 or click ... for the code Builder.
When the Visual Basic Editor comes up,
you will be positioned between the start
and end of the Format event for the YearWork Group Header.
Press the TAB key to indent the line of code
so code is easier to read.
Type a single quote mark to indicate a comment
and then type: 'YearWork Header
Press ENTER to go to the next line.
Type Me. to reference the report object that the code is behind.
Intellisense pops up and you can see the name of controls
on the report, as well as properties of the report.
Paste the name you copied from the Property Sheet
and type a period.
Now Intellisense gives you a list of properties
you can set for the section.
type: for
and you can see ForceNewPage is highlighted.
Press TAB to select the highlighted choice on the list.
Then type:
= 0 'no page break before customer when year changes
Click off the line.
Anything after a single quote mark turns green
to indicate it is a comment.
Comments are ignored by the compiler
and will not be processed.
So when do we turn on ForceNewPage before customer?
We want it to come on whenever Access
comes to the end of the customer,
which will be the Customer Footer section.
Select the Customer Footer section.
Type [ in the On Format event, which selects [Event Procedure].
Then press Ctrl-F2 or click ... for the code Builder.
Again press TAB to indent the statement.
Type 'Customer Footer and press ENTER to create a comment
On the next line, type me. then paste, then a period.
and: for and press TAB when you see
ForceNewPage on the Intellisense list.
Then type: = 1 'page break before next customer
Click away from the line and again, comments turn green.
Take a little time to look at this and think about it.
VBA is not hard, it makes sense.
It is a very easy language to learn,
even if you do not consider yourself a programmer.
First you reference an object, then the property you want,
then what you want to do to it.
Whenever you write VBA,
you should compile and save the code before running.
From the menu, choose Debug, Compile
and then click the Save icon on the toolbar.
I have a few more toolbars showing then you will have.
Go back to the report and choose Print Preview.
As you flip through the pages,
you will see that new customers start on new pages.
The summary for each year is shown below the last customer
for the year, instead of at the top of a new page.
On the last page, there is no page break before summaries,
unless the page ends and there is no room to print summaries
without going to another page.
And the grand totals are printed after the year totals.
In this lesson, you have learned about page breaks
and how to use just a couple lines of VBA,
to get reports to do exactly what you want!
In the next lesson,
we will talk about shading to differentiate records,
which was going to be included in this lesson.
As you can see on the report, every other line is shaded.