Chapter 12

Understanding Form Structures and Coding

Forms are the gateway to your data, and they help determine the ongoing quality of the data. Good form design ranks with good database design as one of the two most important components of an expert solution. The four chapters following this one provide a wealth of techniques for creating expert forms for your applications. However, before you can apply the techniques in those chapters, you must first possess some basic form development skills. This chapter teaches you the required skills by showing you how to:

Form Layouts at Your Fingertips


In the 1980s, R:BASE from Microrim was one of the leading database development products, and I was an R:BASE consultant in those pre-Access days. One thing I loved about R:BASE was that it stored definitions for forms and reports as text strings in a database table that was neither hidden nor locked. Once you figured out the data structures of these tables, you could do wondrous things to form and report designs manually.

For one thing, you could edit any form's definition directly. Because the form development interface was slow to load, it was actually faster to change a form definition directly by typing EDIT * FROM SysForm WHERE SysFName = 'Product' and manually editing the layout information. If you were really bold, you could update layout properties by running SQL UPDATE queries against these form and report layout tables, for example, to change the default PageSize property of all reports at once.

You could even use the definition tables to ask questions about your form and report data structures. For example, you could review the PageSize property for every report in the database by simply typing SELECT * FROM SysRep WHERE SysRData LIKE 'PageSize%'. Finally, you could easily write program code that walked through the form and report definition tables and produced documentation on all of those objects.

So, naturally, when I got my Access 1.0 alpha copy, I began looking for the form and report definition tables. Coming up empty handed, I asked the Access team how to locate them so that I could create a documentation add-in for Access. They thought I was nuts, and explained to me that the concept of exposing form and report structures went against two key Microsoft philosophies: (a) do not expose more of the product innards than necessary to the users because that would also expose the information to the competition; and (b) minimize support calls by protecting users from product features that, when used wrongly, could make their applications unusable.


To this day, the storage format of form and report definitions is not documented for developers. Perhaps Access forms load faster and support call frequency is lower as a result, but I still miss direct access to the information!

Using the Form Wizards in Access, you can shell out a usable form in a few minutes. For some users, when the wizard is finished, the form development is finished. For developers of expert forms, however, the work has just begun.

I use the term expert form to describe a form that meets a set of strict criteria for usability and functionality. If your application meets any of the following tests, you should be creating expert forms for your users:

If you answered yes to any of these questions, you should take your application's forms quite seriously. Forms are the primary user interaction component of your application and will play a key role in the success or failure-and consequently the acceptance-of the application.

The tangible tests for qualifying as an Access expert form using my ruler include the following:

The conceptual basis for expert forms, some of the simple techniques for creating good forms, and the importance of the items in the previous list are all discussed in various sections in Part I of this book, especially in Chapter 8, "Designing Effective Interfaces."

In this chapter and the next four, I will explore advanced, hands-on techniques for creating forms that meet the tests above and provide the highest level of usability possible, as follows:

How to Refer to Forms and Controls

Before delving into the advanced form techniques, I'll answer a common question: Why are there so many ways to refer to a form and its controls in Access? There are five different syntax variations for referencing a member of the Forms collection, as follows:

Forms!formname

Forms![formname]

Forms("formname")

Forms(expression)

Forms(indexnumber)


The Forms collection includes only open forms. See the section "Working with Form Design Information" later in this chapter to learn how to work with forms that are not open.

With this syntactic flexibility comes some confusion. In addition, the list of syntax options gets even longer when you consider the Me identifier, which is shorthand for "the form in which this piece of code is running." Let's sort through these options and make some sense of them.


Starting with Access 95, another syntax variation was introduced for referring to forms. The syntax applies to forms that have been opened via program code as class objects, and prefixes the form name with Form_, as described in the section "Using Forms as Class Modules" later in this chapter.

Object References with a Bang (!)

The exclamation point is also called the bang, as in the spoken phrase "Forms bang frmCust" to express Forms!frmCust. Throughout Access, the bang stands for, "look at a member of the object's default collection," even though its name is not necessarily specified. Put another way, think of the bang as a syntax to find an object or property that you have named (such as a form), and the dot as a reference to an object or property that Access has named (such as BackColor). So, the default member of the Forms object is a collection of loaded forms, each of which you named; thus, the syntax Forms!formname is legitimate.


An interesting sidelight to this syntactic structure is that you can have an object name that is the same as a property name, and use the appropriate syntax to refer to the desired item. Assume that you name a form control Filter, even though using reserved words as object names is not a good policy. The first syntax below would refer to the Filter control, while the second would refer to the form's Filter property:

Me!Filter ' Looks in the default collection (Controls) for the named control

Me.Filter ' Looks for a property with this name

Moving down one level, consider one specific form within the Forms object. The form has more than one collection-it has both Properties and Controls. In order for the bang to work, one collection must be designated as the default. In this case, the Access team at Microsoft coded the Controls collection as the built-in default, so the syntax pairs in Listing 12.1 are equivalent for a form assigned to the variable frmCust.

Listing 12.1-Form Syntax Pairs that Are Equivalent

' Create a form object variable

Dim frmCust As Form

Set frmCust = Forms!frmCust

' Now contrast equivalent syntaxes

frmCust.Controls!controlname ' Use the Controls collection name

frmCust!controlname ' Drop the default collection name

frmCust.Controls![controlname] ' Brackets are optional...

frmCust![controlname] ' ...and used when the name has spaces

frmCust.Controls("controlname") ' The controlname must be in quotes...

frmCust("controlname")

frmCust(expression) ' ...or as a variable or expression

frmCust(indexnumber) ' Used if you know the index number...

frmCust.Controls(indexnumber) ' ...of the control in the collection


In the examples in Listing 12.1, I used the object variable frmCust created at the top of the routine to make the code faster and more readable; but, the use of fully qualified references to the parent form in each of the displayed syntaxes is equally valid, as in:

Forms!frmCust.Controls!controlname

In contrast, because the Properties collection is not the default collection for a Form object, you could not refer to it directly with a bang; thus, the following syntax is not valid:

frmCust!propertyname ' Not valid


My example here is complicated by the fact that Access allows members of an item's Properties collection to be referred to directly with a dot, as in frmCust.propertyname. This is an anomaly/feature specific to properties; nevertheless, my example is still accurate and serves to illustrate the issue at hand.

With several options to choose from, the decision points used to determine how to specify collection members on forms and other objects are as follows:


If you have written any code against the Data Access Objects, you have already noticed that Jet supports and derives a performance boost from the bang syntax shortcut as well. The default member of a Recordset object is its Fields collection, thus the following syntaxes are legitimate for a Recordset object rst:

rstCust.Fields!fieldname ' Use the Fields collection name

rstCust!fieldname ' Drop the default collection name

Visual Basic developers will wonder why my syntax diagrams do not include the following two syntactic formulations familiar to them:

controlname

Me.controlname

With the addition of the VBA engine, Access now allows both of these types of references. However, they do not execute as fast as references with a bang. The Access group still prefers to use the bang for collection member references and the dot for property references, so I follow suit. The use of a non-qualified control name is also an ambiguous object reference, and I prefer my code more explicit. Consider this code:

Dim txtCompanyName As TextBox

Set txtCompanyName = Me!txtCompanyName

MsgBox txtCompanyName.BackColor

