Multidimensional Collection?

[Richard Ohl]

Hi there,
I'm trying to achieve something similar to PHP, where multi-dimensional
arrays can be used to store data conveniently.
I want to have a collection with named (string) keys. To each of those keys
a subcollection is assigned with again named keys and mixed values. The
structure would be:
"fieldName"
"type" = "sometype"
"value" = mixed value (bool, int, string...)
"position" = 18
"otherProps" = "something else"
"nextFieldName"
"type"...

I would need to be able to loop throug the collection keeping the fieldnames
and access the subcollections or, if possible, even directly get specific
data like myCollection("fielName")."type"

Is there a way to get something like that done in VBA? Any hint pointing to
the appropriate docs, examples and tipps are greatly appreciated!
Kind regards,
Richard

 

[Tony Jollans]

Members of Collections can be of any Type, including other Collections.

You add a sub-collection to a collection using syntax like:

Collection1.Add Collection2, "Key1"

.. and you can access sub-collections using syntax like:

Collection1("Key1")("SubCollKey3"), or
Collection1("Key1").Add Whatever, NewKey

Will that satisfy your need?

 

[Karl E. Peterson]

I'm trying to achieve something similar to PHP, where multi-dimensional
arrays can be used to store data conveniently.
I want to have a collection with named (string) keys. To each of those keys
a subcollection is assigned with again named keys and mixed values. The
structure would be:
"fieldName"
"type" = "sometype"
"value" = mixed value (bool, int, string...)
"position" = 18
"otherProps" = "something else"
"nextFieldName"
"type"...

I would need to be able to loop throug the collection keeping the fieldnames
and access the subcollections or, if possible, even directly get specific
data like myCollection("fielName")."type"

Is there a way to get something like that done in VBA? Any hint pointing to
the appropriate docs, examples and tipps are greatly appreciated!

As Tony said, collections can contain collections. That's one route. Depending on
the syntax you're wanting to use, the efficiency of lookups desired, and the
regularity of the subitems, you may also want to consider creating an array (or
collection) of class instances. That could easily support the syntax:

myCollection("fieldName").type

(Assuming "type" weren't a reserved word, at any rate. <g>)

 

[Richard Ohl]

Tony Jollans tastaturte dies:

Members of Collections can be of any Type, including other Collections.

That's a very helpful hint, indeed! I just thought I might be able to mix
your and Karl's ideas - I might be able to create a custom type and add that
to a collection.

The structure of the collection is quite simple for your skills, I'm a
beginner, though... It's intended use is to create a collection of form
elements that shall be bound to a Stored Procedure. Looping through existing
connections in M$ Access does not automatically distinguish between fields I
want and those I don't want to send to a SP.

Assume you have a simple Form: Field A is a type char(5), Field B is an
integer and C is a Varchar(50). Now there is a Field B1, which holds a
calculated Value I don't need in my DB (just for the sake of displaying
something to a user) and Field A1 which is A & B.

Now I wanted to have something I can build the Call to the SP from - in
simple words:
Loop through the collection which is a (manually defined) constant in my
form. For each item put a "@" in front of the "name" property. Get the value
of the field in the form and send it to the SP with the correct type.

The properties of the item would probably be "name", "type", "length" (for
chars/varchars), "isOutput" bool and so on... Everything I need to
dynamically build a call to a pre-defined stored procedure.

Does that make any sense? I'd certainly appreciate any further input!
Many thanks & kind regards,
Richard

 

[Richard Ohl]

Karl E. Peterson tastaturte dies:

Richard Ohl wrote:

As Tony said, collections can contain collections. That's one route. Depending on
the syntax you're wanting to use, the efficiency of lookups desired, and the
regularity of the subitems, you may also want to consider creating an array (or
collection) of class instances. That could easily support the syntax:

myCollection("fieldName").type

Please see my more detailed answer to Tony's post, although you'd certainly
earned a long answer as well! But I don't want to bore you...

You route is very interesting. The subitems are very regular. I've been
unsure whether I could use a custom type because one of the subitems is eg.
"length" which could be left unset on types like integer or bit, but would
be necessary for var-/chars. With decimals or money I'd need to specify
precision and scale, which I don't need for integers and so on... But name,
type, isOutput and so on are always the same. The other properties would
need to default to Nothing or something distinctive to say "I'm not set, so
take your shit and get out!".

Do you have a more detailed example to your above mentioned idea? I'm not
yet that familiar with VBA...

Many thanks and kind regards,
Richard

 

[Tony Jollans]

Unfortunately, having told you that a Collection can contain anything, I
must back-pedal a little Putting a User-defined Type into a Collection is
awkward and it would be easier just to create your own Class. As a simple
example:

Create a New Class Module, for now just let the name default to Class1, and
add this to it:

Public propName
Public propType
Public propLength

In your main routine:

Dim MyProps as Class1
Dim MyColl as Collection

Set MyColl = New Collection

Set MyProps = New Class1
MyProps.propName = "Mercedes"
MyProps.propType = "500SEL"
MyProps.propLength = "16"

MyColl.Add MyProps, "First"

Set MyProps = New Class1
MyProps.propName = "Chrysler"
MyProps.propType = "Voyager"
MyProps.propLength = "16"

MyColl.Add MyProps, "Second"

' For demonstration
Msgbox MyColl("First").propName
Msgbox MyColl("Second").propName

 

[Richard Ohl]

Tony Jollans tastaturte dies:

Unfortunately, having told you that a Collection can contain anything, I
must back-pedal a little Putting a User-defined Type into a Collection is
awkward and it would be easier just to create your own Class. As a simple
example: [snip example]

Hi Tony,
first of all: I apologise for not coming back earlier, I got myself a really
nice cold...
Thanks for that example, I gonna have a closer look into it and report back
than. Again, many many thanks!
Richard