Looping through Collections

As I 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 currently 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'll 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(0) (pronounced all forms sub zero). The second item in the AllForms collection is AllForms(1). The next is AllForms(2), and so on, as illustrated in Figure 8-1.

Figure 8-1:

Current-Project. AllForms collection and members.

Figure 8-1:

Current-Project. AllForms collection and members.

CurrentProject.AllForms collection rms(0) rms(1) rms(2) rms(3) rms(4) rms(5) rms(6) rms(7) rms(8)

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!("formName").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 0-8.

MyForm

MyForm.Controls(O)

MyForm.Controls(l) Figure 8-2: MyForm.Controls(3) A form as a MyForm.Controls(5)

collection of MyForm.Controls(7) controls.

MyForm

MyForm.Controls(O)

MyForm.Controls(l) Figure 8-2: MyForm.Controls(3) A form as a MyForm.Controls(5)

collection of MyForm.Controls(7) controls.

MyForm.Controls(6)

MyForm.Controls(8)

MyForm.Controls(6)

MyForm.Controls(8)

MyForm.Controls(4)

MyForm.Controls(2)

0 0

Post a comment