Does the MsgBox statement in the sample refer to the form control directly or to the object variable that represents it? Only Access knows, thus this coding model is imprecise and potentially dangerous.

The discussion of form references in this section and those that follow is directly relevant to working with reports as well. Substitute the objects Form and Forms with Report and Reports in the section, and the information provided is equally accurate.

Using the Me Designator

Starting with Access 2, the reference Me became an acceptable surrogate for the reference Form in Basic code. Nevertheless, I come across many Access users who have not yet made the switch to Me.

Me refers to the object that is running the current code, while Form refers to the Form property (the self-awareness or "form-ness") of the current form. In many cases, they are the same thing and are treated the same by Access, as when the following lines of code are placed in a form module:

MsgBox Me.Name ' Show the form name

MsgBox Form.Name ' Show the form name

Both of these code lines get a pointer to the form that contains the code, then they look at the Name property of the referenced object so both produce the same result (for example, the string frmCust). However, there are also situations in which the two identifiers behave differently. The difference stems primarily from the fact that Me is only available from Basic code, while Form is also available elsewhere in the form.

Technically, Me can be thought of as a Private form module-level VBA variable of type Form, created when a specific instance of a form is opened. Like a Private variable, Me has limited scope and is only available in the code behind its own form. Me always points to the form that contains the code procedure in which it is referenced, regardless of whether that form is the active form.

Like Me, the designator Form is available in Basic code. Additionally, Form can also be used in other elements of a form, including properties (such as the ControlSource and event properties of controls), and when referring to a form from code in other forms, from code in standard modules, and from macro actions.

Use the following points to understand the conceptual difference between Me and Form:

Forms!formname.Form

The following are three examples of the different usage of the two designators:

Me.Caption = "Customer" ' Set the form caption

Form.Caption = "Customer" ' Set the form caption

Call FrmCaptionSet(Me, "Customer") ' Set the form caption

Call FrmCaptionSet(Form, "Customer") ' Set the form caption

=[Form].[Filter] ' Retrieves the form's Filter property

=[Me].[Filter] ' Generates a #Name? error

=FrmRecStamp([Form],Now()) ' Runs the function successfully

=FrmRecStamp([Me],Now()) ' Generates an error at runtime

strPhone = Forms!frmCust!subCustPhone.Form!txtPhone

Noting that Form is more widely useful than Me in forms, which has the more limited scope, you are inclined to ask: "Under which circumstances is Me recommended?" Because you could use Form in all instances where Me also works, why not use it all of the time and discard Me? I offer the following three reasons for using Me where it is allowed:

The following are a few final hints for using Me in your form (and report and class) modules.

DoCmd.Close ' Closes the form with the focus

DoCmd.Close acForm, Me.Name ' Closes the form running this code line


The use of Screen.ActiveForm instead of Me is a frequent source of application errors among coders who are new to the event model in Access.

Listing 12.2 AES_Frm0.Mdb-A Hard-Coded Routine to Affect a Form Property
Change

Private Sub cmdChange_Wrong_Click()

' Purpose: Set this form's caption the wrong way

Call FrmCaptionSet_Wrong(1, "New Caption Set - Hard-coded")

End Sub

Public Sub FrmCaptionSet_Wrong(rintForm As Integer, rvarCap As Variant)

' Purpose: Change the caption of a form by its index

' Decide which form to change

Select Case rintForm

Case 1

Forms!frmMe.Caption = rvarCap

Case 2

Forms!frmAddNew.Caption = rvarCap

End Select

End Sub

Listing 12.3 AES_Frm0.Mdb-A Generic Routine to Affect a Form Property
Change

Private Sub cmdChange_Click()

' Purpose: Show how to call a truly generic routine

Call FrmCaptionSet(Me, "New Caption Set - Generic Routine")

End Sub

Public Sub FrmCaptionSet(rfrm As Form, rvarCap As Variant)

' Purpose: Change the caption of the specified form

' Arguments: rfrm:=Form to change

' rvarCap:=New caption

rfrm.Caption = rvarCap

End Sub

Note that passing Me into the procedure above actually passed a variable of type Form. This feature enables your older code, which used to refer to Forms!formname explicitly, to be changed in many cases to refer to a Form type variable instead. In larger blocks of code, this new structure has the added benefit of increased performance because multiple references to the same Form object variable execute significantly faster than multiple references to the Forms collection.


The discussion of form references in this section is directly relevant to working with reports as well. Substitute the words Form, Forms, SubForm, formname, and ActiveForm with Report, Reports, SubReport, reportname, and ActiveReport in the section and the information provided is equally accurate. The use of Me in report module code is identical to its use in forms as described here.


Working with Form Design Information

You do not have direct access to the saved layout information used by Access to draw a form. Form (and report) definitions are stored in a system table in your database as binary data, and the storage structure is not documented.

Therefore, in order to make Form Wizards, Property Wizards, and builders function, Access must support the ability to open Form and Report objects in design view and manipulate their properties programmatically. Microsoft's implementation of this activity in Access is not as philosophically "clean" as modifying database object properties in the DAO because modifying a form from code involves opening the form to make the design changes. Nevertheless, it works just fine.

Getting to a Form's Design

You can review or modify a saved form definition by opening the form in design view, changing properties, then closing and saving the form. This is simply an automated variation of the steps you would take manually to do the same process via the Access interface.

To open a form in design view, use the syntax below in a VBA procedure, replacing the formname placeholder with the name of a form in your database.

DoCmd.OpenForm formname, acDesign, , , , acHidden


The constant, acHidden, in the window mode argument will open the form without displaying it. If you do not include this constant, the user will see the form opening in design view, which may or may not be desirable.

Once the form is open, you can perform the following actions on it:

A form opened for design becomes part of the Forms collection of open forms, despite the facts that it is open in design view and that it is hidden. Therefore, you can refer to it with the same syntax variations you would use for referring to any open form:

Forms!formname

Forms![formname]

Forms("formname")

Forms(expression)

Forms(indexnumber)

Your code will be easier to read and modify, and will run faster, if you create a form variable whenever you work with the design of an open form. Once the form is open in design view, create a Form object variable and set it equal to the open form:

Dim frm As Form

DoCmd.OpenForm formname, acDesign, , , , acHidden

Set frm = Forms!formname

You can now refer to all properties and controls of the open form through this object variable. To refer to the properties of an open form, whether open in form view or design view, use the Properties collection of the form, which contains all the properties by name. For example, you can use a string variable along with the form variable frm to retrieve the RecordSource property from an open form, using any of these syntaxes:

' The direct approach

strSrce = frm.Properties("RecordSource")

' Property name "RecordSource" is in a variable

strSrce = frm.Properties(strRecSrce)

' Shorter, direct syntax

strSrce = frm.RecordSource

Although the third syntax above is more common, shorter to type, and faster to run, the second syntax is useful when you need to supply the property name in a variable or as an argument to a procedure. The first syntax is infrequently used simply because it wastes space and typing effort. An example in the following section shows these techniques.

To refer to a control on an open form, whether open in form view or design view, use the Controls collection of the form, which contains all the controls by name, or use a bang to refer to the control directly. See the examples in the "Object References with a Bang (!)" section earlier in this chapter.


