Microsoft Access Topics: Overview of the Collection Class

Microsoft Access Topics:

Overview of the Collection Class

Introduction

A collection is a series of objects. In most cases, all of the objects must be of the same type. Sometimes, this rule can be avoided; but it is better to include, in a collection, the objects or values that can be described using the same characteristics.

Creating a Collection

To assist with the creation of a collection, the Visual Basic language has a class named Collection. Based on this, to create a collection, declare a variable of type Collection:

Private Sub Detail_Click()
    Dim colPersons As Collection
End Sub

After declaring the variable, you must initialize it and allocate memory for it. To initialize it, use the Set operator to identify the variable. To allocation memory for the variable, use the New operator followed by the name of the class: Collection. Here is an example:

Private Sub Detail_Click()
    Dim People As Collection
    Set People = New Collection
End Sub

Adding Items to a Collection

After declaring the variable, it is empty. To make it useful, you must add items to it. To support this operation, the Collection class is equipped with a method named Add. Its syntax is:

Collection.Add(Item, [Key], [Before], [After])

The only required thing you must provide is the item you want to add. If you are only interested in adding the new item and do not need to get any value back, you can omit the parentheses. Here is an example:

Private Sub Detail_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint"
End Sub

In the same way, you can add as many items as you want. Here are examples:

Private Sub Detail_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint"
    People.Add "Laurent Tigers"
    People.Add "Gertrude Simms"
    People.Add "James Cranston"
    People.Add "Paula Aurora"
    
End Sub

Accessing an Object of a Collection

To access an item using its numeric index, type the name of the collection, followed by the period operator, followed by Item with an opening and a closing parentheses. In the parentheses of Item, enter the index of the item that you want to access. Here is an example:

Private Sub cmdSelectPerson_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint"
    People.Add "Laurent Tigers"
    People.Add "Gertrude Simms"
    People.Add "James Cranston"
    People.Add "Paula Aurora"
    
    txtPerson = People.Item(1)
End Sub

Here is an example that accesses the third item in the collection:

People.Item(3)

Based on the design of the Visual Basic language, Item is the default property of a collection. Based on this, you do not have to use the Item name to access the item. You can directly apply the parentheses on the name of the collection. Here is an example:

Private Sub cmdSelectPerson_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint"
    People.Add "Laurent Tigers"
    People.Add "Gertrude Simms"
    People.Add "James Cranston"
    People.Add "Paula Aurora"
    
    txtPerson = People.Item(3)
End Sub

You may already think of problems that would occur when trying to access an item by its index. For example, you must know with certainty what item is stored at a particular position. When we reviewed the ability to add items to a collection, we saw that the syntax of the Add() method was:

Collection.Add(Item, [Key], [Before], [After])

The second argument allows you to create a type of tag and apply it to each item. This tag is like an identification. It can be used to identify an item using something else than its index. This tag is called a key.

To create a key when adding an item, pass a second string to the method. The string can be anything you want but you should not make it too complicated. Here are examples where we used to initial of each person's name to create its corresponding key:

Private Sub cmdSelectPerson_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint", "HP"
    People.Add "Laurent Tigers", "LT"
    People.Add "Gertrude Simms", "GS"
    People.Add "James Cranston", "JC"
    People.Add "Paula Aurora", "PA"
End Sub

Based on this, if you know the key of an item, you can use it instead of the index to get the item. To use the key, apply an opening and a closing parentheses to the name of the collection. In the parentheses, use the key, as a string. Here is an example:

Private Sub cmdSelectPerson_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint", "HP"
    People.Add "Laurent Tigers", "LT"
    People.Add "Gertrude Simms", "GS"
    People.Add "James Cranston", "JC"
    People.Add "Paula Aurora", "PA"
    
    txtPerson = People("JC")
End Sub

When you use a key, even if the collection changes, for example, if the items are moved by their positions, when you refer to one by its name, Microsoft Access would look for the item that has that key, regardless of its position.

The Count of Items

One of the pieces of information you can get from a collection is the number of its members. To give you access to the number of items of a collection, the Collection class is equipped with a property named Count. To get the number of items of a collection, type the name of the collection, followed by the period operator, followed by the name of the count property. Here is an example:

People.Count

In most, if not all cases, the Count property is read-only. This means that you cannot change it and therefore you cannot assign a value to it. You can only retrieve the value stored in the Count property.

Removing an Item From a Collection

If you have an item in a collection but do not need that item anymore, you can delete it. To support the ability to remove an item from a collection, the Collection class is equipped with a method named Remove. Its syntax is:

Remove(Index)

When calling this method, pass the index of the item you want to delete. If you pass an index that does not exist, you would receive an error.