Introduction to Collections

Fundamentals of Collections

Introduction

A collection is a group of values or objects that are treated as a unit. The objects must be either of the same type or compatible. The values or objects should be easily added to the collection, located inside the collection, or removed from the collection.

To support collections, the Visual Basic language provides a class named Collection.

Starting a Collection

To start a collection, declare a variable of type Collection. Of course, you must initialize it using the New operator. Here is an example that starts a collection:

Private Sub cmdCollection_Click()
    Dim months As Collection

    Set months = New Collection
    
    Set months = Nothing
End Sub

Adding a Value to a Collection

The most fundamental operation performed on a collection is to add a value to it. To support this, the Collection class is equipped with a method named Add. Its syntax is:

Public Sub Add(ByVal Item As Object, _
	       Optional ByVal Key As String, _
	       Optional ByVal { Before | After } As Object = Nothing)

This method takes three argument but only the first is required, which is the value to add. You can call this method every time you need to add a value.

Practical Learning: Introducing Collection

1. Start Microsoft Access
2. Click Blank Desktop Database
3. Set the file name to Exercise3 and click Create
4. On the Ribbon, click Create and click Form Design
5. In Controls section of the Ribbon, click the Button and click the form.
   If a wizard starts, click Cancel
6. On the form, right-click the button and click Build Event
7. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:

   Private Sub Command0_Click()
       Dim months As Collection
   
       Set months = New Collection
       
       months.Add "January"
       months.Add "February"
       months.Add "March"
       months.Add "April"
       months.Add "May"
       months.Add "June"
       
       Set months = Nothing
   End Sub

For Each Item of a Collection

To let you access each item of a collection, the Visual Basic language provides the For Each...Next statement. The formula to use it is:

For Each element In collection
    statements
    options
Next

In the VBA, you must first declare a variable that will represent each item of the collection. This variable will be used as the element. This is followed by the In keyword and the collection you will use. The statement ends with the Next keyword. In the statements section, specify the element.

Practical Learning: Visiting Each Item of a Collection

1. Change the code as follows:

   Private Sub Command0_Click()
       Dim month
       Dim months As Collection
   
       Set months = New Collection
       
       months.Add "January"
       months.Add "February"
       months.Add "March"
       months.Add "April"
       months.Add "May"
       months.Add "June"
       
       For Each month In months
           MsgBox month
       Next
       
       Set months = Nothing
   End Sub

2. Return to Microsoft Access and switch the form to Form View
3. Click the button
4. Click OK on each message box
5. Return to Microsoft Visual Basic

The Number of Items in a Collection

To let you get the number of items in a collection, the Collection class is equipped with a property named Count:

Public Property Get Count() As Integer

Practical Learning: Getting the Number of Items of a Collection

1. Change the code as follows:

   Private Sub Command0_Click()
       Dim month
       Dim months As Collection
   
       Set months = New Collection
       
       months.Add "January"
       months.Add "February"
       months.Add "March"
       months.Add "April"
       months.Add "May"
       months.Add "June"
       
       MsgBox "This collection contains " & months.Count & " items"
       
       Set months = Nothing
   End Sub

2. Return to Microsoft Access and switch the form to Form View
3. Click the button

   

4. Click OK on each message box
5. Return to Microsoft Visual Basic

The Index/Key of an Item in the Collection

Introduction

Every time you add a new item to the collection as we have done so far, the new item occupies a specific position. The position of an item within a collection is also referred to as the index of the item. You can locate an item based on its index. The first added item receives an index of 1, the second receives an index of 2, and so on.

To let you access an item, the Collection class is equipped with a property named Item:

Public Property Let/Get Item( _
    ByVal { Key As String | Index As Integer | Index As Object } _
) As Object

Based on this, to access an item, type the name of the collection followed by .Item(). In the parentheses, type the index of the item you want to access.

Practical Learning: Getting an Item by its Index

1. Change the code as follows:

   Private Sub Command0_Click()
       Dim months As Collection
   
       Set months = New Collection
       
       months.Add "January"
       months.Add "February"
       months.Add "March"
       months.Add "April"
       months.Add "May"
       months.Add "June"
   
       MsgBox "Month: " & months.item(1)
       MsgBox "Month: " & months.item(2)
       MsgBox "Month: " & months.item(3)
     
       Set months = Nothing
   End Sub

2. Return to Microsoft Access and click the button
3. Click OK on each message box
4. Return to Microsoft Visual Basic
5. An alternative is to omit .Item. To see some examples, change the code as follows:

   Private Sub Command0_Click()
       Dim months As Collection
   
       Set months = New Collection
       
       months.Add "January":   months.Add "July"
       months.Add "February":  months.Add "August"
       months.Add "March":     months.Add "September"
       months.Add "April":     months.Add "October"
       months.Add "May":       months.Add "November"
       months.Add "June":      months.Add "December"
   
       MsgBox "Month: " & months(12)
       MsgBox "Month: " & months(7)
       MsgBox "Month: " & months(10)
       MsgBox "Month: " & months(5)
     
       Set months = Nothing
   End Sub