The discussion of form references in this section is directly relevant to working with reports as well. Substitute the words Form, Forms, formname, OpenForm, CreateControl, and DeleteControl with Report, Reports, reportname, OpenReport, CreateReportControl, and DeleteReportControl in the section, and the information provided is equally accurate.

Performing Bulk Form Operations

There are times when you want to get a list of all forms in a database or open more than one form at a time, perhaps to perform bulk operations on multiple forms in an application. To work with multiple forms, your code must "walk" the list of saved forms and open each one by name. Because the Forms collection refers only to forms that are open, you will need to find another way to get a list of forms in a database.

The list of all saved forms in an Access database is held in the Forms container in the Containers collection, maintained by Jet in the Data Access Objects hierarchy. The Containers collection is a Jet object that has multiple containers, some defined by Jet (such as Tables) and some defined by Access (such as Forms and Reports). One of the purposes of a Container object is to store references (called Documents) to objects created by Access that are not native to Jet (see Figure 12.1). Jet must maintain references to non-Jet objects so that it can implement its security model on those objects for the benefit of Access, for example, to enable you to set security permissions on forms.

Fig. 12.1

Form names are stored as Document objects.

The Forms container has one Document object for each saved form; each document has maintenance and ownership information for the form. The form's actual design layout is not known by Jet and is not in the Document object, but instead is stored in a system table maintained by Access and hidden from users.


Forms are stored in a format known only to Access. Therefore, other products that can call Jet-Visual Basic, for example-cannot load and use the forms you create in Access. This is also why you cannot create, append, open, close, and otherwise manipulate Form objects through Jet in the same manner you work with TableDefs and QueryDefs; instead, you must use Access commands like OpenForm to do the work.

Because Access' forms engine is proprietary and difficult to separate from Access, it was not used as the basis for the new Forms 2 engine implemented in Excel, PowerPoint, and Word 97. Similarly, Access's forms model was too proprietary to Access to replace with Forms 2. Thus, Access has a different set of form services than the other Office applications.

Knowing where the list of saved form names is stored, you can now walk through the list sequentially, using these steps:

  1. Open the container of forms documents.
  2. Get the name of each document (form).
  3. Open the named document (form).

Listing 12.4 shows an example of this technique. The example goes through the list of saved forms, opens each form, and searches for occurrences of a specific value within a specific property. Figure 12.2 shows the result of running the listed code in the Debug window.

Listing 12.4 AES_Frm0.Mdb-Opening Each Form in a Database and Reading a Specific Property

Function FrmPrpFind(rstrPrpName As String, rvarPrpVal As Variant) _

As Boolean

' Purpose: Open all forms in design view hidden and look

' for a specific property value in the named property

' Arguments: rstrPrpName:=property to search

' rvarPrpVal:=value to find

' Returns: True/False, True=found at least one

' Example: FrmPrpFind("AutoCenter", False)

Dim con As Container

Dim dbs As Database

Dim edoc As Document

Dim frm As Form

Dim iintCon As Integer

' Open the Forms container

Set dbs = CurrentDb

Set con = dbs.Containers!Forms

' For each document (Form object) in the container

For Each edoc In con.Documents

' Open the form in design view

DoCmd.OpenForm edoc.Name, acDesign, , , , acHidden

Set frm = Forms(edoc.Name)

If frm.Properties(rstrPrpName) = rvarPrpVal Then

Debug.Print "Property value " & rstrPrpName & " = " & rvarPrpVal _

& " was found in form " & frm.Name

FrmPrpFind = True

End If

DoCmd.Close acForm, frm.Name

Next

End Function

Fig. 12.2

This is the result of searching all forms for a specific property.

As a developer of expert forms, you will use this technique frequently to perform bulk form operations. The following are a few ideas:

Using Custom Form Properties

Access has a rich built-in property model for forms. There are times, nevertheless, when it is necessary to add your own properties to forms. Access now provides two ways you can approach this situation, both built around variables.

Setting Custom Properties Using Variables

The simplest way to create a custom property is to create a module-level variable in the Declarations section of the form:

Public pstrCalledFrom As String ' Name of the calling form

The variable becomes a repository where you can store information about the form; thus, it is similar to a property attached to the form. You address the variable as a property of the form from code outside the form by using the class name of the form, as in this syntax:

Form_formname.varname

The syntax for addressing form class objects with Form_formname was introduced in Access 95. If you are not familiar with this syntax, study the following Caution item and then read "Using Forms as Class Modules" later in this chapter.


The following syntax is also valid, but not in the context you would expect:

Forms!formname.varname

The latter syntax refers to a member of the Forms collection opened via the OpenForm method or the user interface, while the former syntax refers to a programmatic instance of the form class. The code in Listing 12.5 clarifies what is happening and why a programmatic reference to a form must explicitly indicate the desired form instance.

Listing 12.5 AES_Frm0.Mdb-Using Multiple Syntax Variations to Open Forms Produces Multiple Instances

Form_frmOrigin.Visible = True ' Open a programmatic instance

DoCmd.MoveSize 10, 10 ' Move to the upper left

DoCmd.OpenForm "frmOrigin" ' Open the default instance


DoCmd.MoveSize 1200, 1200 ' Allow both to show

' At this point there are two forms open

Form_frmOrigin.lblOrigin.Caption = "Opened from code"


Forms!frmOrigin!lblOrigin.Caption = "Default instance"

When addressing the variable from elsewhere within the host form's module (as long as it has been declared Public), you can use the fully qualified syntax or simply use the name of the variable directly-as you would with any other variable:

Form_formname.varname = value

Me.varname = value

varname = value


Even though you are using the variable as a transitory property, you cannot address it using the following syntax, as you would with a built-in property:

Form.varname

If you reuse variable names, your code may be hard to read with respect to scope. You can legitimately create variables in a form procedure, a form module, and a standard module that all have the same name. When your code calls any one of these variables, it may be hard for the reader, and sometimes even Access, to determine which variable you intended to actually address. Judicious use of naming conventions, as detailed in Chapter 6, "Leszynski Naming Conventions for Access," and demonstrated throughout this book, will help alleviate such problems.

As an example, assume that your application has a customer address form, frmCustAddr, that can be opened from several other forms. When this form is opened, it needs to know which of the other forms called it so that it can send information back to the calling form. The form frmCustAddr contains a module-level variable declaration for pstrCalledFrom, as shown at the beginning of this topic. A code snippet that opens the form and sets the custom property variable looks like this:

DoCmd.OpenForm "frmCustAddr"

Form_frmCustAddr.pstrCalledFrom = Me.Name

Once set, the variable's value is available until the form that contains it is closed. Code behind the form that contains the property can use the property setting at will:

Forms(Me.pstrCalledFrom).Requery ' Requery the calling form

Of course, creating custom form properties using this technique has limitations, including the following:

Despite the limitations, setting custom properties via variables provides a clean, simple syntax for adding information to a form at execution time.

Setting Custom Properties Using Property Let

A more powerful way to create and invoke custom form properties is found in the new property procedures of Access. There are three varieties, as follows:

As in the previous section's examples, the storage device for these custom form property procedures is a module-level variable. However, you now have the option to make the scope of the variable either Public or Private. In general, you will use Private.


If your property variable is declared as Public when using property procedures, then code outside the form can affect the variable's value without invoking the corresponding Property Let procedure. This circumvents the property code you have written and may lead to unexpected behavior in your application.

