Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Create a Collection of Collections

ACC2000: How to Create a Collection of Collections

View products that this article applies to.

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).

↑ Back to the top

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.

↑ Back to the top

More information

To create a collection within a collection, follow these steps:
  1. 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
  2. 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.

↑ Back to the top

References

For more information about collections, click Microsoft Visual Basic Help on the Help menu, type collection object in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

↑ Back to the top

Keywords: KB210035, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 210035
Revision : 4
Created on : 7/15/2004
Published on : 7/15/2004
Exists online : False
Views : 376