Ms-Access and Collection Object Basics

Introduction.

VBA Array is more popular and commonly used, for storing several sets of related values (rows and columns) than Collection Object.  We have used Arrays to store User-Defined Types and Class Module objects.  I think it is time to learn something new and different, the usage of Collection and Dictionary Objects. The Collection Object is very convenient to store related items as a group.  Why Dictionary Object, we will take it up at an appropriate time?

To use Arrays, we need to Dimension a Variable, User-Defined Type, or Class Module Object for the required number of elements in advance, or Re-dimension to increase or decrease the size of an Array before we are able to store value(s) into them. But this procedure is not required for Collection Object.  After instantiating the Collection Object, we can add any number of items to it. The Collection members can be of any data type, built-in Objects, Class-Module Object, or another Collection Object, with its own item members.

Collection Demo Program.

Let us write a simple program to demonstrate the usage of Collection Object.

Public Sub CollTest1()
Dim C As Collection
Dim j As Integer

‘instantiate the Object
Set C = New Collection
                            
C.Add 5
C.Add 15
C.Add "iPhone"
C.Add "Disk 2TB"
C.Add 35.75

'Print the items in debug window
GoSub Listing

C.Remove 3 'Remove 3rd item

GoSub Listing

Set C = Nothing

Exit Sub

Listing:
Debug.Print
For j = 1 To C.Count
    Debug.Print C.Item(j)
Next
Return

End Sub

Code Review Line By Line.

The first two lines declare the Variable C as a Collection Object. The next line declares the Variable j as an Integer type, as a control variable for the For. . . Next Loop.  The third line instantiates the Collection Object C in memory.

The Collection Object has four built-in methods: Add, Count, Item, and Remove, for managing the Collection items in memory.

An image of the Collection Object instance below, displaying its methods list.

• The Add method adds value or object as a Collection member to the Collection Object and the Remove method is for deleting an item from the Collection.

• With the Item method combined with an item number as Index, we can retrieve a particular item from the Collection. The Count method gives the total items count in the Collection.

• In the above example, we are not using any Object type items, but a few simple mixed data Types  Integer, String, and Double Precision Number, as Collection members.

• By using the Add method of C Collection Object we have added five items to the collection.  The first two items are integer numbers, the next two items are String data types and the last one is a  double-precision number. 

• The Collection Object instance name C and the Add method are joined with a dot separator, followed by space than the actual value to be added to the Collection.

• The Add method has four parameters, check the image given below:

  

• The parameters: Item, [Key], [Before], [After].  The first parameter Item is mandatory; the Value to be added to the Collection.

• The next three parameters are optional. 

• When any of the optional parameters are used with the Add method other parameter places must be skipped with a comma separator, except for the right side items.  Explicit usage of parameter names with their values enables us to give the parameter values in any order.  We will learn their usage in another example VBA Code. 

  Note: Do not get confused with the Item Parameter of Add Method with the Item() Method of Collection Object.

We have added five items as members of the Collection Object with the Add method.  Two Integer Type values, two String data Type Values, and one double-precision number. 

It simply demonstrates that you can add any data type, except User-Defined Type (UDTs) into the Collection.  When you want to add UDTs into a Collection, convert your UDTs into a Class Module Object.

Next, we are calling a printing sub-routine, within the program, that prints the Collection members to the debug window.  The sub-routine has a For . . . Next Loop to run from 1 to the number of items (C.Count) in the Collection.  The value in the j control variable is used as the index number parameter to the Item() method of the Collection Object, to retrieve the value and print it in the debug window.

The next Line removes the third item (iPhone) from the item members, by calling the Remove method.

The printing sub-routine is called one more time to display the changed list of items, after the removal of the third item in the old list.

The Exit Sub statement prevents the control of the program from dropping into the internal subroutine lines and stops the program.  The listing will appear in the Debug Window as shown below.

The Output in Debug Window.

5 
15 
iPhone
Disk 2TB
35.75

5 
15 
Disk 2TB
35.75

We can insert a value Before a particular item member by specifying the item number with the Before  Parameter Name.

C.Add 2, Before:=1 ‘add value 2 Before existing first item 
OR
C.Add 2,,1

The above statement will add value 2 as the first item in the above program, pushing all existing items down.

C.Add 20, After:=3 ‘Add value 20 After existing item number 3
OR
C.Add 20,,,3

This statement inserts the value 20 After the third item, after value 15,  in the list.

The Code below demonstrates the Before:= and After:= Parameter usages.

Public Sub CollTest2()
Dim C As Collection
Dim j As Integer

Set C = New Collection