The purpose of property procedures is threefold, as follows:

Philosophically, custom property procedures provide developers with the tools in Access to create tighter class definitions. A saved form is a class module with properties and methods that provide a template for forms built on the class. Property procedures provide the mechanism for creating the custom property definitions that are saved with the class. See the section "Using Class Modules" later in this chapter for more details on form and standard class modules.

Let's explore a set of property procedures in use. The form for our example is frmCust, a simple form enabling entry and editing of multiple company name records. The form can be opened by several different code procedures in your application, and you need to be able to open the form in different data modes from the various procedures.

Although you can use various arguments of the OpenForm method to tailor the data entry mode of a form, it is much cleaner programming style to be able to simply set a custom property value on the form (for example, prpFormMode). This enables code to run when the property is set and also allows any code to query the value of the form's custom property.

To create the custom property prpFormMode, you must first create a storage variable. Because two procedures, Property Let and Property Get, will both be making use of the variable, it must have module-level scope. For this example, we'll declare the following variable in the form module's Declarations section:

Private mstrFormMode As String


I find property code more readable if the property variable and its property procedures share the same base name (in this case, prpFormMode).

Next, create the Property Let procedure. This function will accept the property value from the calling code and determine what actions to take. If the argument value is valid, the function should assign it to the storage variable, thus creating the form property. The syntax diagram for a Property Let procedure looks like this:

[Public | Private][Static] Property Let procname ([arglist], value)

Listing 12.6 shows the Property Let function for the prpFormMode property.

Listing 12.6 AES_Frm0.Mdb-A Property Let Procedure to Assign the Value
of a Custom Property

Public Property Let prpFormMode(rstrMode As String)

' Purpose: Set the form mode when the form opens

' Arguments: rstrMode:=Form mode: Add/AddEdit/Browse/Edit

' Pseudocode:

' 1. Check the form mode argument for validity

' 2. Enable/disable buttons and features depending on argument

' 3. Set the custom property equal to the argument

On Error GoTo prpFormMode_Err

Const cstrProc As String = "prpFormMode"

' All form buttons are enabled on the saved form

' Disable specific buttons/properties based on the property

Select Case rstrMode

Case "Add" ' All buttons are allowed

Me.DataEntry = True ' Data entry only

Case "AddEdit" ' All buttons are allowed

' Nothing to do

Case "Browse" ' No editing allowed

Me!btnAdd.Enabled = False

' Make the form read-only

Me.AllowAdditions = False

Me.AllowEdits = False

Me.AllowDeletions = False

Case "Edit" ' No add allowed

Me!btnAdd.Enabled = False

Me.AllowAdditions = False ' No new records

Case Else

Err.Raise 65535 ' Raise a custom error

GoTo prpFormMode_Exit

End Select

mstrFormMode = rstrMode ' Set the property variable

Me.Caption = Me.Caption & " [" & rstrMode & "]" ' Show mode in caption

prpFormMode_Exit:

Exit Property

prpFormMode_Err:

If Err.Number = 65535 Then ' Customize the error

Call lci_ErrMsgStd(Me.Name & "." & cstrProc, Err.Number _

, "Invalid value passed to property.", False)

Else

Call lci_ErrMsgStd(Me.Name & "." & cstrProc, Err.Number _

, Err.Description, False)

End If

Resume prpFormMode_Exit

End Property

The property procedure is called by addressing the name of the property function attached to the class name of the form, as in the following code:

DoCmd.OpenForm "frmCust"

Forms!frmCust.prpFormMode = "Add"


When calling a property procedure in the current form from code within the same form, you can use the Me keyword:

Me.prpFormMode = "Add"

Additionally, a form does not need to be open before setting a custom property from code. If you issue a command that creates a new class instance of the form, Access will open it hidden first, then set the property:

Form_frmCust.prpFormMode = "Add"

Note the following important features of the property function:

To retrieve the custom property setting created by a Property Let procedure, create a Property Get procedure of the same name, as in Listing 12.7. This procedure must have its return value set to the same data type as the property storage variable and the argument in the corresponding Property Let procedure. Your code calls the Property Get procedure using syntax structured like the following example:

If Form_frmCust.prpFormMode = "Add" Then

Listing 12.7 AES_Frm0.Mdb-A Property Get Procedure to Retrieve the
Value of a Custom Property

Public Property Get prpFormMode() As String

' Purpose: Return the saved form mode

prpFormMode = mstrFormMode ' Return the stored property value

End Property

The advantage of the property procedure technique over setting a module variable directly, as shown in the previous section, is the ability to run code routines as an event response to the setting of the custom property. Additionally, property functions are exposed to you and other developers who browse the form in the Object Browser. Figure 12.3 shows the custom prpFormMode property listed with the other properties for its form.


A form property is read-only when you create a Property Get function on a form, but do not create a corresponding Property Let function. Code in the form module must set the value of the property variable directly before you can use the Property Get function to retrieve the value in the variable.

Fig. 12.3

Custom property procedures for a form are listed in the Object Browser.


The data type and function structure for a custom property can be discerned from the syntax diagram for its Property Get function, as shown in the lower portion of the Object Browser dialog in Figure 12.3.

Property procedures enable you to extend your forms by creating custom properties for specific application needs.


Property procedures can be used with reports as well. The syntax is identical, except that you use the class descriptor for addressing Report objects-Report-where I have used the descriptor Form in this chapter, as in this example:

Report_rptCust.prpRptMode

Using Property Procedures with Objects

Property Set procedures are used when the procedure argument, or property value, must be an object data type. Property Set procedures are structured and used identically to Property Let procedures, with the following exceptions:

Consider form frmCust again. You want this form to set a property that points to the last edited text box control so that other code in your application can check on the properties of the control. The code in Listing 12.8 provides a way to do this. This Property Set procedure accepts a form control as its argument and saves a handle to the control in the custom property.

Listing 12.8 AES_Frm0.Md-A Property Set Procedure to Set a Custom Property to an Object Pointer

Public Property Set prpCtlLastEdited(rctl As Control)

' Purpose: Point to the last edited Text Box control

' Arguments: rctl:=Control on the form

On Error GoTo prpCtlLastEdited_Err

Const cstrProc As String = "prpCtlLastEdited"

If rctl.ControlType <> acTextBox Then ' Only allow text boxes

Err.Raise 65535

Else

Set mctlCtlLastEdited = rctl ' Set the property variable

End If

prpCtlLastEdited_Exit:

Exit Property

prpCtlLastEdited_Err:

If Err.Number = 65535 Then ' Customize the error

Call lci_ErrMsgStd(Me.Name & "." & cstrProc, Err.Number _

, "Invalid control type passed to the property.", False)

Else

Call lci_ErrMsgStd(Me.Name & "." & cstrProc, Err.Number _

, Err.Description, False)

End If

Resume prpCtlLastEdited_Exit

End Property

To set the custom property, use the Set keyword and pass an object to the property procedure:

Set Me.prpCtlLastEdited = Me!txtCompnyName

The Property Get to match this Property Set procedure is shown in Listing 12.9. Notice that its return value is an object.

Listing 12.9 AES_Frm0.Mdb-A Property Get Procedure to Retrieve a Custom Property that Is an Object Pointer

Public Property Get prpCtlLastEdited() As Control

