Optimizing Continuous Form Size: Part 6 (conclusion)

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
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form (this post)

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

VI. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

The purpose of this section is to provide information so that a person familiar with Access can incorporate this functionality into another Continuous Form.

The code used for Continuous Form sizing and positioning is contained within two modules, four small forms, and code within each Continuous Form. A sizable portion of the code copied from the sample form should be used without modification. A small portion of the code can be tailored. In this part of the document, each section of code is displayed and clearly marked.

To incorporate the sizing and positioning functionality into another Continuous Form, perform these steps:

A) Make sure you have these references to code libraries (or similar references for your version of Access):

B) Import the two sample database modules (make no changes):

- FormMetrics is a class module that provides various form measurements.

- modGlobalsAndMaxRows is a standard module containing global variables and the function "FnMaxRowsOnForm," which accepts the name of a form and its border style and returns the maximum number of rows that will fit in the form.

C) Import the four small sample database forms used for calculating border size (make no changes):

• - frmBorderStyle0None
• - frmBorderStyle1Thin
• - frmBorderStyle2Sizable
• - frmBorderStyle3Dialog

D) Import form "frmSampleListing3" from the sample database for convenience in copying its sizing and positioning code. This form can be deleted when finished.

E) Open "frmSampleListing3" in Design mode and click on the Code button to expose the code behind the form. Copy all code from the beginning down through the "End Sub" statement of the "Private Sub Form_Current()" event. Copying the remainder of the code is optional ("Sub Form_Close" and "Sub cmdCloseForm_Click").

Three Subs are required:

Private Sub Form_Open

Sub SetVerticalFormSize

Private Sub Form_Current

Insert the copied code into the code module of your Continuous Form. If you are starting with a new Continuous Form, it might be easier to copy all of the code from "frmSampleListing3" into the new form, then modify and delete code as needed.

F) Customize the copied code if desired, following these customization notes.

Notes about customization

Placement of the sizing and positioning code (the code in the Open event of the sample form) can be in either the Open or Load event. As far as I can determine, it's pretty much your choice. Upon opening a form, the Open event fires first, but since these are the first two events to fire, it seems not to make any difference where the code lies. If you insert the copied code into the Load event, change "Form_Open" to "Form_Load".

The other two Subs ("Sub SetVerticalFormSize" and "Private Sub Form_Current") need to be inserted as separate subs, apart from the Open event code.

Following are several snippets of VBA code taken from the code in the sample form. Each is explained as to its purpose and whether it is customizable.

These lines of code at the top of the Open event should remain without change. Of course, the references to "Open" could be "Load".

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

These "Dim" statements are used in determining the number of records to be displayed and in setting the form size, and are customizable.

-----------------------------------------------

-----------------------------------------------

This statement (optional):

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

in the Open event of each form limits Access to one form at a time visible on the screen. If the above statement is removed, also remove all the OnClose event code; it unhides (makes visible) the previous form (OnClose event code shown here):

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

The next section of code, beginning with the comment "Begin Form Metrics Code" and ending with the comment "End Form Metrics Code", is the heart of the Continuous Form sizing logic and should be used without alteration, except possibly the percentage of unused vertical space allocated above the form. It can be changed if desired ("Const sglPctSpAboveForm").

--------------------------------------------------------------------------------------------------------------------

 
(Click to enlarge)

(Click to enlarge)

---------------------------------------------------------------------------------------------------------------------

The next section of code obtains the number of data rows to be displayed and calls a subroutine to set the vertical size of the form based on that number. This code is customizable.

---------------------------------------------------------------------------------------------------------------------

(Click to enlarge)

---------------------------------------------------------------------------------------------------------------------

Since the form has been resized above, the next set of code acquires the current form measurements and positions the form vertically within the Access window. This code should be used without alteration.

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

The remainder of the code in the Open event of the sample form is comprised of common statements to close the recordset, return memory to the system and exit. The "Stop" and "Resume 0" statements provide flexibility in debugging. If an error occurs, an error message will be generated for the user, then the code will stop. The F8 key can be used to step the code to the "Resume 0" statement, which will return to the specific statement which caused the error—sometimes very helpful.

---------------------------------------------------------------------------------------------------------------------

The following "SetVerticalFormSize" subroutine is the code described on page 6.

---------------------------------------------------------------------------------------------------------------------

(Click to enlarge)

There is an anomaly in the code for which no explanation has been found. Even though there is no useful code in the OnCurrent event, the existence of the event skeleton seems to be required for error-free operation.

---------------------------------------------------------------------------------------------------------------------

The remainder of the code in each of the sample forms is comprised of normal event code for closing the form.

After you finish inserting and customizing the code, click on the "Save" button, then click on the "Debug" button in the top menu bar and click on the "Compile" option in the top line of the dropdown menu. Any errors will be called to you attention. Correct any errors and repeat the Save and Compile sequence until no more errors are noted. Then close the code window and open your Continuous Form, observing whether it accomplishes what you expected.

Best wishes for Continuous Form vertical optimization.

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

If you have any comments or corrections concerning this document or the sample database, please direct them to the author. The name and email address are near the top of the first page.

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

Optimizing Continuous Form Size: Part 4

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 (this post)
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

IV. Vertical Positioning

After determining the number of rows of data to be displayed (maximum or otherwise) and specifying the vertical size of the Continuous Form, consideration can be given to the vertical positioning of the form within the Access window. Horizontal positioning is not a consideration since it does not affect vertical sizing or positioning.

The specifics of satisfactory vertical positioning are pretty much individual preferences. Although the metrics from the form make it possible to vertically center the form within the Access window, vertical centering (particularly with small forms) does not seem to lend itself to comfortable viewing by the user. Positioning in the upper part of the Access window is generally preferable to positioning it lower.

The vertical positioning logic used in the sample forms favors the upper part of the window (30% of unused Access window space above the form), but can easily be changed. This amount was determined after testing, but is not based on any scientific value. Perhaps the suggestion here will spur your thinking toward something better.

The simple vertical positioning logic used in the sample forms is spelled out here in pseudo code:

Const sglPctSpAboveForm as Single = 0.30

'Since the form has been resized, get the form's current 'measurements in Twips, including the overall FormHeight.

Call mfi.GetSize ...

' Position form vertically in the Access window.

DoCmd.MoveSize , (WindowHeight - FormHeight) * sglPctSpAboveForm

The MoveSize calculation results in the integer number of twips for positioning the top of the form down from the top of the Access window.

A suggestion on moving the form:

There is probably something the author is not familiar with, but it seems that Windows 7 (don't know about XP) complains about moving a form unless it already contains records. You might want to try something different, but it appears to be necessary to use a "Requery" statement somewhere prior to using the MoveSize statement.

Next time: Sample Access Database Demonstration

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

Optimizing Continuous Form Size: Part 2

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 (this post)
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

II. Vertical Sizing by Hand Adjustment

In case the reader is not familiar with hand-adjusting the vertical size of a Continuous Form, here is a procedure for it. This method will cause the form to open in production mode to a fixed vertical size, the same size every time regardless of the number of rows of data.

In Design view, drag the bottom of the form down to the size you would like to see it when it opens in production mode. Then 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. When the form opens in production mode, it will open to your preset size. With records displayed, you can see how close to your desired size the form is set. If not satisfactory, the form can be opened again in Design view and the procedure repeated.

The inconvenience of this method is that unless you are prescient, the size of the Detail section may not quite display a full integer number of records or you may have oversized it and have empty space. Hand-adjustment is just a way to get close to a desired vertical size.

Next time: Vertical Sizing by Calculations

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