Access™ 2007 VBA Programmer's Reference

13.10. Creating and Using Collection Classes

So far, this chapter has dealt with situations where the relationship between objects is one-to-one. Although that's often the case in real-world programming, it is also quite often the case where the relationship is one-to-many. A group of related objects is called a collection, and a collection class is simply a class that contains a list, or collection of related classes. VBA provides a neat little object for creating and handling object collections, oddly enough called a Collection object.

13.10.1. The Collection Object

You are familiar with using object collections; VBA is full of them. The TableDefs and QueryDefs collections are examples you use almost every day. These collections maintain a list of pointers to the individual objects they control, in fact collection objects are also referred to as Controllers.

To access an individual object within an object collection, you refer to its collection name and either the name of one of the objects it contains, or its ordinal position within the collection. For example, to check the date a table was created, you could use either of the following constructs.

Debug.Print CurrentDb.TableDefs("Table1").DateCreated
Debug.Print CurrentDb.TableDefs(1).DateCreated

Most collection objects implement the Add, Remove, and Item methods, and the Count property. Unfortunately, the name given to the Add method can vary from application to application, and even from object to object, but they essentially ...