Focus video player for keyboard shortcuts
Auto
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Identify Page Setup options and learn how to force a new page between sections of a second grouping.
Exercise files
Download this lesson’s related exercise files.
Page Breaks and Page Setup1.7 MB Page Breaks and Page Setup
1.7 MB
Quick reference
Page Breaks and Page Setup, ForceNewPage with VBA
Application Terminology
Force New Page
Force New Page can be set for Group sections on the Property Sheet. It can be found on the Format and All tabs of the Property sheet.
Force New Page Help
Page Setup – Page options
Page Setup Ribbon
Page Setup – Print options
Steps
Force New Page Between Second Group Sections
- To Force a New Page between sections of a second grouping, create code for the Format event of the second group footer to force a new page before the next group.
- Create code for the first group header to not break the page before the next second group header so the first second group section stays on the same page as the first group section.
- 00:05 This is Lesson 28 of Access 2013.
- 00:09 In this lesson, we will discuss Page Setup and
- 00:12 learn how to control page breaks.
- 00:15 I'm throwing in a little VBA too.
- 00:19 Hi, this is Crystal.
- 00:21 Go to the Design View of the report, r_CustomerServicesByYear.
- 00:28 Drop the choices for Margins in the Page Size group
- 00:32 of the REPORT DESIGN TOOLS, PAGE SETUP ribbon.
- 00:36 If you are not using the default paper size,
- 00:39 drop the list of choices under Size in the Page Size group
- 00:45 My margins are set to 0.25 inches,
- 00:48 which is the Narrow setting under Margins.
- 00:52 Many printers cannot print this close to the edges of the page.
- 00:57 Click the Page Setup icon in the Page Layout group.
- 01:01 Change the Margins to 0.5 inches for
- 01:05 Top, Bottom, Left, and Right.
- 01:09 With 8-1/2 x 11 paper, this means you will be able to
- 01:12 print 7.5 inches across a page in Portrait mode.
- 01:18 If your data is wider, notice you can
- 01:20 change the layout to Landscape.
- 01:24 On the Page tab of Print Options, you can also
- 01:27 set the Size, including a User Defined size.
- 01:31 If your printer has more than one paper tray,
- 01:34 and you want to use a different one, change the Source.
- 01:38 If you format the report
- 01:40 for a specific printer instead of default,
- 01:44 then if you share this database with someone
- 01:47 who does not have the same print driver,
- 01:49 they will have to change this option.
- 01:53 On the Columns tab, you can change the number of Columns.
- 01:57 When you look at the Print Preview,
- 01:59 notice how the page does not break when the year changes.
- 02:04 Each section has a property called Force New Page,
- 02:08 which defines how pages will break.
- 02:12 Change Force New Page
- 02:13 for the YearWork Header to Before Section.
- 02:18 When you look at the Print Preview
- 02:20 and flip through the pages, there is a page break
- 02:22 before the section each time the year changes.
- 02:26 If you also set Force New Page to Before Section
- 02:29 for the Customer Header, the first page only has the year,
- 02:34 which is not desireable.
- 02:37 Instead, set Force New Page to After Section
- 02:41 in the Customer Footer.
- 02:43 This solves the problem of the first page
- 02:46 but now the last page has just summary information.
- 02:50 Clearly, there is room on the next-to-last page
- 02:54 for the summary information.
- 02:57 This is a know issue with Access
- 02:59 and no property you can set to avoid it.
- 03:02 We can, however, use a little VBA,
- 03:06 Visual Basic for Applications, to solve this issue.
- 03:10 Change Force New Page for the Customer Footer,
- 03:13 and Header, section back to None.
- 03:16 When you press F1 in the ForceNewPage property,
- 03:20 you can pop up Help.
- 03:22 Here is what Help says for the ForceNewPage property.
- 03:26 None is 0 (zero) and Before Section is 1.
- 03:31 Select the Customer Header section
- 03:34 and copy the Name to the clipboard.
- 03:36 For me, this is GroupHeader1.
- 03:39 Note that your sections may be named different,
- 03:43 so be sure to use your Customer Header section name
- 03:47 in what we are going to do next.
- 03:50 We do not want a page break before the Customer Header
- 03:53 if it is on the same page as the YearWork Header section.
- 03:58 Click on the Event tab of the Property Sheet
- 04:01 and then click on the YearWork Header.
- 04:04 Type [ in the On Format event, which selects [Event Procedure].
- 04:13 Then press Ctrl-F2 or click ... for the code Builder.
- 04:19 When the Visual Basic Editor comes up,
- 04:21 you will be positioned between the start
- 04:24 and end of the Format event for the YearWork Group Header.
- 04:30 Press the TAB key to indent the line of code
- 04:33 so code is easier to read.
- 04:35 Type a single quote mark to indicate a comment
- 04:38 and then type: 'YearWork Header
- 04:41 Press ENTER to go to the next line.
- 04:44 Type Me. to reference the report object that the code is behind.
- 04:50 Intellisense pops up and you can see the name of controls
- 04:54 on the report, as well as properties of the report.
- 04:59 Paste the name you copied from the Property Sheet
- 05:02 and type a period.
- 05:03 Now Intellisense gives you a list of properties
- 05:06 you can set for the section.
- 05:09 type: for
- 05:12 and you can see ForceNewPage is highlighted.
- 05:16 Press TAB to select the highlighted choice on the list.
- 05:20 Then type:
- 05:21 = 0 'no page break before customer when year changes
- 05:30 Click off the line.
- 05:32 Anything after a single quote mark turns green
- 05:35 to indicate it is a comment.
- 05:37 Comments are ignored by the compiler
- 05:40 and will not be processed.
- 05:43 So when do we turn on ForceNewPage before customer?
- 05:48 We want it to come on whenever Access
- 05:51 comes to the end of the customer,
- 05:54 which will be the Customer Footer section.
- 05:56 Select the Customer Footer section.
- 06:00 Type [ in the On Format event, which selects [Event Procedure].
- 06:08 Then press Ctrl-F2 or click ... for the code Builder.
- 06:12 Again press TAB to indent the statement.
- 06:15 Type 'Customer Footer and press ENTER to create a comment
- 06:21 On the next line, type me. then paste, then a period.
- 06:27 and: for and press TAB when you see
- 06:31 ForceNewPage on the Intellisense list.
- 06:35 Then type: = 1 'page break before next customer
- 06:45 Click away from the line and again, comments turn green.
- 06:48 Take a little time to look at this and think about it.
- 06:51 VBA is not hard, it makes sense.
- 06:54 It is a very easy language to learn,
- 06:57 even if you do not consider yourself a programmer.
- 07:00 First you reference an object, then the property you want,
- 07:04 then what you want to do to it.
- 07:06 Whenever you write VBA,
- 07:08 you should compile and save the code before running.
- 07:11 From the menu, choose Debug, Compile
- 07:14 and then click the Save icon on the toolbar.
- 07:19 I have a few more toolbars showing then you will have.
- 07:23 Go back to the report and choose Print Preview.
- 07:26 As you flip through the pages,
- 07:28 you will see that new customers start on new pages.
- 07:33 The summary for each year is shown below the last customer
- 07:36 for the year, instead of at the top of a new page.
- 07:40 On the last page, there is no page break before summaries,
- 07:45 unless the page ends and there is no room to print summaries
- 07:48 without going to another page.
- 07:50 And the grand totals are printed after the year totals.
- 07:55 In this lesson, you have learned about page breaks
- 07:58 and how to use just a couple lines of VBA,
- 08:01 to get reports to do exactly what you want!
- 08:05 In the next lesson,
- 08:07 we will talk about shading to differentiate records,
- 08:10 which was going to be included in this lesson.
- 08:13 As you can see on the report, every other line is shaded.
Lesson notes are only available for subscribers.