Working with Collections

Many objects are grouped into collections. A collection is a special object that contains other objects. For example, Access maintains its own collection of open forms, and you can use this collection in VBA. Because Access maintains this collection object, you don't need to instantiate it yourself. In this section of the chapter, you learn the basic syntax to work with collections. Collections are most useful for listing and finding instances of the objects that they contain.

Here's the first way to list the objects in a collection:

 Sub ListCollection1()   ' List the items in a collection   ' First, open three forms   Dim frmClients As New Form_Clients   Dim frmProjects As New Form_Projects   Dim frmSwitchboard As New Form_Switchboard   frmClients.Visible = True   frmProjects.Visible = True   frmSwitchboard.Visible = True   ' Now go through the built-in Forms   ' collection to list them   Dim i As Integer   For i = 0 To Forms.Count - 1     Debug.Print Forms.Item(i).Name   Next i End Sub 

This code starts its work by opening three forms, using the syntax you've already seen to instantiate the form objects. It then declares a variable i to act as a loop counter. The loop sets i equal to each value, from 0 to one less than Forms.Count. Forms.Count is a property that returns the number of objects in the built-in Forms collection (that is, the number of open forms). The numbering here is zero-based: that is, if the collection contains three objects, they are numbered 0, 1, and 2.

To review the details of looping, see "Using For…Next," p. 95.

Each iteration of the loop retrieves the name of one of the objects in the collection. Item is a property of the Forms collection that returns a particular member of the collection. For example, Forms.Item(1) returns the second member of the collection. Because the collection contains forms, this member is a form object, and the code can retrieve and print the Name property of the form. When you run the procedure, you see this result in the Immediate window:

 Clients Projects Switchboard 

There are two ways to simplify this code. First, the Item property is the default property of the Forms collection. This means that you don't actually have to include the property name in the code. So this procedure still works to list the open forms:

 Sub ListCollection2()   ' List the items in a collection   ' First, open three forms   Dim frmClients As New Form_Clients   Dim frmProjects As New Form_Projects   Dim frmSwitchboard As New Form_Switchboard   frmClients.Visible = True   frmProjects.Visible = True   frmSwitchboard.Visible = True   ' Now go through the built-in Forms   ' collection to list them   Dim i As Integer   For i = 0 To Forms.Count - 1     Debug.Print Forms(i).Name   Next i End Sub 

Finally, because looping through all members of a collection is such a common operation, there's a special looping statement just for this purpose. The For…Each loop goes through a collection, setting an object variable to each member of the collection in turn:

 Sub ListCollection3()   ' List the items in a collection   ' First, open three forms   Dim frmClients As New Form_Clients   Dim frmProjects As New Form_Projects   Dim frmSwitchboard As New Form_Switchboard   frmClients.Visible = True   frmProjects.Visible = True   frmSwitchboard.Visible = True   ' Now go through the built-in Forms   ' collection to list them   Dim frm As Form   For Each frm In Forms     Debug.Print frm.Name   Next frm End Sub 

When you use the For…Each syntax, your loop counter variable must be an object variable of the appropriate type. As VBA goes through the loop, it instantiates this object variable to refer to each member of the collection in turn. With this syntax, you don't have to worry about retrieving the collection's Count and setting an integer appropriately; VBA takes care of the details.

Every collection has a Count property and an Item method. You might also find methods such as Add and Remove on many collections. These methods adjust the membership of the collection. Because the Forms collection is maintained by Access, it does not have Add or Remove methods.