Optimizing Continuous Form Size: Part 5

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
4. Vertical Positioning
5. Sample Access Database Demonstration (this post)
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

V. Sample Access Database Demonstration

The sample Microsoft Access database associated with this document is named "Optimizing Continuous Form Size.mdb". It contains sample forms that can be opened/perused for demonstration of the concepts described in the previous sections of this document.

The sample database contains:

• 2 Tables:
  • - tblBooks
  • - tblBooksFew
• 8 Forms:
  • - frmBorderStyle0None
  • - frmBorderStyle1Thin
  • - frmBorderStyle2Sizable
  • - frmBorderStyle3Dialog
  • - frmSampleListing0
  • - frmSampleListing1
  • - frmSampleListing2
  • - frmSampleListing3
• 2 Modules.
  • - FormMetrics
  • - modGlobalsAndMaxRows

A "tour" of the Listing Forms will demonstrate the concepts and advantages of optimizing the vertical size of Continuous Forms.

Open database "Optimizing Continuous Form Size".

Open form "frmSampleListing0" in Design mode. While this form is a Continuous Form, it is a classic version, not having any vertical sizing and positioning logic. Notice the tiny vertical size with no detail section rows visible.

Close the form.

Open form "frmSampleListing0" in production mode (with the Open button or by double clicking on the name of the form). It opens to a small size but Access forces about 3 rows of data--even with no detail section rows showing initially in Design mode. You can see the rest of the data by using the vertical slide bar on the right of the form.

Close the form using the "Exit" button on the form.

Open "frmSampleListing0" again in Design mode and drag the lower border of the form down to about the 3-inch mark as shown by the scale on the left side of the form.

Click File | Save | File | Close. The final File | Close can be replaced with a close via the big X in the upper right corner of the form.

Open form "frmSampleListing0" in production mode. Notice that the form is approximately the size set in Design mode.

Close the form using the "Exit" button.

Open form "frmSampleListing0" again in Design mode and move the top of the form to the top of the Access window. Then drag the lower border of the form down as far as possible.

Click File | Save | File | Close. The final File | Close can be replaced with a close via the big X in the upper right corner of the form.

Open form "frmSampleListing0" in production mode. Notice that the form itself nearly fills the Access window top to bottom, but there may be a blank space in the form below the rows of data (on larger screens and screens with higher resolution), demonstrating that on its own, Access does not fit the size of a Continuous Form to the number of rows of data to be displayed.

Close the form using the "Exit" button.

It's strictly optional, but if you wish, you can open the form in Design mode and reset its size to what it was originally--very tiny--so others can follow the same tour, then Click File | Save | File | Close to save the form to its original size.

The next time the form is opened in Design mode, it will appear in the Access window in the same location it was when it was saved and closed.

The remainder of the tour of the sample database will use the other sample Listing Forms (1, 2, and 3), all of which include the same sizing and positioning logic.

frmSampleListing1

Open form "frmSampleListing1" in Design mode. Notice it is very long vertically.

Close the form.

Open form "frmSampleListing1" in production mode (with the Open button or by double clicking on the name of the form). It opens with 30 records displayed. In spite of its large vertical size in Design mode, the size of the form in production mode just fits the number of rows of data—a demonstration of optimal vertical sizing by VBA code. You can verify this by dragging the bottom border of the form down a bit to see that there is no data hidden. And the last row is not slightly pinched; the bottom border of the record selector box on the left is visible.

Close the form using the "Exit" button on the form.

frmSampleListing2

Open form "frmSampleListing2" in Design mode. Notice it is very small vertically, similar to frmSampleListing0.

Close the form.

Open form "frmSampleListing2" in production mode (with the Open button or by double clicking on the name of the form).

As opposed to frmSampleListing0, which opened with 3 records, this form is dynamically sized to fit all 10 records, all the data available. As with the previous form, you can verify this by dragging the bottom border of the form down a bit to see that there is no data hidden. Again the bottom border of the record selector box on the left is visible.

Close the form using the "Exit" button.

frmSampleListing3

Open form "frmSampleListing3" in Design mode. It is small but not tiny. The 1-inch mark should be visible in the scale on the lower left of the form.

Close the form.

Open form "frmSampleListing3" in production mode (with the Open button or by double clicking on the name of the form). It nearly fills the Access window top to bottom, such that there is not enough spare space to include an additional row of data—demonstrating the use of vertical size optimizing code. You can verify there are more records than can be displayed on the screen by noting that there is a vertical slide bar on the right of the form and the Navigation Button field at the bottom of the form shows "of 116", indicating there are 116 records available for this form.

Close the form using the "Exit" button on the form.

This ends the tour through the sample database, demonstrating Continuous Form vertical size optimization.

Next time: Adapting Dynamic Sizing and Positioning Code to Your Continuous Form