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.
Download this lesson’s related exercise files.Services_31_Start RepeatSections_VBAforContinued.accdb
1.8 MB Services_31_RepeatSections_VBAforContinued.accdb
Repeat Sections, VBA to Show or Hide Continued Label
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.
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.
Space-Underscore (space, underscore) at the end of a line in VBA is called Line Continuation.
VBA is Visual Basic for Applications and is the programming language for Access desktop databases.
Drop Multiple Controls
- To Drop Multiple Controls of the same type, right-click the control you want
- Choose Drop Multiple controls from the shortcut menu
- Click in each place where you want that type of contol
- Press (Escape) to end the Drop Multiple Control mode.
Repeat Section at the Top of the Next Page
- 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
- 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
- To indent VBA code, press (Tab) at the beginning of the statement
- To decrease the indent of VBA code, press (Shift-Tab) at the beginning of the statement
- 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
- 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.
- Click in the Group Header section to place the control.
- Change the Caption of the Label to say, for instance, Customer continued.
- Set the Continued label format to be italics so it doesn't look like data.
- Change the Label Name to be, for instance, Label_Continued.
- Add a textbox control in the Page Header section and delete the associated label.
- Change the Control Source of the textbox to =Page
(to keep track of the page number)
- Name the control with the page number expression to txtPage.
- Set the Visible property of the control with the page number expression to No.
- Go to the VBA code behind the report.
- 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.
- 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.
- Initialize the value of the nPage variable in the Report Header Format event.
- 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
- 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.
- Debug, Compile the code and Save
Lesson notes are only available for subscribers.