Determining the Boundaries of Collections

To work effectively with collections, it’s important to understand how collections are ordered and how to determine their boundaries. To determine the boundaries of a collection, use the Count property of the collection. The value of the Count property reflects the number of objects in the collection. For example, if a collection’s Count property returns 5, then there are five objects in that collection.

However, remember that DAO collections are zero-based. This means that members of DAO collections are indexed starting with 0, with the first object in the collection having an index number of 0. Therefore, when your code compares the value of the Count property to the current index position of the collection, it must subtract one. For example, the following code illustrates how to use the Count property to determine the number of TableDef objects in a database and to print the name of each table. In this code, strDbPath is the path to the database:

Dim dbs As Database
Dim intX As Integer

Set dbs = OpenDatabase(strDbPath)
For intX = 0 To dbs.TableDefs.Count - 1
	Debug.Print dbs.TableDefs(intX).Name
Next intX

You can see how this example subtracts one from the Count property to make sure the comparison works.

You can simplify your code by using the For Each…Next statement instead of a For...Next statement. The following code accomplishes the same task, but without requiring you to handle collection boundaries. In this example, strDbPath is the path to the database:

Dim dbs As Database
Dim tdf As TableDef

Set dbs = OpenDatabase(strDbPath)
For Each tdf In dbs.TableDefs
	Debug.Print tdf.Name
Next tdf

The first example above shows how you can refer to an object by using its location within a collection. When you add or delete objects from a collection, the Count property changes and no longer provides a reliable means of specifying an object’s location in a collection. Therefore, you should not use the Count property to determine the boundaries of a collection with code that loops through and deletes objects.