Optimizing Continuous Form Size: Part 1

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 (this post)
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations
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
I. Introduction - Continuous Forms
A "Continuous Form" is one of the form types that can be generated by Microsoft Access. It can display a variable number of rows of data with a fixed number of columns. This type of form is often used for displaying a list of items with related information such as Item Number, Item Description, Item Price, etc. Data fields are displayed in a format similar to a spreadsheet table.
This document 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. For continuous forms that do not fill the Access window top to bottom, a method of vertically positioning them is suggested.
Dynamically adjusting the size of continuous forms can be valuable, especially in Access applications that will be distributed to a number of locations having different screen sizes and different screen resolutions.
The sample Access database to which this document refers ("Optimizing Continuous Form Size.mdb") was developed using Access 2003 in Access 2000 file format, and has been tested successfully under Access 2000, 2003, and 2007. A bit of the code has run under Access 2010.
The following screen shot is a simple Continuous Form displaying a couple of fields from a table containing a list of books:

The number of rows of data displayed depends on the internal space defined for the form. Without adjustment, Access will display nearly a full integer number of rows, but does not quite do justice to the last row of data unless specifically set by the user. Left to chance or estimation, the last row may be just slightly "pinched" at the bottom. The bottom border of the last record selector box is not visible (not a serious problem). This screen shot shows an example:

The vertical size of the form can be hand-adjusted in Design view (usually with repeated adjustment and testing) to better fit an estimated number of rows of data--and that size will remain fixed when the form is opened in production mode.
If the "AutoCenter" property is set to "Yes", Access will attempt to center the form both vertically and horizontally in production mode, but the vertical size of the form itself remains fixed at the size set by the user in Design mode.
If the user attempts to maximize the form size for the Access window, hand-adjustment may leave the form too long, resulting in the last visible row of data partially showing, like this:

If the number of rows of data to be displayed does not fill up the specified size of the form, there will be a blank area between the last row of data and the bottom of the form. The next screen shot is an example of this case:

Without some method of dynamically adjusting the vertical size of the Continuous Form for the number of rows of data to be displayed, we must depend on hand-adjustment to a fixed size with trial and error and perhaps the occasional blank area.
Next time: Vertical Sizing by Hand Adjustment