Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Ms-Access and Collection Object Basics

VBA Array is more popular and commonly used, for storing several set of related values (rows and columns), than Collection Object.  We have used Arrays to store User-Defined Types and Class Module Object.  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 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 in memory we can add any number of items into it. The Collection members can be of any data type, built-in Objects, Class Module Object or another Collection Object, with it’s own item members.

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

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

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

An image of the Collection Object instance displaying it's methods list below:

  • The Add method adds value or object as Collection member to the Collection Object and the Remove method is for deleting an item from the Collection.
  • The Item method combined with an item number as Index we can retrieve a particular item from 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 few simple mixed data type values: integer, String and Double Precision Number, as Collection members.
  • By using the Add method of C Collection Object we have added five items into the collection.  First two items are integer numbers, next two items are String data type and the last item is a double precision number. 
  • The Collection Object instance name C and the Add method is joined with a dot separator, followed by a space then the actual value to be added to the Collection.
  • The Add method have 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 parameter is used with the .Add method other parameter places must be skipped with comma separator, except for the right side items.  By  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 Collecction 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 have a For . . . Next Loop to run for 1 to the number of items ( C.Count) in the Collection.  The value in the j control variable is used as index number parameter to the Item() method of the Collection Object, to retrieve the value and print it in the debug window.

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 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.

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


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


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 without the use of Parameter Names and giving the parameter value in it’s 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:

Disk 2TB

Disk 2TB

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 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 Address Book, to retrieve item value randomly from the Address Book Collection members.

Let us write a new program to demonstrate the usage of Item Keys with Values in 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 the second image, on top of this Page, as reference for proper order of Parameters of Add Method of Collection Object, displayed by VBA Intellisense.

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

  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
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form
  11. Dictionary Object Basics
  12. Dictionary Object Basics-2
  13. Sorting Dictionary Object Keys and Items
  14. Display Records from Dictionary to Form
  15. Add Class Objects as Dictionary Items
  16. Update Class Object Dictionary Item on Form


  1. "When any of the optional parameter is used with the .Add method other parameter places must be skipped with comma separator, except for the right side items." Do you mean the right-side items don't have commas after them?

    What's the purpose of the Key parameter? Is it like a tag?

  2. .Add Item,[Key],[Before],[After]
    These are the parameters of .Add Method. Assume that we need to Add an item Before the second item in the Collection we will be using two parameters: Item & Before values in the statement as shown below:

    Coll.Add "Grovelli",,2
    Coll.Add "Grovelli",Before:=2

    In the first example above we have inserted two commas after the Item Parameter value (Grovelli) indicating that the KEY parameter is not used and it's place is between those two commas. The value 2 says insert the Item BEFORE the second item in the existing Collection. You should not insert a comma after the value 2 for the last parameter AFTER.

    In the second example, the usage of parameter name with value (BEFORE:=2) doesn't need that extra comma we have used in the first statement. Not only that with the usage of Parameter names you can pass the values in any order you want.
    Coll.Add Before:2, Item:="Grovelli"

    KEY Parameter is a Unique String Value, like Primary Key of Table, Employee Code of Employees etc. You can retrieve an Item directly from the Collection, rather than reading each item from Collection and comparing for a match.

    Assume that your Identity Number is "G123" and added into the Collection as:

    Coll.Add "Grovelli","G123"
    Coll.Add Item:="Grovelli", Key:="G123"
    Coll.Add Key:="G123", Item:="Grovelli"

    then you can directly retrieve it from Collection as below:

    Var = Coll.Item("G123")
    var = Coll("G123")
    Debug.Print Coll("G123")

    The Key value must be of String Data Type.

    The last example Program in this Page shows a demo of this method.

  3. Correction: Coll.Add Before:2, Item:="Grovelli"

    Coll.Add Before:=2, Item:="Grovelli"


Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations Class Module msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Event Menus and Toolbars Collection Object Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Form Command Button Data Dictionary Object Emails and Alerts Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...


Blog Archive

Recent Posts