' Purpose: Retrieve the last edited Text Box control

Set prpCtlLastEdited = mctlCtlLastEdited ' Get the property variable

End Property

Code elsewhere in your application can now access any property of the control pointed to by the custom property. For example, this syntax calls the Property Get procedure from Listing 12.9 and returns the ForeColor property of the most recently edited text box on frmCust:

lngColor = Form_frmCust.prpCtlLastEdited.ForeColor

Advanced Uses of Property Procedures

As with any other function, property procedures allow multiple arguments. When you define more than one argument in Property Let and Property Set procedures, use the last (rightmost) argument to assign the property value. Because the last argument is passed in from the right side of the equal sign in a property assignment, only the other arguments need to be passed in to the function by placing them inside parentheses. Figure 12.4 is a diagram showing how Access passes property arguments to a pair of sample property procedures that have three arguments.

Fig. 12.4

This diagram shows how Access passes custom property procedure arguments to property procedures.

The capability to pass multiple arguments to property procedures enables you to provide as much context and information to the procedures as is required to set the custom property value. It also enables you to create property arrays using property procedures.

For example, a property procedure with two arguments can be coded to use the first argument as an array index and the second (last) argument as the property value to place in the array at the specified index. Assume you have an array dimensioned in your form module as follows:

Private mavarDayComment(vbSunday To vbSaturday) As Variant

The array has seven elements (rows), one for each day of the week. Through property procedures you can use this array to store seven values under one custom property name (prpDayComment). The Property Let and Property Get function definitions for such a technique are shown in Listing 12.10.

Listing 12.10 AES_Frm0.Mdb-Property Let and Property Get Procedures to
Work with a Custom Property Array

Public Property Let prpDayComment(rintIndex As Integer _

, rvarComment As Variant)

' Purpose: Set a comment property setting for a specified weekday

' Arguments: rintIndex:=Day of week, vbSunday through vbSaturday

' rvarComment:=Comment

If rintIndex < vbSunday Or rintIndex > vbSaturday Then

Exit Property ' Don't accept invalid property setting

' Alternately, you could trigger a custom error here

End If

mavarDayComment(rintIndex) = rvarComment ' Set the property

End Property

Public Property Get prpDayComment(rintIndex As Integer) As Variant

' Purpose: Return a comment property setting for a specified weekday

' Arguments: rintIndex:=Day of week, vbSunday through vbSaturday

If rintIndex < vbSunday Or rintIndex > vbSaturday Then

Exit Property ' Don't accept invalid request

' Alternately, you could trigger a custom error here

End If

prpDayComment = mavarDayComment(rintIndex) ' Get the property

End Property

These property procedures create a custom form property with seven elements, prpDayComment(1) through prpDayComment(7). Lines of code to set the custom property look like this:

Me.prpDayComment(vbMonday) = "Company meeting"

Forms!frmCalendar.prpDayComment(vbMonday) = "Company meeting"

Form_frmCalendar.prpDayComment(vbMonday) = "Company meeting"


Property procedures can also be created for standard modules. Such custom properties essentially become properties of the database file (application) itself. Custom properties attached to standard modules can be addressed with the property name only, as in prpUserName, or with the module name as a class designator, such as basLogin.prpUserName. I recommend the latter syntax for clarity of code.

If you intend to create custom properties in a standard module, define it as a class module when you first create it.

Using Class Modules

Access now fully supports the concept of class modules. A class is a saved object definition that includes properties and methods. In Access 95, class modules were implemented only through the Form and Report objects. Access 97 expands this concept to include the ability to have standard class modules of type Module, stored in the Modules tab of the Database window. The following are important points about class modules:


Alternately, you can set the Hidden attribute of a form using the Properties dialog box for the form, available from the View, Properties menu option. Once hidden, they are not displayed to users in the Database window. Consider hiding the forms that you use as class modules from code only if you decide not to convert them to standard classes.

For the purposes of this chapter's discussion, I'll mix references to form and standard class modules, because they are coded the same even though they may be used slightly differently.

Creating and Using Class Modules

The New keyword is used to create a new instance of an object. When the New keyword is used while declaring a form or class object variable, a new instance of the form, form class module, or class module is created. The instance is loaded into memory but is not displayed to the user until the object's Visible property is set to True. The following code creates a form variable and opens an instance of the form frmMultiInst, but does not display the form:

Dim frmMultiInst As New Form_frmMultiInst

A variable declaration that defines an object without using the New keyword creates a placeholder for the object, but is set to the value Nothing until an actual object (a form, for example) is assigned to the variable with the Set statement. Therefore, the next two lines of code achieve the same result as the previous single line of code:

Dim frmMultiInst As Form_frmMultiInst

Set frmMultiInst = New Form_frmMultiInst

In most cases, using the New keyword in the variable declaration is the appropriate approach because it creates both a variable and an instance of an object in the variable with a single command.

As an example of a class module, assume that your application requires reusable code routines to validate part numbers. One approach would be to place the same validation logic in every form procedure in the application that must validate a part. An alternative approach would be to place the validation code in a common library module. The newest approach is for you to create a class module to do the work. Because a class module is self-contained, it can be called from any place in the application, making it highly reusable and centralized. More importantly, the self-contained class module can be imported into multiple applications as a reusable validation object throughout your company.

We've already seen in this chapter how custom property procedures can be added to a class module. For the validation engine, create a class module called clsPartValidate (we'll use a standard class module because no interface is needed; alternately, using a form as a class module would work just as well but incur the performance burden of calling the forms engine to load it). Add a custom property, prpPartNum, to hold the part number passed in by any code that wants to use the validation class, as shown in Listing 12.11.

Listing 12.11 AES_Frm0.Mdb-Property Let to Define the Custom Part Number Property

' In the Declarations section of the class module

' Store the PartNum custom property

Private mvarPartNum As Variant

' In the code inside the class

Public Property Let prpPartNum(rvarPart As Variant)

' Purpose: Set the part number property

' Arguments: rvarPart:=Part number

mvarPartNum = rvarPart ' Set the property variable

End Property

Notice that Listing 12.11 does not include a Property Get sibling for the Property Let procedure. The validation engine accepts and checks part numbers, but has no need to return the part number to the calling program because the program sent it to the class in the first place.

Next, create a custom method, mtdPartValidate, for the class. Custom methods are simply procedures within a class that are declared Public, so that they can be invoked from anywhere in the application. Listing 12.12 shows a sample method to validate the part number set by the custom property in Listing 12.11.

Listing 12.12 AES_Frm0.Mdb-A Custom Part Number Validation Method in
a Class

Public Sub mtdPartValidate()

' Purpose: Validate the part number property set in mvarPartNum

Const cintFail As Integer = vbOKOnly + vbCritical

Const cintPass As Integer = vbOKOnly + vbInformation

Const cstrProc As String = "mtdPartValidate"

' Part number must be like A9999

Beep

If lci_IsBlank(mvarPartNum) Then

MsgBox "Part number cannot be blank.", cintFail, cstrProc

ElseIf Len(Trim(mvarPartNum)) <> 5 Then

MsgBox "Part number must be five characters.", cintFail, cstrProc

ElseIf Left(UCase(mvarPartNum), 1) < "A" _

Or Left(UCase(mvarPartNum), 1) > "Z" _

