A Collection object is an ordered set of items that can be referred to as a unit.
Collection
The Collection object provides a convenient way to refer to a related group of items as a single object. The items in a Collection, known as members, need only be related by the fact that they are in the Collection. It is not necessary that members of a Collection be of the same data type.
A Collection can be created in the same way as other objects. For example:
Dim X As New Collection
Once created, members can be added to the Collection using the Add method and removed using the Remove method. Specific members can be returned from the Collection using the Item method, while the entire collection can be iterated using the For Each...Next statement.
Count Property.
Add Method, Item Method, Remove Method.
Add Method, For Each...Next Statement, Item Method, Remove Method.
In Microsoft Access, a Collection object can consist of a set of objects of any type. You can create a Collection object that consists of several objects of the same type or objects of different types.
The collection defined by a Collection object can contain any combination of Microsoft Access objects, data access objects, and objects from any other application that exposes its objects to Microsoft Access. You can also add a Collection object to a collection defined by a Collection object.
The collection can also contain user-defined objects that you create in Microsoft Access. For example, you can create user-defined objects with custom methods and properties from a form module or report module. Then you can add these objects to a collection defined by a Collection object, and manipulate them as a set.
This example creates a Collection object (MyClasses), then creates a dialog box in which users can add objects to the collection. To see how this works, choose the Class Module command from the Insert menu and declare a public variable called InstanceName at module level of Class1 ( type Public InstanceName) to hold the names of each instance. Leave the default name as Class1. Copy and paste the code into the General section of another module, then start it with the statement ClassNamer in another procedure. (This example only works with host applications that support classes.)
Sub ClassNamer()
Dim MyClasses As New Collection ' Create a Collection object.
Dim Num ' Counter for individualizing keys.
Dim Msg As String ' Variable to hold prompt string.
Dim TheName, MyObject, NameList ' Variants to hold information.
Do
Dim Inst As New Class1 ' Create a new instance of Class1.
Num = Num + 1 ' Increment Num, then get a name.
Msg = "Please enter a name for this object." & Chr(13) _
& "Press Cancel to see names in collection."
TheName = InputBox(Msg, "Name the Collection Items")
Inst.InstanceName = TheName ' Put name in object instance.
' If user entered name, add it to the collection.
If Inst.InstanceName <> "" Then
' Add the named object to the collection.
MyClasses.Add item := Inst, key := CStr(Num)
End If
' Clear the current reference in preparation for next one.
Set Inst = Nothing
Loop Until TheName = ""
For Each MyObject In MyClasses ' Create list of names.
NameList = NameList & MyObject.InstanceName & Chr(13)
Next MyObject
' Display the list of names in a message box.
MsgBox NameList, , "Instance Names In MyClasses Collection"
For Num = 1 To MyClasses.Count ' Remove name from the collection.
MyClasses.Remove 1 ' Since collections are reindexed
' automatically, remove the first
Next ' member on each iteration.Sub
The following example creates a Collection object, and then creates a dialog box in which users can add user-defined objects to the collection. To test this example, create a new form and save it as Form1. In the form’s module, declare a public variable varInstName. Your new user-defined objects will be derived from this form and its module.
You can manipulate individual objects within the collection by calling the methods or setting the properties that you have defined for them. In the module of Form1, enter the following code.
' Declare public variable in Declarations section of form module.varInstName As Variant
Sub ChangeCaption (strCaption As String)
Me.Visible = True
Me.Caption = strCaptionSub
This Sub procedure becomes a method which you can perform on your user-defined object, once you have created a new instance of the object.
Once you have completed these steps, enter the following procedure in a standard module. When you run this procedure, it will create new instances of your user-defined object and add them to the collection defined by the Collection object. Then it will display the objects (forms in this case) with their changed captions.
Function MakeCollection()
' Create new Collection object using New keyword.
Dim colFormObjects As New Collection
' Declare other variables.
Dim strObjName As String, strList As String
Dim strMsg As String, strCRLF As String
Dim intI As Integer
strCRLF = Chr(13) & Chr(10)
' Create objects and add them to collection.
Do
' Create new instance of user-defined object.
Dim frmObject As New Form_Form1
strMsg = ("Please enter a name for this new object." & strCRLF _
& "Press Cancel to see the items in the collection.")
' Get name for this instance of object.
strObjName = InputBox(strMsg, "Name the collection item:")
' Store name in public variable you declared in form module.
frmObject.varInstName = strObjName
' If user entered name, add object to collection.
If frmObject.varInstName <> "" Then
colFormObjects.Add Item := frmObject
End If
' Clear object variable so it can be set to next instance.
Set frmObject = Nothing
Loop Until strObjName = ""
' Enumerate and display items in collection.
' Note that items in Collection object are indexed beginning with 1.
intI = 1
For Each frmObject In colFormObjects
strList = strList & frmObject.varInstName & strCRLF
frmObject.ChangeCaption ("New Form #" & intI)
intI = intI + 1
Next frmObject
' Display list of object names.
MsgBox strList, , "Objects in colFormObjects collection"Function