Microsoft KB Archive/160012

= ACC97: How to Use Collections to Manage Class Objects in VBA =

Article ID: 160012

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q160012





SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to use collections in Visual Basic for Applications to manage references to class objects in Microsoft Access 97. This technique allows your class objects to persist, and enables you to control the individual properties of those objects by using the familiar collection syntax used in Microsoft Access for implementing Data Access Objects (DAO) and other Microsoft Office Object models.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.



MORE INFORMATION
In order to use collections to manage class objects, you must do the following:


 * Create an instance of the class
 * Set the properties and methods of the class
 * Add the class to a public collection
 * Unload the instance of the class

You might expect that unloading the instance of the class results in the class being closed and terminated. However, the class object persists because you add it to a collection, which then owns the reference to the class. This is a very powerful technique that allows you to control object references through a collection; the class object does not terminate until you remove it from the collection.

The following example creates a class object and a form object, and then manages both objects from a collection in a standard module.

Create a Class Module
 Create a new database called ClassTest.mdb. On the Insert menu, click Class Module. Save the class module as clsTest.  Type the following lines in the Declarations section: Private This_ClassID As String Private This_frm As New Form_frmTest  Select Class in the Object box of the Module window. "Initialize" is automatically selected in the Procedure box.  Type the following procedure: Private Sub Class_Initialize On Local Error GoTo Class_Initialize_Err Dim Msg As String This_frm.Visible = True This_ClassID = "Initialized" This_frm.Caption = This_ClassID MsgBox "Class Initialized", vbInformation, "Class Example" Class_Initialize_End: Exit Sub Class_Initialize_Err: Msg = "Error #: " & Format$(Err.Number) & vbCrLf Msg = Msg & Err.Description Err.Raise vbObjectError, "clsTest.Initialize (Private)", Msg Resume Class_Initialize_End End Sub </li> On the Insert menu, click Procedure.</li>  In the Insert Procedure dialog box, type ClassID in the Name box and click Property in the Type box. Then type the following procedures: Public Property Get ClassID As Variant ClassID = This_ClassID End Property

Public Property Let ClassID(ByVal vNewValue As Variant) This_ClassID = vNewValue This_frm.ClassID = This_ClassID This_frm.Caption = This_ClassID End Property </li> Save and close the clsTest class module.</li></ol>

Create a Form
<ol> Create the following form not based on any table or query in Design view:

Form: frmTest

-

Caption: TestForm

</li> With the form still open in Design view, click Code on the View menu.</li>  Type the following line in the Declarations section of the form's class module: Dim This_ClassID As String </li>  Add the following event procedure to the form's Unload property: Private Sub Form_Unload(Cancel As Integer) col.Remove This_ClassID End Sub </li> On the Insert menu, click Procedure.</li>  In the Insert Procedure dialog box, type ClassID in the Name box and click Property in the Type box. Then type the following procedures. Note that the Get ClassID function and vNewValue variable in this example are dimensioned as String instead of the default, which is Variant: Public Property Get ClassID As String ClassID = This_ClassID End Property

Public Property Let ClassID(ByVal vNewValue As String) This_ClassID = vNewValue End Property </li> Save and close the frmTest form.</li></ol>

Create a Standard Module
<ol> Create a new standard module and save it as Module1.</li>  Type the following line in the Declarations section: Public col As New Collection </li>  Type the following procedure: Function CreateClassTest As String ' Create an instance of the clsTest class module, which creates ' an instance of the frmTest form. Dim cls As New clsTest

' Create a unique identifier string and set it to the upper index ' of the Public col Collection plus one. Dim varClassId As String varClassId = "Key_" & CStr(col.Count + 1)

' Set the clsTest class module's ClassID property to the value of        ' varClassId, which in turn sets the frmTest.ClassId property to         ' the same value. This is so the form has a method to track its ' relationship to the collection. cls.ClassID = varClassId

' Add the instance of the class object to the collection passing ' varClassId as the Key argument. col.Add cls, varClassId

MsgBox "Created New Collection Item: " & varClassId, _ vbInformation, "Class Example"

' Unload the cls object variable. Set cls = Nothing

' Return the varClassId. CreateClassTest = varClassId End Function </li> Close and save the module.</li></ol>

Test the Example
When you call the CreateClassTest function multiple times, it opens multiple instances of the frmTest form, each of which is unique and capable of managing itself and its participation in the public collection. Each form is aware of its Key position in the collection, and each one removes itself from the collection when you close the form.

The following sample procedure creates three instances of the clsTest class:

<ol>  Create a standard module and type the following procedure: Function CreateThreeItems As Boolean Dim strKeys(1 To 3) As String Dim i As Integer For i = LBound(strKeys) To UBound(strKeys) strKeys(i) = CreateClassTest Next i        For i = LBound(strKeys) To UBound(strKeys) MsgBox col.Item(strKeys(i)).ClassID, vbInformation, _ "Class Test" Next i     End Function </li>  To test this function, type the following line in the Debug window, and then press ENTER: ?CreateThreeItems Note that messages boxes are displayed each time the clsTest class module initializes, when each of three instances of the frmTest form is created, and again after all three instances of the form are open. </li></ol>

<div class="references_section">