Or Not IsNumeric(Mid(mvarPartNum, 2)) Then

MsgBox "Invalid part number format, must be like A9999." _

, cintFail, cstrProc

Else

MsgBox "Part number is valid.", cintPass, cstrProc

End If

End Sub

Once the class module has a property and a method, it can be used for part number validation from code. Because the class was created to serve as a background engine, it has no user interface and is never displayed to the user while open. The code in Listing 12.13 shows how to create an instance of the class, pass a part number to it, and initiate the validation routine.

Listing 12.13 AES_Frm0.Mdb-Invoking the Property and Method in a Custom
Class

Dim clsPartValidate As New clsPartValidate

clsPartValidate.prpPartNum = Me!txtPart ' Set the part number property

clsPartValidate.mtdPartValidate ' Invoke the validate method

Set clsPartValidate = Nothing

Using Forms as Class Modules

A form opened by a user from the Database window or OpenForm in code is called the default form, because the object instance is unique to the application environment, meaning that only one instance of a specific form can be open at a given time if any of these invocation methods are used:

Unlike the default instance of a form, a form class module can be opened more than once from code and does not need to be explicitly closed, because it is created via a code reference or explicit variable. When the variable or procedure goes out of scope then the class form is closed by Access.


Despite the fact that Access closes the form class object for you, it is considered good programming practice to explicitly release the memory used by any object by setting the object variable to Nothing, as shown for the class module in Listing 12.13. I have seen rare instances when some objects will generate errors if they go out of scope without an explicit cleanup.

Your code can also create an instance of a form class without creating a variable by simply addressing it, as in this code:

Form_frmMultiInst.Visible = True

However, this creates an instance of the class with no attached object variable, which has the following potential pitfalls:

Form class objects in Access have some limitations. The most glaring is that the classes in a referenced database are not available to a referencing database, which would provide the ultimate in object reusability. You can create a Public function in a standard module of a referenced database, which can be called from referencing databases, and have that function invoke a class object in the referenced database, providing a workaround.

Another limitation is that class objects cannot be used in expressions that are handled by Access's expression service, which does not understand class objects. Thus a form or standard class module's custom property, for example, cannot be used in the Where clause criteria of a query. Consider a form frmCust, open as a class object, with a custom property prpCustNum. Assume that the following line of code properly retrieves the Customer Number property in the Debug window:

Debug.Print Form_frmCust.prpCustNum

In spite of this visibility of the form class's property to the Debug object and other code running in the application, a query would not work correctly if it attempted this syntax:

WHERE tblCust.CompnyName = [Form_frmCust].[prpCustNum]

A third limitation is that custom property information for a form class is erased by print preview mode. If your form class is displayed to the user and the user selects File, Print Preview from the menu, this action clears the module-level variables in the form class that hold your custom property settings. Upon returning to form view from preview mode, the form may no longer work as expected.

Despite these few limitations, class definitions in Access are still a powerful tool for creating reusable objects within an application.

Creating Multiple Form Class Instances

Because each instance of a class is a unique object, you can open multiple object variables against a single class definition. Therefore, if you had two forms open in an application to accept part numbers, each form could start and work with its own instance of the part validation class described earlier. Two procedures can contain exactly the same code to create exactly the same object variable, yet each procedure creates a unique instance of the object.

A class instance is destroyed when the code that creates it goes out of scope, so a form displayed to the user from code may not persist long enough for the user to manipulate it. The following code displays the target form for only an instant, but does not do so by displaying the default form as described above. Thus, the form is destroyed when the procedure ends:

Private Sub cmdOpen_Click()

' Open a form through code

Dim frmMultiInst As New Form_frmMultiInst

frmMultiInst.Visible = True

End Sub

The form is destroyed immediately because the procedure cmdOpen_Click stops running and the variable frmMultiInst goes out of scope. Here are three ways you can create a form through code and circumvent this problem:

Listing 12.14 AES_Frm0.Mdb-Opening a Form Instance that Persists Using
a Module-Level Variable

' In the calling form's Declarations section

Dim mfrmMulti As Form_frmMultiInst

' In the calling form

Private Sub cmdOpen_Click()

' Purpose: Open a form instance

Set mfrmMulti = New Form_frmMultiInst

mfrmMulti.Visible = True

End Sub

Listing 12.15 Opening a Form Instance that Persists Using a Static Variable

Private Sub cmdOpen_Click()

' Purpose: Open a form instance

Static sfrmMultiInst As New Form_frmMultiInst

sfrmMultiInst.Visible = True

End Sub


Even though I show this Static instance technique for demonstration purposes, I do not recommend it. If the user closes the form manually, the form instance is destroyed; but the Static variable that refers to it is not alerted and does not release its memory. Application errors can be generated when this happens.

One of the great benefits of opening a form instance through code is the ability to set properties on a modal Form object from subsequent code. If the following lines of code employing OpenForm are used to open a form as a modal dialog box, the second line of code does not execute until the form is closed-the modality of the form causes the code to pause at the OpenForm method:

DoCmd.OpenForm "frmMultiInst", , , , , acDialog ' Open a dialog box

' The next line is not reached while the form is open

Forms!frmMultiInst!lblMsg.Caption = "File not found."

In contrast, if code is used to open a form instance via an object variable, the code continues execution. Therefore, the following four lines replace the previous two lines, and all of the lines are executed in immediate sequence:

Set mfrmMultiInst = New Form_frmMultiInst

mfrmMultiInst.Modal = True

mfrmMultiInst.Visible = True

mfrmMultiInst.lblMsg.Caption = "File not found."

With VBA, you can also create an array of classes, using syntax similar to Listing 12.16. A class array is dimensioned with multiple elements (rows), as with any other kind of array; but the data type of the array is set to a form class.

Listing 12.16 AES_Frm0.Mdb-Creating an Array of Class Objects

' In the form's Declarations section

Private mfrmMultiInst(3) As Form_frmMultiInst

' A procedure in the form

Private Sub cmdOpen_Click()

' Purpose: Open multiple form instances

Dim iintLoop As Integer

For iintLoop = 0 To 3

Set mfrmMultiInst(iintLoop) = New Form_frmMultiInst

mfrmMultiInst(iintLoop).Caption = "Instance " & CStr(iintLoop)

mfrmMultiInst(iintLoop).Visible = True

Next iintLoop

End Sub

Figure 12.5 shows the result of running this code. Be aware that an array of objects is resource intensive and should be used wisely.

Fig. 12.5

Four form class instances were created using a single form class array variable.

Using Collections to Work with Forms and Controls

VBA's new Collection object enables you to group several related objects together into a container where they can be subjected to bulk operations. Think of a Collection object as a one-dimensional array of pointers to objects. Unlike an array, which holds data, the only information the Collection object holds is references (pointers); thus, the elements referenced by a collection do not need to be of the same type. You can mix form, report, control, and data access object references together in a single collection. You can even make a Collection object a member of another Collection object.

Working with Collections

Before you add items to a Collection object, you must create it using the New keyword, as in the following example:

Private mcolData As New Collection


Most collections you create in a form module will be addressed by more than a single procedure in the form. Consequently, you will usually declare Collection objects in the form module's Declarations section as either Public or Private.

Adding items to a Collection object and working with them is easy and involves the following three methods:

When adding an item to a Collection object, use this syntax:

