Quick reference

• 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.