Adding Objects to a Custom Collection

After you've declared the Collection object, you can insert new items into the collection by using the Add method. The objects with which you populate your collection do not have to be of the same data type. The Add method looks as follows:

object.Add item, key, before, after

You are only required to specify object and item. Object is the collection name. This is the same name that was used in the declaration of the Collection object. Item is the object you want to add to the collection.

Although the other arguments are optional, they are quite useful. It's important to understand that the items in a collection are automatically assigned numbers starting with 1. However, they can also be assigned a unique key value. Instead of accessing a specific item with an index (1, 2, 3, and so on) at the time an object is added to a collection, you can assign a key for that object. For instance, to identify an individual in a collection of students or employees, you could use Social Security numbers as a key. If you want to specify the position of the object in the collection, you should use either the before or after argument (do not use both). The before argument is the object before which the new object is added. The after argument is the object after which the new object is added.

Part I

The NewEmployees procedure in the following hands-on declares the custom collection object called colEmployees.

©Hands-On 8-1: Creating a Custom Collection

This chapter's hands-on exercises are provided in the Acc2003_Chap08.mdb file included in the book's downloadable files.

1. Open Acc2003_Chap08.mdb or, if you'd like to start from scratch, create a new Microsoft Office Access 2003 database.

2. Switch to the Visual Basic Editor window and insert a new module (Insert | Module).

3. In the new module, enter the NewEmployees procedure as shown below.

Sub NewEmployees()

' declare the employees collection Dim colEmployees As New Collection

' declare a variable to hold each element of a collection Dim emp As Variant

' Add 3 new employees to the collection With colEmployees

.Add Item:="John Collins", Key:="128634456" .Add Item:="Mary Poppins", Key:="223998765" .Add Item:="Karen Loza", Key:="120228876", Before:=2 End With

' list the members of the collection For Each emp In colEmployees Debug.Print emp


MsgBox "There are " & colEmployees.Count & " employees." End Sub

Note that the control variable used in the For Each.. .Next loop must be Variant or Object. When you run the above procedure, you will notice that the order of employee names stored in the colEmployees collection (as displayed in the Immediate window) may be different from the order in which these employees were entered in the program code. This is the result of using the optional argument before with the Karen Loza entry. This argument's value tells Visual Basic to place Karen before the second item in the collection.

Introduction to Access 2003 VBA Programming

0 0

Post a comment