collection.Add Item[, Key][, Before][, After]


Arguments in the Add method are named arguments; thus, you can specify them in any order as long as they are qualified by name, as in this example:

colWidget.Add After:=3, Item:=rstWidget, Key:="Widget"

Unless otherwise specified, an item added to a collection is appended to the end and given the next available index value. Each Collection object has a Count property indicating the highest index number assigned to an object in the collection.


Unlike other Access and VBA collections and arrays, the lower bound of a Collection object is an index value of 1, not 0.


There are four Add method arguments, as follows:

mcolData.Add Forms!frmWidget

mcolData.Add Item:=Forms!frmWidget!txtWidgetName

mcolData.Add Form_frmWidget!subComponent.Form.txtSupplier

mcolData.Add Me.RecordSetClone

mcolData.Add Forms!frmWidget!txtWidgetName, "WidgetName" ' Add an item with a key

ctlWidget = mcolData(7) ' Retrieve the seventh item by index

ctlWidget = mcolData(mcolData.Count) ' Retrieve the last item by index

ctlWidget = mcolData("WidgetName") ' Retrieve the Widget item by key

The Item method of a Collection object retrieves an object reference to the designated member by index number or key string. Because the Item method is the default method for the collection, the Item keyword is optional; thus, these two syntaxes are equivalent:

ctlWidget = mcolData.Item("WidgetName")

ctlWidget = mcolData("WidgetName")

The Remove method removes an item reference from the Collection object. The original object is not affected in any way; only its relationship with the collection is destroyed. To remove an object, refer to it by index number or by key string:

mcolData.Remove(7)

mcolData.Remove("WidgetName")

To loop through a Collection object, you can use either of the following variations of a For statement. The second syntax will execute faster due to the presence of an object variable:

Dim iintLoop As Integer

For iintLoop = 1 To mcolData.Count

Debug.Print mcolData(iintLoop).Name

Next iintLoop

Dim eobj As Object

For Each eobj In mcolData

Debug.Print eobj.Name

Next eobj

Addressing Attributes of Collection Members

Collection objects are both strange and wonderful. The strange aspect derives from the mixed nature of objects in a collection. You cannot depend on the items in a collection to be homogeneous unless your code controls the object class of each item added to a collection. However, this feature also supplies collections with their "wonderfulness" because you can accumulate related but dissimilar object types together. In this section I will provide some examples of how to take advantage of the wonderfulness without bumping into the strangeness.

Because a collection member is an object, you can append property and method designators to a collection reference as if applying them to the member object. The keyword must be appropriate for the collection member, as in the following examples:

mcolData.Add Forms!frmWidget!txtWidgetName, "WidgetName" ' Add a text box item

MsgBox mcolData.Item("WidgetName").Name ' Retrieve the text box name

MsgBox mcolData("WidgetName").Value ' Retrieve the text box value

In a collection that contains a variety of objects, there is a risk of applying a method or property to a collection member that does not support the specified method or property. This situation results in an error. You can prevent the problem by using the TypeOf keyword to determine if the collection member is of the expected type, as in the following code:

If TypeOf mcolData(iintIndex) Is TextBox Then ' If a text box

ablnLocked(iintIndex) = mcolData(iintIndex).Locked ' Get the property

End If

Another feature of collection members is the capability to use their object references in With statements. This capability is especially useful on forms where you may want to work with several control property settings at a time. In the following example, the With statement is used to clarify and speed up code that changes the editable nature of controls in the collection based on a flag variable:

For iint = 1 To mcolCtlCust.Count

With mcolCtlCust(iint)

.Enabled = Not blnLock

.Locked = blnLock

.BackColor = IIf(blnLock, gclngColorGray, gclngColorWhite)

End With

Next iint

Using Collections of Controls

Creating Collection objects whose members are form controls enables your expert forms to loop through the collection and manipulate each control. You can set control properties, retrieve control properties, invoke control methods, and perform any other legitimate control-related action.

In previous versions of Access, you performed such tasks by looping through the built-in Controls collection for a form, using code like the following:

Dim iint As Integer

For iint = 0 To Me.Controls.Count [ms] 1

Debug.Print Me.Controls(iint).Name

Next iint

This code also works in Access 97 and, indeed, is still useful for reviewing every control on the form. To perform an operation on subsets of controls with such code, however, you must loop through the form's entire control collection and test each control for specific attributes. By contrast, creating a custom Collection object pointing directly to only controls with the desired attributes is more efficient and will execute faster.


Because a form builds its own collection object containing all of its controls, you can use the Me reference as the object of a With statement to loop through a form's control collection, as in this example:

With Me

!txtCustName = "Jones"

!txtCustPhone = "206-555-1212"

End With

You can load a control collection using hard code or soft code. Hard code points explicitly to controls by name, as in these lines:

Private Sub Form_Open(Cancel As Integer)

' Create a collection of customer order controls

mcolCtlCust.Add Me!txtCustName

mcolCtlCust.Add Me!txtCustNum

mcolCtlCust.Add Me!txtOrderDate

mcolCtlCust.Add Me!txtOrderNum

End Sub

Soft code identifies the controls to add to the collection by an attribute rather than by name. The examples in Listing 12.17 load the same collection in three different ways: by using each control's class (type), by using each control's LNC naming convention tag, and by using values placed in the Tag property of each control by the developer.

Listing 12.17 Three Different Ways to Load Controls into a Collection
Object by Attribute

' All three examples use this module-level collection

Public mcol As New Collection

' All three examples use these local variables

Dim ctl As Control

Dim iint As Integer

' 1. Use TypeOf to load all Text Box controls into a collection

For iint = 0 To Me.Controls.Count [ms] 1

Set ctl = Me.Controls(iint)

If TypeOf ctl Is TextBox Then

mcol.Add ctl

End If

Next iint

' 2. Use LNC tag to load all Text Box controls into a collection

For iint = 0 To Me.Controls.Count [ms] 1

Set ctl = Me.Controls(iint)

If Left(ctl.Name, 3) = "txt" Then

mcol.Add ctl

End If

Next iint

' 3. Use the Tag property to load all Text Box controls into a collection

' This also uses the new For Each version of a form control loop

For Each ctl in Me.Controls

If ctl.Tag = "Cust" Then

mcol.Add ctl

End If

Next

Code for working with control collections executes quickly and is quite efficient with system resources. The following are some examples of the use of control collections:

Chapter 14, "Navigating in Forms and Applications," provides examples of the use of control collections in form data validation.

A useful example of control collections derives from the need to group controls on your form. Assume that your form has related controls that should be set, cleared, or validated together. For example, your customer form has a set of address fields for the home address, and another set for the business address. You would like your form to have some mechanism for identifying each control in each group.

First, declare a collection array, mcolCustAddr(), whose number of elements matches the number of groups, in this case two. Next, fill each element of the collection array with the address information. Finally, loop through the collection array and perform the bulk operation required. Listing 12.18 shows sample code to clarify this strategy.

Listing 12.18 AES_Frm0.Mdb-Creating a Collection Array for Controls in
Two Different Control Groups

' In the Declarations section, create a collection array

Private mcolCustAddr(0 To 1) As New Collection ' One for each address group

Private Sub Form_Load()

' When loading the form, assign controls to their collection element

