Private Sub sectionname_Format(Cancel As Integer, FormatCount As Integer)
SectionName The name of a Report section.
Cancel The setting determines if the formatting of the section occurs. Setting the Cancel argument to True (–1) cancels formatting of the section.
FormatCount An Integer value that specifies whether the Format event has occurred more than once for a section. For example, if a section doesn't fit on one page and part of it moves to the next page of the report, Microsoft Access sets the FormatCount argument to 2.
Remarks
To run a macro or event procedure when this event occurs, set the OnFormat property to the name of the macro or to [Event Procedure].
A Format event occurs for each section in a report. This allows you to create complex running calculations by using data from each section, including sections that aren't printed.
For report detail sections, the Format event occurs for each record in the section just before Microsoft Access formats the data in the record. A Format macro or event procedure has access to the data in the current record.
For report group headers, the Format event occurs for each new group, and a Format macro or event procedure has access to the data in the group header and the data in the first record in the detail section. For report group footers, the Format event occurs for each new group, and a Format macro or event procedure has access to the data in the group footer and the data in the last record in the detail section.
By running a macro or an event procedure when the Format event occurs, you can use data in the current record to make changes to the report that affect page layout. For example, you can display or hide a congratulatory message next to a salesperson's monthly sales total in a sales report, depending on the sales total. After the control is displayed or hidden, Microsoft Access formats the section by using the values of format properties, such as CanGrow , CanShrink , HideDuplicates, KeepTogether, and Visible.
For changes that don't affect page layout or for event procedures or macros that should run only after the data on a page has been formatted, such as a macro that prints page totals, use the Print event for the report section.
There are times when Microsoft Access must return to previous sections on a report to perform multiple formatting passes. When this happens, the Retreat event occurs as the report returns to each previous section, and the Format event occurs more than once for each section. You can run a macro or event procedure when the Retreat event occurs to undo any changes that you made when the Format event occurred for the section. This is useful when your Format macro or event procedure carries out actions, such as calculating page totals or controlling the size of a section, that you want to perform only once for each section.
Macro
You can use the FormatCount property to check whether the Format event has occurred more than once for a section. For example, if a section doesn't fit on one page and part of it moves to the next page of the report, the Format event occurs once for each page, and the FormatCount property is set to 2. You can check the FormatCount property setting to determine how many times a Format macro has run per section.
You can use the CancelEvent action in a Format macro to cancel formatting of a section. If you cancel formatting, Microsoft Access doesn't format the section for printing and prints the next section instead. You can use the CancelEvent action to skip a section in a report without leaving a blank space on the page when the report is printed.
Example
The following example displays or hides a congratulatory message next to a calculated control that shows sales totals by salesperson. In report sections where the sales total is greater than the sales goal, a label named Message displays the message "Congratulations! You have met your sales goal" when the section is printed; in sections where the sales total is less than the sales goal, the label is hidden.
To try the example, add the following event procedure to a report that contains a label called Message, a text box called GrandTotal (a calculated control that displays the sales total), and a detail section called SalesDetail.
Private Sub SalesDetail_Format(Cancel As Integer, _
FormatCount As Integer)
Const conSalesGoal = 1000
If Me!GrandTotal > conSalesGoal Then
Me!Message.Visible = True
Else
Me!Message.Visible = False
End If
End Sub