Repeat Sections, Show or Hide Continued Label

Locked lesson.

Upgrade

About this lesson

Learn how to Repeat Section on the next page so you can see the group value that detail continues from, and use VBA to add a Continued label to group header on a report.

Quick reference

Repeat Sections, VBA to Show or Hide Continued Label

Application Terminology

Property Sheet Shortcut

The shortcut key to toggle the Property sheet on or off is  

Repeat Section Property

Group Header sections have a Repeat Section property which enables a value to be printed again on the next page if detail for that section is continued.

Visible Property

The Visible Property can be set to Yes or No for a control or section and determines if it will be visible when viewed in Form View, Report View, or Print Preview.

Line Continuation

Space-Underscore    (space, underscore) at the end of a line in VBA is called Line Continuation.

VBA

VBA is Visual Basic for Applications and is the programming language for Access desktop databases.

Steps

Drop Multiple Controls

  1. To Drop Multiple Controls of the same type, right-click the control you want
  2. Choose Drop Multiple controls from the shortcut menu
  3. Click in each place where you want that type of contol
  4. Press  (Escape) to end the Drop Multiple Control mode.

Repeat Section at the Top of the Next Page

  1. To Repeat Section on the next page so you can see the group value that detail continues from, select the section by clicking its section bar or on a blank area of the section
  2. On the Format tab of the Property Sheet, change Repeat Section to Yes

Quickly Change Yes to No (and vice-verse) on Property Sheet

  • To quickly change Yes to No (and vice-verse) on the Property Sheet, double click a Yes/No property.

Go to VBA Code

  • To go to VBA Code, press   (Alt-F11)

Increase or Decrease Indent in VBA code

  1. To indent VBA code, press  (Tab) at the beginning of the statement
  2. To decrease the indent of VBA code, press (Shift-Tab) at the beginning of the statement

Trigger Intellisense

  • If Intellisence doesn't come up in VBA, press  (Ctrl-Spacebar) to trigger it.

Indicate a Statement is Multiple Lines in VBA

  • To indicate that a statement is multiple lines in VBA, press   (space, underscore) at the end of each line except the last one.

Add a Continued Label to Group Header on a Report

  1. To add a Continued label so you know that a group header value is being repeated, click the Label tool in the Controls group of the DESIGN ribbon from the Design View of a Report.
  2. Click in the Group Header section to place the control.
  3. Change the Caption of the Label to say, for instance, Customer continued.
  4. Set the Continued label format to be italics so it doesn't look like data.
  5. Change the Label Name to be, for instance, Label_Continued.
  6. Add a textbox control in the Page Header section and delete the associated label.
  7. Change the Control Source of the textbox to =Page
    (to keep track of the page number)
  8. Name the control with the page number expression to txtPage.
  9. Set the Visible property of the control with the page number expression to No.
  10. Go to the VBA code behind the report.
  11. If Option Explicit is not at the top of the module, add it under Option Compare Database



    this ensures better name checking when you compile.
  12. At the top of the VBA module, under the compiler directives (Option blahblah) dimension a variable that all procedures in the module can see.



    If you have multiple Continued Labels, create multiple variables and Name them accordingly.  for instance, nPageYear and nPageCustomer.
  13. Initialize the value of the nPage variable in the Report Header Format event.

  14. Set the next new page number to not be continued to be 1 after the page number of the Group Footer.  In the Group Footer Format event:



    substitute YOUR Group Footer Section name for GroupFooterName
  15. Turn on the label using the Page Header section Format event if it is not equal to 1 or the page number after the group footer.  Otherwise, if the page number is equal to the page number that the section started on, set Visible = No.

  16. Debug, Compile the code and Save