C.Add 5
C.Add 15
C.Add "iPhone"
C.Add "Disk 2TB"
C.Add 35.75

GoSub Listing

C.Add 2, Before:=1 'Insert the item before the first item
C.Add 20, After:=3 'Insert the item after first 3 items

GoSub Listing

Set C = Nothing
Exit Sub

Listing:
'Print the items
Debug.Print
For j = 1 To C.Count
   Debug.Print C(j)

Next
Return

End Sub

Note: The advantage of using parameter names is that you can pass the values in any order you want when you need to use more than one parameter in a statement.

C.Add After:=3,Item:=20

The second example is without the use of Parameter Names and giving the parameter value in its proper position.

Public Sub CollTest2_2()
Dim C As Collection
Dim k As Integer
Set C = New Collection

C.Add 5
C.Add 15
C.Add "iPhone"
C.Add "Disk 2TB"
C.Add 35.75

GoSub Listing

C.Add 2, , 1 'Insert the item before the first item
C.Add 20, , , 3 'Insert the item after first 3 items

GoSub Listing

Set C = Nothing

Exit Sub

Listing:
'Print the items
Debug.Print
     For k = 1 To C.Count
         Debug.Print C(k)
     Next: Debug.Print
Return
End Sub

Sample printout on the Debug Window is shown below:

5 
 15 
iPhone
Disk 2TB
 35.75 

 2 
 5 
 15 
 20 
iPhone
Disk 2TB
 35.75 

In all our printing examples we have used the Item's Index number to retrieve the values to print them in the Debug Window.  When there are several items in the collection it is very difficult to keep track of a particular Item's Index number to retrieve the specific value we want.  To overcome this problem we can store an easily memorable Key-Value, along with the Item Value, like the First Name of a Contact in the Address Book, to retrieve the item value randomly from the Address Book Collection members.

Usage of Item Keys

Let us write a new program to demonstrate the usage of Item Keys with Values in the Collection.

Public Sub CollTest3()
Dim C As Collection
Dim strKey As String
Dim strGet As String

Set C = New Collection

C.Add 5, Key:="FIVE" 
C.Add 15, Key:="FIFTEEN"
C.Add "iPhone", "7+"     'you can omit the KEY param name
C.Add "Disk 2TB", "DISK" ' Add method's 2nd Parameter is KEY
C.Add 35.75, "999"

'add value 2 with Key "TWO" before the first item.
'this item will be the first item in the collection
'parameter names not in proper order – valid

C.Add Item:=2, Before:=1, Key:="TWO"

'add value 7 with Key "SEVEN" as third item in the collection
'parameter names not in proper order – valid

C.Add Key:="SEVEN", Item:=7, After:=2

'Retrieve value using it's KEY from collection

strKey = ""
Do While strKey = ""
    strKey = InputBox("Value Key: " & vbCr & vbCr & "Q - Quit", "Enter Key", "")
    
    Select Case strKey
        Case "Q"
           Exit Do
        Case "TWO", "FIVE", "SEVEN", "FIFTEEN", "7+", "DISK", "999"
           strGet = C(strKey)
        Case Else
           strGet = " Not Found!"
   End Select
   
MsgBox "Key:<<" & strKey & ">> Value: " & strGet
strKey = ""
Loop

Set C = Nothing

End Sub

The KEY value must be of String Type.  The KEY value must be a unique identifier.

Refer to the second image, on top of this page, as a reference for the proper order of Parameters of Add Method of Collection Object, displayed by VBA Intellisense.

The above sample programs with mixed types of data items were presented for demonstration purposes only.  The Collection Object will normally contain only one type of data and is mostly used for adding Objects, like Forms, Reports, Class-Module Objects, Database Objects, and so on, with their properties and methods.

CLASS MODULES.

1. MS-Access Class Module and VBA
2. MS-Access VBA Class Object Arrays
3. MS-Access Base Class and Derived Objects
4. VBA Base Class and Derived Objects-2
5. Base Class and Derived Object Variants
6. Ms-Access Recordset and Class Module
7. Access Class Module and Wrapper Classes
8. Wrapper Class Functionality Transformation

COLLECTION OBJECT.

1. Ms-Access and Collection Object Basics
2. Ms-Access Class Module and Collection Object
3. Table Records in Collection Object and Form

DICTIONARY OBJECT.

1. Dictionary Object Basics
2. Dictionary Object Basics-2
3. Sorting Dictionary Object Keys and Items
4. Display Records from Dictionary to Form
5. Add Class Objects as Dictionary Items
6. Update Class Object Dictionary Item on Form