6. Return to Microsoft Access and click the button
7. Click OK on each message box and return to Microsoft Visual Basic

The Key to an Item

By default, when you add a new item to a collection as we have done so far by passing only the required argument to the Collection.Add() method, the item receives a default index. When you add a new item to a collection, instead of letting the database engine specify a default index, you can provide an index of your choice. That index is referred to as a key. To specify the key of a new item, pass the second argument to the Collection.Add() method and give the desired value. The key should be a string and it must be unique among the other keys of the collection.

Practical Learning: Setting the Key of an Item

1. Change the code as follows:

   Private Sub Command0_Click()
       Dim months As Collection
   
       Set months = New Collection
       
       months.Add "September", "9"
       months.Add "March", "3"
       months.Add "January", "1"
       months.Add "July", "7"
       months.Add "April", "4"
       months.Add "December", "12"
       months.Add "February", "2"
       months.Add "August", "8"
       months.Add "October", "10"
       months.Add "May", "5"
       months.Add "November", "11"
       months.Add "June", "6"
   
       MsgBox "Month: " & months(12)
       MsgBox "Month: " & months(7)
       MsgBox "Month: " & months(10)
       MsgBox "Month: " & months(5)
     
       Set months = Nothing
   End Sub

2. Return to Microsoft Access and click the button
3. Click OK on each message box
4. Return to Microsoft Visual Basic
5. You can use the key of an item to locate its value. To do this, type the name of the collection followed by parentheses. In the parentheses, type the key of the item you want to access. to see some examples, change the code as follows:

   Private Sub Command0_Click()
       Dim NewEngland As New Collection
       
       NewEngland.Add "Connecticut", "CT"
       NewEngland.Add "Maine", "ME"
       NewEngland.Add "Massachusetts", "MA"
       NewEngland.Add "New Hampshire", "NH"
       NewEngland.Add "Rhode Island", "RI"
       NewEngland.Add "Vermont", "VT"
   
       MsgBox NewEngland("NH")
       MsgBox NewEngland("CT")
       MsgBox NewEngland("RI")
     
       Set NewEngland = Nothing
   End Sub

6. Return to Microsoft Access and click the button
7. Click OK on each message box
8. Close the form
9. When asked whether you want to save, click No

Removing an Item

To let you delete an item, the Collection class is equipped with a method named Remove. Its syntax is:

Public Sub Remove(ByVal Index As Integer)

To remove an item, pass its index as argument to this the method.

Collections and Classes

Creating a Collection of Objects

You can create a collection of objects, in which case the members are based on a class. You start the variable normally. One way to add an object is to first define it through a variable/object, then pass that object as the argument of the Collection.Add().

To access the items of a collection, you can use a For Each operation. Its element produces an item that you can use as you want.

Practical Learning: Introducing Collections of Objects

1. On the Ribbon, click File and click Open
2. In the list of files, click Road System1 from Lesson 6
3. On the Ribbon, click Create and click Form Design
4. In Controls section of the Ribbon, click the Button and click the form.
   If a wizard starts, click Cancel
5. On the form, right-click the button and click Build Event
6. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:

   Private Sub Command0_Click()
       Dim rd As Road
       Dim strSummary As String
       Dim RoadSystem As New Collection
       
       strSummary = "Road System Database" & vbCrLf & _
                    "=============================================="
       Set rd = New Road
       rd.RoadName = "I-76"
       rd.Distance = 434.87
       rd.Description = "From an interchange with I-71 west of Akron, Ohio, east to I-295 in Bellmawr, New Jersey."
       RoadSystem.Add rd
       
       Set rd = New Road
       rd.RoadName = "US 322"
       rd.Distance = 494
       rd.Description = "From Cleveland, Ohio east to Atlantic City, New Jersey"
       RoadSystem.Add rd
       
       Set rd = New Road
       rd.RoadName = "I-29"
       rd.Distance = 750.58
       rd.Description = "From Kansas City, MO to Manitoba Highway 75 (in Canada)."
       RoadSystem.Add rd
       
       Set rd = New Road
       rd.RoadName = "MD 410"
       rd.Distance = 13.92
       rd.Description = "From East Bethesda to Pennsy Drive in Landover Hills"
       RoadSystem.Add rd
       
       For Each rd In RoadSystem
           strSummary = strSummary & vbCrLf & _
                        "Road Name: " & rd.RoadName & vbCrLf & _
                        "Distance: " & CStr(rd.Distance) & " miles" & vbCrLf & _
                        "Description: " & rd.Description & vbCrLf & _
                        "---------------------------------------------------------------------------------------"
       Next
       
       MsgBox strSummary
       
       Set rd = Nothing
       Set RoadSystem = Nothing
   End Sub

