Microsoft Access VBA Macro Programming

The Do Until loop adds the objects into the collection from a source of data. This could be

an external database, or it could just be a local array holding the information. In this example,

it is a simple query onto the table you created earlier called MyTable.

The variableobjPname, which was defined as ourMyPNameobject, is set to a new

Pnamefor each loop, based on the field in the recordset called MyName. This means create

a new instance ofMyPNamethat can hold data and be added to theMyPNamescollection.

The objectobjPnameis then added to theMyPnamescollection. At the end of this loop,

all the data in the recordset has been added into the MyPNames collection. You also need to

add a public function calledItemso you can refer to individual objects within the collection

by their index number. Enter this into the same class module as the rest of the code:

Public Function Item(Index As Integer) As Pname

Set Item = mPnames.Item(Index)

End Function

This definesItemas a function of the objectMyPnamesand setsItemto point to the

mPnamescollection based on the index number given. BecausemPnameswas defined as a

Collectionobject, it already has theItemfunction built into it. This function provides a

“mirror” of what is going on in themPnamescollection in the background.

You will need to add a property calledCountso your code can establish how many

MyPNameobjects there are in theMyPNamescollection:

Public Property Get Count() As Long

Count = mPnames.Count

End Property

In this case, you only need a propertyGetbecause this is a read-only property, and it gets

updated by theClass_Initializeroutine adding in the source data. This acts again as “a

mirror” against themPnamesobject, which is defined as a collection and already has the

Countproperty built into it.

You have now set up a collection calledMyPNamesof objects calledMyPName, which

simply refers to a recordset based on the table USysRibbons. The collection has aCount

property and anItemmethod.

Using the PNames Collection

You can now use yourCollectionobjectMyPNameswithin standard code just as you would

any other object. Use the following code within a standard Access VBA module:

Sub test_class()

Dim pn As New MyPNames, n As Integer

MsgBox pn.Count

For n = 1 To pn.Count

270 Microsoft Access 2010 VBA Macro Programming