Login to download
  • 00:04 This is Access 2013, Lesson 31.
  • 00:09 When you have more detail lines than one page,
  • 00:13 it would be nice to repeat the group sections
  • 00:16 at the top of the next page so the data is more clear.
  • 00:19 Hello, this is Crystal.
  • 00:21 In this lesson, you will learn how to repeat sections,
  • 00:25 and turn on a Continued label with VBA,
  • 00:28 if a group is continued from the previous page.
  • 00:32 In the Navigation Pane, collapse the groups for
  • 00:35 Tables, Queries, and Forms by clicking their headers.
  • 00:40 Open r_CustomerServicesByYear in Print Preview
  • 00:46 The 2014 data for Betty Barkley continues at the top of page 2,
  • 00:52 but there is currently no way to know that without going back
  • 00:55 and looking at the group information on page 1 again.
  • 00:59 Go to the Design View and select the YearWork Header.
  • 01:03 On the Format tab of the Property Sheet,
  • 01:06 change Repeat Section to Yes.
  • 01:11 Select the Customer Header
  • 01:13 and change its Repeat Section property to Yes.
  • 01:17 When you look at the report,
  • 01:19 you now see that Year and Customer
  • 01:22 are repeated at the top of page 2.
  • 01:26 It would be nicer, however, to have labels that say Continued.
  • 01:32 Right-click the Label Tool in the Controls group
  • 01:35 of the DESIGN ribbon and choose Drop Multiple Controls.
  • 01:40 Add a label on the right side of the Year Work Header
  • 01:43 that says Year Continued.
  • 01:46 Add a label on the right side in the Customer Header
  • 01:49 that says Customer Continued.
  • 01:53 When you are done typing each caption,
  • 01:55 press ENTER to end your typing.
  • 01:58 When you are done with both labels,
  • 02:00 press ESCape to turn off dropping labels.
  • 02:04 Press Alt-Enter to turn on the Property Sheet.
  • 02:09 On the Property Sheet,
  • 02:10 change the Name to Label_Continued_Year
  • 02:18 and Label_Continued_Customer, respectively,
  • 02:23 on the Other or All tab.
  • 02:26 Select both labels and press Ctrl-I
  • 02:29 to turn the text inside them to Italics.
  • 02:33 Set Visible to No on the Format tab of the Property Sheet.
  • 02:38 Shift-click the Running Sum label to add it to the selection.
  • 02:43 Line up the selected labels with Align Right on the QAT,
  • 02:48 and click Align Right in the Text Formatting group
  • 02:51 to right align the contents within each label as well.
  • 02:55 Now select the YearWork textbox and its Continued label.
  • 03:00 Click the Align Bottom icon on the QAT
  • 03:03 and then the Align Top icon.
  • 03:07 Do the same for the Customer and its Continued label.
  • 03:11 In order to keep track of whether or not
  • 03:13 a section is just starting, or it is continuing,
  • 03:17 we need to know the current page number
  • 03:19 and what page each of the group sections start on.
  • 03:23 In the Page Header section, add a textbox control
  • 03:27 and delete the associated label.
  • 03:29 Select the new textbox and set the following properties:
  • 03:34 Name: txtPage (Other tab)
  • 03:38 Control Source: =[Page] (Data tab)
  • 03:44 Visible: No (Format tab)
  • 03:51 Back Color: Black (Format tab, it will change to #000000)
  • 03:53 Fore Color: White (Format tab, it will change to #FFFFFF)
  • 03:56 You may want to wait to set Visible until you test this out
  • 03:59 and see for yourself that the page number is right.
  • 04:03 Drag a sizing handle to reduce the width of the textbox
  • 04:07 to only show the contents.
  • 04:09 Page is a property of the report
  • 04:12 to keep track of the current page number
  • 04:15 Now let's use VBA to create variables
  • 04:19 to calculate page numbers that the next
  • 04:21 YearWork and Customers group start on.
  • 04:24 On the DESIGN ribbon of REPORT DESIGN TOOLS,
  • 04:28 click on the View Code icon in the Tools group.
  • 04:31 This takes you to the code behind the report.
  • 04:34 Click on the blank line under Option Explicit.
  • 04:38 Press ENTER twice to create 2 more blank lines.
  • 04:43 Press UP ARROW to move up to the middle blank line.
  • 04:47 Type: dim nPageYear as long [and press ENTER]
  • 04:55 After you move off the line, Access adjust the case of
  • 04:58 Dim, As, and Long to capitalize the first letter of each word.
  • 05:04 This tell you that Access recognizes these as keywords.
  • 05:09 Type dim nPageCustomer as long [and press ENTER]
  • 05:17 Again, Access adjusts the case of recognized keywords.
  • 05:22 Notice, I mixed the case for the variable names.
  • 05:27 From now on, they can be typed in lower case
  • 05:30 and Access will, again, correct them,
  • 05:33 letting you know they are recognized.
  • 05:36 These variables will keep track of the page numbers
  • 05:39 where each section will start.
  • 05:42 They are dimensioned at the top of the code so that
  • 05:45 all procedures in the module can see their values.
  • 05:49 When the report opens, initialize each of these
  • 05:53 variables to have a value of 1.
  • 05:56 There are 2 lists at the top of the module.
  • 05:59 Drop the list on the left and choose ReportHeader.
  • 06:03 The default event is Format.
  • 06:06 In the list on the right at the top,
  • 06:08 you see the events that the ReportHeader can have.
  • 06:12 This is an alternate way to define events.
  • 06:15 Events that have code will be bolded.
  • 06:18 If you click on another event, Access will create
  • 06:21 the declaration and End statements for you.
  • 06:25 I don't need this event, just wanted to show you how it is done,
  • 06:28 so I will delete these lines.
  • 06:31 In the ReportHeader_Format procedure, add these statements:
  • 06:36 'Report Header [Enter]
  • 06:40 nPageYear = 1 [Enter]
  • 06:46 nPageCustomer = 1 [Enter]
  • 06:51 The order that procedures are listed does not matter.
  • 06:56 Select the lines by clicking and dragging to the left of each line,
  • 07:00 just before you go into the gray area,
  • 07:02 while the cursor is pointing at the code.
  • 07:05 Let go of the mouse, then click on the selection
  • 07:07 nPageYear = Me.txtPage + 1
  • 07:07 and drag the selection to one of the blank lines
  • 07:11 below the Dim statements at the top.
  • 07:14 Go back to the form and click on the YearWork footer.
  • 07:19 On the Property Sheet, click in the On Format event
  • 07:22 and drop the list of choices.
  • 07:25 Macros that are defined and [Event Procedure] will be listed.
  • 07:31 Choose [Event Procedure] and press Ctrl-F2.
  • 07:36 Again, you are positioned
  • 07:38 between the declaration and End statements.
  • 07:41 Type:
  • 07:43 'YearWork Footer [Enter]
  • 07:47 nPageYear = me.txtPage + 1
  • 07:55 Remember to indent your lines with TAB.
  • 08:00 We are adding 1 to the current page since we know that
  • 08:03 the next year will start on the next page.
  • 08:06 The reason, remember, for the txtPage textbox
  • 08:10 is to keep track of the current page.
  • 08:13 We already have a procedure for Customer Footer.
  • 08:17 Find it and add another line that says:
  • 08:20 nPageCustomer = Me.txtPage + 1
  • 08:26 Now that we have set up the variables, the only thing left to do
  • 08:30 is turn the visibility of the labels on or off for each page.
  • 08:36 From the top of the module sheet,
  • 08:38 choose PageHeaderSection on the left.
  • 08:41 type:
  • 08:43 [Tab] If nPageYear = Me.txtPage Then [Enter]
  • 08:55 [Tab] Me.Label_Continued_Year.Visible = False [Enter]
  • 09:04 [Shift-Tab] Else [Enter]
  • 09:13 [Tab] Me.Label_Continued_Year.Visible = True [Enter]
  • 09:23 [Shift-Tab] End If [Enter]
  • 09:28 This is saying that if the variable
  • 09:30 for the page a year starts is the current page,
  • 09:34 then the Continued label will not show.
  • 09:38 Otherwise, the Visible property for the continued label
  • 09:41 will be true and the label will show.
  • 09:45 Debug, Compile the code and Save it.
  • 09:48 If you have errors, compare your code to mine and fix them.
  • 09:53 Keep compiling until nothing happens, then save.
  • 09:58 Go back to the report and notice there is a Continued label
  • 10:01 when a year continues from the previous page.
  • 10:05 The Continued label does not show when a year starts.
  • 10:09 Press Alt-F11 to go back to the code.
  • 10:13 We are going to use a different method to turn on or off
  • 10:17 the Continued label for Customer that is shorter.
  • 10:20 On a new line, type:
  • 10:22 Me.Label_Continued_Customer.Visible
  • 10:29 = (nPageCustomer <> Me.txtPage)
  • 10:42 Then click off the line.
  • 10:44 First, Access evaluates what is in the parentheses.
  • 10:48 It is either True or it is False.
  • 10:52 <> means not equal to.
  • 10:56 So if the current page number is not equal to the page
  • 11:00 that the customer starts on, then the answer is True and the
  • 11:04 Visible property of the Continued label
  • 11:06 for customer will be True.
  • 11:09 Debug, Compile, and then Save the code.
  • 11:12 I like to rearrange my procedures so they are in a logical order
  • 11:16 instead of an alphabetical order.
  • 11:19 If you do this as well, remember to Compile and Save.
  • 11:24 Close Print Preview then go into Print Preview again.
  • 11:29 Voila! We have Continued messages that turn on and off.
  • 11:35 If you wanted to get really fancy,
  • 11:37 you could also change the Caption property
  • 11:40 to show which page they were continued from.
  • 11:44 Again, we are out of time.
  • 11:46 In the next lesson,
  • 11:47 we will change the sort for service name to a group.

Lesson notes are only available for subscribers.

Gift this course