ACC: How to Create a Collection of Collections (154448)


The information in this article applies to:

  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q154448
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

A limitations of the Visual Basic for Applications Collection object is that it cannot contain a user-defined data type. You can achieve similar behavior, 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 "nested" collections.

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 your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

To create a collection within a collection, follow these steps:

  1. Create a module and type 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, type the following in the Debug window, and then press ENTER:

    CollectionCollection

    Note that the contents of the collection appears in the Debug window.

REFERENCES

For more information about collections, search the Help Index for Collection object.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbProgramming KB154448
©2004 Microsoft Corporation. All rights reserved.