Chapter 10: VBA Programming Fundamentals
407
For example, when you build an Access form, you’re actually creating a Form object. As you add
controls to the form, you’re adding them to the form’s Controls collection. Even though you
might add different types of controls (such as buttons and text boxes) to the form, the form’s
Controls collection contains all the controls you’ve added to the form.
You’ll see many, many examples of working with individual objects and collections of objects in
this book. Understanding how objects differ from simple variables is an important step to becom-
ing a proficient Access developer.
Each type of Access object includes its own properties and methods, and shares many other prop-
erties (such as Name) and methods with many other Access objects.
Collections, however, have just a few properties and methods. These are the most important prop-
erties associated with Access collections:
l Name: The name of the collection. Most collection names are capitalized and are the plural
form of the type of object contained within the collection. For example, a form’s controls
are contained within the form’s Controls collection.l (^) Count: The number of items contained with the collection. A collection with a Count
of 0 is empty. Collections can contain virtually any number of items, but performance
degrades when the Count becomes very large (in excess of 50,000 objects).
l Item: Once you have objects stored in a collection, you need a way to reference individual
objects in the collection. The Item property points to a single item within a collection.
The following example demonstrates setting a property on just one item in a collection:
MyCollection.Item(9).SomeProperty = Value
or:
MyCollection.Item(“ItemName”).SomeProperty = Value
where MyCollection is the name assigned to the collection, SomeProperty is the name of a property
associated with the item, and Value is the value assigned to the property.
This small example demonstrates a couple of important concepts regarding collections:
l (^) There are different ways to reference the items stored in a collection. In most cases,
each item stored in a collection (such as a form’s Controls collection) has a name and
can be referenced using its name:
MyForm.Controls(“txtLastName”).FontBold = True
As a consequence, each object’s name within a collection must be unique. You can’t, for
example, have two controls with the same name on an Access form.
The alternate way to reference an object in a collection is with a number that indicates the
item’s ordinal position within the collection. The first item added to a collection is item 0
(zero), the second is item 1, and so on.