Looping through Collections

As we mention in Chapter 5, Access contains objects and collections whose properties and methods can be controlled through VBA. Each collection has a specific name. For example, the CurrentProject.AllForms collection contains the names of every form in the current database.

Every collection has a .Count property that describes how many objects are in the collection. For example, CurrentProject.AllForms.Count represents the number of forms in the current database. For example, if you type ? CurrentProject.AllForms.Count into the VBA Editor's Immediate window and press Enter, you see the number of forms contained within the database.

Objects within a collection are always enumerated (numbered), starting with 0 (zero). For example, the first item in the AllForms collection is AllForms(O) (pronounced "all forms sub zero"); the second item in the AllForms collection is AllForms(l); the third is AllForms(2); and so on. In the collection shown in Figure 8-1, Address Book Form is AllForms(O); Email Messages Form is AllForms(l); EmailWarningDialog is AllForms(2); and so on.

Figure 8-1:

Current Project. AllForms collection and members.

All Access Objects ' «

Tables

¥

Queries

Forms

Ä

3 Address Book Form

¡IS Email Messages Form

¡H] EmailWarningDialog

rH Main Switchboard

My Business Main Form

r§1 My Business Subform

rOrder Details Subform

ril Orders Main Form

¡H] Print from Order Form

¡H] Products Form

Report Switchboard

,-il Sales Tax Calcs

Sales Tax Form

Sales Tax Subform

PH SkipLabels Form

HI VesNo Invoices Form

rH YesNo Labels Form

Reports

¥

Macros

¥

Modules

«

— CurrentProject.AllForms collection

— CurrentProject.AllForms collection

As discussed in Chapter 5, an object can also be a collection. That is, it can be both an object and a collection at the same time. For example, a form is an object, but a form is also a collection of controls. From VBA (or an Access expression), you refer to an open form's Controls collection by using the syntax

Forms!("formWame").Controls where formName is the name of an open form. As with any collection, the controls in a form are enumerated (numbered starting with zero). For example, the first control on a form is formName .Controls(O), the next is formName.Controls(l), and so forth. Figure 8-2 shows an example using a form named MyForm that contains nine controls, numbered this way:

BizName text box

MyForm.

.Controls

(0)

Biz Name label

MyForm.

.Controls

(1)

BizPhone text box

MyForm.

.Controls

(2)

Phone label

MyForm.

.Controls

(3)

BizURL text box

MyForm.

.Controls

(4)

Web Site label

MyForm.

.Controls

(5)

BizEmail text box

MyForm.

.Controls

(6)

Email label

MyForm.

.Controls

(7)

Close button

MyForm.

.Controls

A form as a collection of controls.

Figure 8-2:

A form as a collection of controls.

Was this article helpful?

0 0

Post a comment