ID: Q100920
The information in this article applies to:
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article describes a routine to keep a given number of records together on a page with their group header. The article also explains how to avoid an abandoned group header at the bottom of a page and how not to print the group header beyond a certain position on the page in a single- column or multiple-column report.
NOTE: For Single columns, the KeepTogether property for groups in Microsoft Access 2.0, 7.0, and 97 gives you the ability to keep groups of similar information together. This property is available in the Sorting And Grouping dialog box for reports. However, this property only prevents page breaks from occurring within a group; it does not prevent column breaks. To prevent column breaks from occurring within a group, use one of the methods described in this article.
This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0
The KeepTogether property is used to keep all of the controls for one record together in a particular section of a report. This property does not keep multiple records together, nor does it keep a group header and its detail records together.
Method 1 below helps you to find the location on the page beyond which you should not print a group header if you want to keep a given number of records together with that header.
Method 2 below helps you to determine the twip location by trial and error and describes two sample functions, one for a single-column report and the other for a multi-column report.
To keep a set number of records together on a single page with the group header, you need to know approximately how many records will be printed per group, or, more importantly, how many records you want to print with a particular group header. You can preview your report to find the group that prints the most records to determine how many records to keep with the group header.
For example, say you have determined that the largest group prints 24 records. The detail section of your report is 1/4 inch in height; thus, 24 detail records take up 6 inches on a page. You must also consider the top and bottom margins, which default to 1 inch each, and the height of your group header section.
Using the Report.Top property, you can determine where on the page the next section will be printed. If enough space remains to print your group header, all of its detail records, and the page footer (if any), plus the bottom margin, your group header will be printed on the same page as its detail records. If there is not enough blank space, delay printing the report (use a combination of the MoveLayout, NextRecord, and PrintSection properties to leave the rest of the page blank) and continue the group header information on the next page.
Below are sample measurements on which this article's calculations are based:
Page Length: 11 inches
Detail.Height: 0.25 inch
Top Margin: 1 inch
Page Header.Height: 1 inch
Group Header.Height: 0.5 inch
Group Footer.Height: 0.5 inch
Page Footer.Height: 1 inch
Bottom Margin: 1 inch
-----------------------------
Total = 5 inches
Divide the maximum space for detail records by the Detail.Height property value (6 inches / 0.25 inch). A total of 24 detail records will fit on a page. Force a new page if fewer than 8 records will print with the header (for example, if the formula returns either less than the sum of 8 * 0.25 inches or less than 2 inches of blank space).
To determine where the top of the 2-inch limit is, or the point beyond which you should not print the group header, add the following values together:
Top margin 1 inch
+ Page Header.Height 1 inch
+ Remainder of detail section (6 inches - 2 inches)
= 1 inch + 1 inch + (6 inches - 2 inches)
To implement this calculation in a module, use the following steps:
1. Enter the =PrintOK() function in the OnPrint property of your
group header. (This function assumes a report name of MyReport.)
Option Explicit
Function PrintOK()
Dim R As Report
Set R = Reports!MyReport
If R.Top > (6 * 1440) Then
R.MoveLayout = TRUE
R.PrintSection = FALSE
R.NextRecord = FALSE
End If
End Function
Another way to avoid an abandoned group header at the bottom of a page, or not to print the group header beyond a certain position on the page, is to replace (6*1440) in the function above with the twip location beyond which you do not want to print.
The report tests the Top property; if the condition is True, the report does not print the section. Instead, it moves down the report layout and attempts to print the section until the condition is False, which occurs at the top of the next page.
For a multi-column report, the function must be modified as follows:
Option Compare Database
Option Explicit
'=====================================================================
'
' Function to force to new column if at bottom of column.
' R is the name of the Report being formatted, as in
' Reports![ReportName], and
' Bottom is the value (in inches) at which a new column is forced.
' This function should be called from the OnPrint property of the Group
' Header.
'====================================================================
Function IfBottom (R As Report, Bottom As Integer)
Dim YPos
Static LastPos
YPos = R.Top
If YPos > Bottom * 1440 Then ' Beyond this position, go to a new
' column.
R.MoveLayout = True
R.NextRecord = False
R.PrintSection = (YPos = LastPos) ' Set to True when at Max
' position.
LastPos = YPos
End If
End Function
Additional query words:
Keywords : kbusage
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowtoLast Reviewed: November 20, 1998