Microsoft KB Archive/210035

= ACC2000: How to Create a Collection of Collections =

Article ID: 210035

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210035



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
A limitation of the Microsoft Visual Basic for Applications Collection object is that it cannot contain a user-defined data type. You can achieve similar functionality, however, by creating a collection that contains the elements that you would normally define in your user-defined data type, and then storing that collection in a second collection. This article shows you how to create such a nested collection.



MORE INFORMATION
To create a collection within a collection, follow these steps:   Create a module, and then type or paste the following procedure: Function CollectionCollection Dim element Dim insideCollection As New Collection Dim outsideCollection As New Collection

' Populate the first element of the collection and add ' it to the parent collection. insideCollection.Add KEY:="fname", Item:="joe" insideCollection.Add KEY:="lname", Item:="smith" insideCollection.Add KEY:="age", Item:=12 outsideCollection.Add insideCollection

' Clear the collection - prevents duplication of elements. Set insideCollection = Nothing

' Populate the second element of the collection and add ' it to the parent collection. insideCollection.Add KEY:="fname", Item:="fred" insideCollection.Add KEY:="lname", Item:="smith" insideCollection.Add KEY:="age", Item:=14 outsideCollection.Add insideCollection

' Print the contents of the parent collection to the Debug window. For Each element In outsideCollection Debug.Print element("fname"), element("lname"), element("age") Next End Function  To test the function, on the View menu, click Immediate Window, type the following command, and then press ENTER:

CollectionCollection

Note that the contents of the collection appear in the Immediate window.