mcolCustAddr(0).Add Me!txtHomeAddr ' Becomes collection element (0)(1)

mcolCustAddr(0).Add Me!txtHomeCity ' Becomes collection element (0)(2)

...

mcolCustAddr(1).Add Me!txtBusnAddr ' Becomes collection element (1)(1)

mcolCustAddr(1).Add Me!txtBusnCity ' Becomes collection element (1)(2)

...

End Sub

Private Sub cmdCopy_Click()

' Copy home address to business

Dim ectl As Control

Dim iint As Integer

For iint = 1 To mcolCustAddr(0).Count ' Copy each collection element

mcolCustAddr(0)(iint).Value = mcolCustAddr(1)(iint).Value

Next iint

End Sub

Using Collections of Forms

A Collection object can include references to open forms. In the same fashion that you use control collections to group controls with related attributes, you can also group forms with related attributes.

First, create a public collection declared in a standard module as gcolFrm. Next, in each form in the application, place code in the Form_Open event that loads a reference to the form into the custom form collection:

gcolFrm.Add Me, Me.Name ' Use the name as the key

For as long as the form is open, there will be a reference to it in the custom collection. You now have an additional reference method with which to manipulate the properties and objects on a form: gcolFrm.Item("formname") or simply gcolFrm("formname").

A collection of all open forms has limited usefulness because Access has a built-in Forms collection that provides the same functionality. However, if you selectively add the Form_Open code line shown to only specific forms or, through some other mechanism, only add forms to the custom collection based upon some criteria, you will create custom form collections that hold an identified subset of the open forms. This is widely useful. For example, you can create a collection of forms that need to be requeried together based on a trigger provided by some application event; then quickly loop through the collection at any time and apply the Requery method to each member form.

As another example, you could create a collection of forms that nest within a parent form. Assume that your application allows for multiple forms to be opened simultaneously from a parent form and either minimized or hidden by the user. At any given time, several forms may be open that are dependent upon the presence of their parent form. In the parent form, create a collection to which you add members that point to child forms as they are being opened. When the user closes the parent form, you can loop through the collection and close each child form before closing the parent.


Once you have added a form reference to the custom collection, your code should explicitly remove the form from the collection when it closes. Otherwise, the collection maintains an erroneous pointer to the closed form. Referencing the collection element for the closed form will generate an error, as will attempting to add the form to the collection a second time (the next time the form opens). Use code like this in a form's Close event:


gcolFrm.Remove Me.Name

Recall that each key value in a single collection must be unique. In the current example, I am using Me.Name to pass the form name to the custom collection as the key. This strategy works fine when your code enforces the condition that each form will only be open once. However, if your code needs to open multiple instances of a single form, and each form instance must load itself into a collection, your code must also enforce a unique name for each form instance. You have the following two choices in such a scenario:

When using the second technique listed, it is convenient to store the key value of the form within the form itself. One way to do this is to create a module-level variable in each form to act as a custom property that stores the form's current key value from the global collection. This approach causes each form to travel with its own index into the collection. The code for this approach is shown in Listing 12.19.

Listing 12.19 AES_Frm0.Mdb-Setting and Clearing a Form's Membership
in a Custom Form Collection

' In the form's Declarations section

Public mstrColKey As String ' Index in global collection

' In the form's Open event

mstrColKey = Me.Name & CStr(Timer)

gcolFrm.Add Me, mstrColKey

' In the form's Close event

gcolFrm.Remove Me.mstrColKey

With this structure in place, any code in the application can retrieve a form's membership location in the custom collection by using the form's mstrColKey custom property value.

Using Nested Collections

It is possible to add a Collection object to another collection as a member. With this technique, you can build collection hierarchies, similar to the way object collections are nested in the DAO.

For example, you can combine the two techniques previously described for creating form collections and control collections, as shown in Figure 12.6. The figure diagrams how each form has a Collection object, mcolCtl, with all the form's controls as members. Additionally, gcolFrm, a public collection, is loaded with all of the form control Collection objects.

Fig. 12.6

Two control collections are nested inside a third collection.

To address items in a nested collection set, follow these syntax examples:

gcolFrm.Item(1).Item(1)

gcolFrm(1)(1)

gcolFrm.Item("frmCust").Item("txtCompName")

gcolFrm("frmCust")("txtCompName")

Some Collection Considerations

Here are a few concepts to keep in mind when using collections on your expert forms or elsewhere in your application:


Even though the examples in this chapter are form-centric, collection members can include reports, their controls, and related data objects.

Creating Faster Forms

From the beginning, form loading has been one of the most difficult jobs for Access and one of the most serious complaints from users. As Microsoft adds twenty or thirty percent more features to forms with each release, forms load more slowly with each version as a result, even as other areas of the product get faster. Such is the price of progress. Fortunately, this trend was reversed with Access 97, which loads forms faster than its immediate predecessor.

When developing expert forms, you must balance the goal of enhanced usability with the fact that slow form loads and events offset much of your usability gains. The first and foremost mission I give to my staff when working on a new project is to communicate to the users the important role that a good infrastructure plays in successful deployment of Access applications. Access works your machine very hard, and must be given adequate horsepower to do its considerably complex job.

Consider the race-car driver. Put an expert driver into a Ferrari, and you will see a fantastic combination of power and performance. Put the same driver into an old Volkswagen Beetle, and all of his skill and potential are wasted.

In the same vein, the single most important performance improvement for an Access application is not to tune the application, but rather to tune its host machine. If you recall my performance benchmarks shown in Chapter 1, "Access As a Development Tool," simply doubling the RAM in a 486 machine from 8M to 16M more than halved the load time of Access and its forms.

Of course, simply beefing up the user's hardware does not remove the application tuning burden from the developer. Let's explore our development and architectural options for improving performance.

Access 97 introduced lightweight forms, or forms that have no code attached to them. Such forms load faster than identical forms with a code module. In order to qualify as lightweight, a form must have no code module (its HasModule property is False). Such a form does not need to utilize any services from VBA in order to load or run the form and does not need to post its type library information into the application's object model. Thus a lightweight form does not appear in the Object Browser and cannot be referenced from an object variable. Use such forms for simple tasks and for subforms that do not have events.

Microsoft has published several help topics and articles over the years listing additional performance optimization suggestions for Access forms. I have pulled their best suggestions together into one list, and combined them with some hints of my own, in an attempt to produce a single comprehensive checklist of performance enhancement techniques for loading and using forms.

Data Techniques:


The Access group did some work to optimize the performance of RecordSource and RowSource properties in Access 97. When you save an SQL statement in one of these properties, Access creates a system-level saved query for the SQL statement, and uses the saved (thus compiled) query when loading the form.

These system objects are visible in the MSysObjects system table but not in the Database window, and are named using the syntax ~sq_fformname for a form RecordSource and ~sq_fformname~sq_ccontrolname for a control RowSource.

Coding Techniques:

If SysCmd(acSysCmdGetObjectState, acForm, formname) _

= acObjStateOpen Then

IsLoaded = True

End If

Layout Techniques:

From Here...

In this chapter, I felt it was more important to focus on the areas of Access that appeal most to a developer of expert forms, rather than provide a complete tour of all the new features of Access forms. To review a complete list of new form features, search Access Help for "What's New." For more information about some of the topics discussed in this chapter, refer to the following:

© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.