Optimizing Continuous Form Size: Part 3

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations (this post)
4. Vertical Positioning
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

III. Vertical Sizing by Calculations

To dynamically adjust the vertical size of a Continuous Form to the number of records to be displayed (without empty space below the records), a programmed method can be used. In addition to adjusting the vertical size of the form, vertical placement within the Access window can be set by program code also.

This section explains how Access VBA (Visual Basic for Applications) code can be used to automatically calculate the optimum vertical size for a Continuous Form. The sample Access database accompanying this document has forms and code for demonstration. Topic V of this document (Sample Access Database Demonstration) describes how to tour the demonstration.

To complete this task, we need to know the vertical size of the Access window (sometimes called the "Form Client") and the vertical size of each of the elements of a Continuous Form that contributes to vertical size.

The vertical size of a Continuous Form is comprised of these individual elements:

• Border (both top and bottom border combined as one value)
• Caption Bar (sometimes called "Title Bar")
• Form Header
• Form Detail
• Form Footer
• Navigation Buttons

If we have the metrics (measurements) for the Access window and each of the elements above in twips (1440 twips/inch), we can calculate the maximum integer number of rows of a Continuous Form that will fit within the Access window on the particular screen being used.

Comparison of the maximum number of rows that can fit in the Access window with the number of rows of data to be displayed, can provide the basis for specifying the actual size and position of the form.

To get the vertical size of some of the form elements, form metrics code is incorporated from the Access 2000 Developer's Handbook, Volume I by Getz, Litwin, and Gilbert (Sybex), Copyright 1999, which was published Dec. 17, 1999, on a web site called "Office VBA", and with subsequent articles by Getz stating that the Developer's Handbook code needed to be supplemented with code he then provided. The code has been modified to fit the current application.

We start with a description of how each of the measurements can be obtained.

Access Window Height, Caption (Title) Bar Height

Code from Getz, et al, provides these.

Form Header, Form Detail, Form Footer

These values are readily available from Continuous Form properties:

• FormHeader.Height
• Detail.Height
• FormFooter.Height.

Navigation Buttons

The NavigationButtons property of a form can be tested (True/False) for the existence of Navigation Buttons. If they are present, their height can be determined by saving the value of the form's InsideHeight property, setting the NavigationButtons property to False and again saving the value of the InsideHeight property. The difference is the height of the Navigation Buttons.

On some forms this difference may occasionally be reported from Access as a value near 15 twips, when it should be around 250 twips. This incongruity–cause unknown–is handled reasonably well by an approximation. If the difference is reported as less than 216 twips, the value is set arbitrarily to 216, equivalent to 0.15 inches, which is close to being correct.

Border

The last measurement to be considered is Border. It takes more effort to obtain this value since there is no property to provide it. We can use the difference between the measured overall form height for a form with no borders and no Caption Bar (BorderStyle = 0) and the measured overall form height for the same size form having the border style of the Continuous Form, less the height of the Caption (Title) Bar.

A method which can be used to determine the height of the borders involves using four very small forms (2 inches by 2 inches in the sample database) that are identical except for identification, border style, and the name of the global variable to which each form height is saved. Upon opening, each of these small forms obtains its overall form height, saves that into its individual global variable, then closes itself.

In the Open event of Listing Forms 1, 2, and 3, in the sample database, the small form with BorderStyle0 (None) is opened to capture the overall form size for border style 0. The small form having the same border style as the Continuous Form is then opened to capture its overall form size. The calculated difference minus the height of the Caption (Title) Bar is the size of the two (top and bottom) borders taken together.

For border styles 1 and 3 (Thin and Dialog), the composite border size is a variation of the following equation for the Sizable border, style 2 (all values in twips):

CompositeBorderHeight = FormHeightBorderStyle2 - FormHeightBorderStyle0 - CaptionBarHeight.

Having determined the values for the height of the Access window and each of the six elements of the form, we can now determine the total vertical space available for rows of data in the form's Detail section. Dividing that total space available by the height of the Detail section gives the maximum number of rows of data that can be displayed on the form in the Access window. Here is the equation (all values in twips):

MaxRowsOnForm = (AccessWindowHeight - CaptionBarHeight - FormHeaderHeight - FormFooterHeight - NavButtonsHeight - BorderHeight) \ FormDetailHeight

Notice the backslash ("\") preceding FormDetailHeight. That forces an integer quotient.

Maximum Number of Rows possible versus Actual Number of Rows of data

After calculating the maximum number of rows that will fit in the Access window, there must be consideration of the actual number of rows of data to be displayed.

The actual number of rows of data available can be obtained from opening a recordset using the same SQL statement as the RecordSource property of the Continuous Form and capturing the RecordCount property, as demonstrated in the code for sample Listing Forms 1, 2, and 3.

The maximum number of rows that can be displayed is compared with the actual number of rows of data available and the following logic is applied:

If all rows of data will fit in the Access window (actual less than or equal to the maximum), the vertical size of the form is specified to accommodate the actual number of rows.

If the number of rows of data available is greater than the maximum that will fit in the Access window, the number of rows for the form is specified as the maximum. There may be a small space left below (and possibly above) the form, but there should not be enough extra space to display an additional row of data. In form "frmSampleListing3" in the sample database, the maximum number of rows displayed can be seen in the Navigation Buttons area by clicking the record selector on the last record visible when the form is open.

In Listing Forms 1, 2, and 3, in the sample database, the number of rows of data to be displayed is calculated and placed into a variable named " intNRows". Then a subroutine is called to set the vertical form size (maximum or otherwise). Here is the call statement:

SetVerticalFormSize Me, intNRows

The use of "Me" as a parameter provides to the subroutine the name of the Continuous Form to be sized.

The "SetVerticalFormSize" subroutine sets the vertical size of the Continuous Form by calculating the form's InsideHeight property using this equation contributed by Lambert Heenan:

InsideHeight = FormHeader.Height + (Detail.Height * intNRows) + FormFooter.Height

(where "intNRows" is the number of rows to be displayed--either the maximum number permitted by the height of the Access window or the number allowing display of all data without blank space in the lower portion of the form.)

Next time: Vertical Positioning