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.