7. Return to Microsoft Access and switch the form to Form View
8. Click the button
9. Click OK on the message box
10. Return to Microsoft Visual Basic

Operations on a Collection of Objects

The characteristics and actions we reviewed for a collection of regular values also apply to a list of objects. For example, to know the number of objects in a collection, you can just get the value of its Count.

As mentioned previously, if you add objects to a collection by passing only one argument to the Collection.Add() method, each new object receives an incrementing 1-based index. You can use such an index to locate an object.

Otherwise, you can specify your own key for each object being added to a collection. Here are examples:

Class Module: USState

Public State As String
Public AreaSqrKm As Integer
Public AreaSqrMi As Integer
Public AdmissionToUnionYear As Integer
Public AdmissionToUnionOrder As Integer
Public Capital As String

Form Module: Exercise

Private Sub cmdCommand0_Click()
    Dim uss As USState
    Dim Code As String
    Dim states As New Collection
    Dim stateSelected As New USState

    uss = New USState
    uss.State = "Alabama"
    uss.AreaSqrKm = 135775
    uss.AreaSqrMi = 52423
    uss.AdmissionToUnionYear = 1819
    uss.AdmissionToUnionOrder = 22
    uss.Capital = "Montgomery"
    states.Add(uss, "AL")

    uss = New USState
    uss.State = "Alaska"
    uss.AreaSqrKm = 1700139
    uss.AreaSqrMi = 656424
    uss.AdmissionToUnionYear = 1959
    uss.AdmissionToUnionOrder = 49
    uss.Capital = "Juneau"
    states.Add(uss, "AK")

    uss = New USState
    uss.State = "Arizona"
    uss.AreaSqrKm = 295276
    uss.AreaSqrMi = 114006
    uss.AdmissionToUnionYear = 1912
    uss.AdmissionToUnionOrder = 48
    uss.Capital = "Phoenix"
    states.Add(uss, "AZ")

    uss = New USState
    uss.State = "Arkansas"
    uss.AreaSqrKm = 137742
    uss.AreaSqrMi = 53182
    uss.AdmissionToUnionYear = 1836
    uss.AdmissionToUnionOrder = 25
    uss.Capital = "Little Rock"
    states.Add(uss, "AR")

    uss = New USState
    uss.State = "California"
    uss.AreaSqrKm = 424002
    uss.AreaSqrMi = 163707
    uss.AdmissionToUnionYear = 1850
    uss.AdmissionToUnionOrder = 31
    uss.Capital = "Sacramento"
    states.Add(uss, "CA")

    uss = New USState
    uss.State = "Colorado"
    uss.AreaSqrKm = 269620
    uss.AreaSqrMi = 104100
    uss.AdmissionToUnionYear = 1876
    uss.AdmissionToUnionOrder = 38
    uss.Capital = "Denver"
    states.Add(uss, "CO")
End Sub

You can use such a key to locate an object. From there, you can access the members of the class.

A Property as a Collection

A property of a class can be based on a collection.

When accessing such a property, you actually get a collection, not a value. A property that holds a collection is called an indexed property. As mentioned already, when you access an indexed property, you get a collection of items. To access an item inside the collection, you can use the parentheses of the Item property. This would give you access to one of the items in the actual collection.

Introduction to Built-In Collections

The Object Browser

Microsoft Access heavily relies on built-in objects and already created collections. Besides the collections and objects, there are also many enumerations.

There are so many objects, collections, classes, and enumerations that it is unrealistic to memorize all of them. While you are working in Microsoft Visual Basic, you can take a look at a list of available objects and enumerations. To get this list, you can use the Object Browser.

To display the Object Browser:

• On the main menu, click View and click Object Browser
• On the Standard toolbar, click the Object Browser button

Any of these actions would display the Object Browser:

Microsoft Access ships with many libraries. Each library has its own set of objects, collections, classes, and enumerations. When using the Object Browser, to see the objects of a certain library, in the Project/Library combo box, select the desired library:

After selecting a library, its objects, collections, and enumerations appear in two columns in the body of the window:

To explore (to see the members of) an object or a collection, click it in the left column. The properties and methods of the selected item would appear in the right column:

To get some information about an item, whether from the left or the right column, click it. The bottom frame of the window gives some details about the selected item. For example, to know the syntax of a method, after locating and clicking its class in the left column, click that method in the right column:

If an item appears as a link (bold and green) in the bottom frame, you can click it. This would select it in the left column.

The Objects in the Current Database

While using an application, the objects that belong to the database are stored in a collection called CurrentData. The CurrentData object itself is a property of the Application object. To access the CurrentData object, you can first call the Application object, type the period operator, and type CurrentData. This would be done as follows:

Application.CurrentData

The CurrentData object holds the collections of objects that belong to the database.

Practical Learning: Ending the Lesson

• Close Microsoft Access