A collection within a collection?

[CanuckAmok]

Howdy,

I have a class module that contains a collection of objects. These objects each also contain a collection of objects. I'm having trouble referencing anything in this sub-collection.

The main object represents a physician's clinic, which contains a collection of appointment slots (time, room, etc.). Each slot contains a collection of appointments (i.e., patients). How do I reference a particular patient? I've tried, for example:

objClinic.slots(i).appointments(j).patientID

...but that returns an error. Is it possible to have a collection within a collection like that, and if so, how is it referenced?

Thanks for any help!

J.

 

[wazz]

i think the approach to collections is to separate them into their own class.
cAppointment
cAppointments
etc.

rather than write it out (and since i'm working on this myself) here's a link i found helpful:
http://www.databaseadvisors.com/newsletters/newsletter200503/0503usingcustomcollections/using%20custom%20collections%20in%20microsoft%20access.asp

 

[MarkK]

It is possible to nest collections and your reference sample is syntactically correct. What error are you getting?
Note that members of collections are always typed as Object at design time so you can't use intellisense to reference members of the object. I sometimes assign the referenced member of a collection to an appropriately typed object variable to enable intellisense and make the code writing easier and make errors more findable.

  dim capp as cAppointment
  set capp = objClinic.slots(0).appointments(0)
  debug.print capp.patientID [COLOR="Green"]'capp is intellisense enabled[/COLOR]

If you get an 'Object or With block variable not set' error, then one of your referenced objects has a value of 'nothing'. You can test for this in the immediate window using ...

? typename(objClinic.slots)

...which returns the type of the object--in this case 'Collection'--or 'nothing' if the object has not been assigned a value. You can also use ...

? objClinic.slots(1).appointments is nothing

...which returns True or False as the case may be.
A common error when instantiating an object that contains other objects is that these 'sub-objects' are not automatically instantiated, for instance, your objClinic might correctly instantiate its 'slots' collection on first reference, but members of the slots collection--presumably slot objects--might not automatically instantiate their 'appointments' collections on first reference.

 

[MarkK]

I agree with you Wazz, and thats' what I do when writing a collection of custom classes. A drawback though is that VBA doesn't allow a default property so the reference chain can get pretty verbose, like...

objClinic.slots.item(0).appointments.item(0).patientID

And this bears on that thread the other day that had...

Public Items as Variant

Its a class like cAppointments where I'd always have a...

Public Items As VBA.Collection

...and a...

Property Get Item(index) As cAppointment
[COLOR="Green"]  'returns a strongly typed member of the collection
  'so consumers have the benefit of intellisense[/COLOR]
  Set Item = Items(index)
End Property

 

[CanuckAmok]

Well, I'm glad I was syntactically correct! That's a start, that was the question I was having a hard timing figuring out. Now I can look for my errors elsewhere.

I believe I found where I was going wrong. Thanks to the clues offered in your replies, you led me in the right direction: the sub-object wasn't automatically instantiated, and some slots had no appointments so there was nothing to refer to. I added a little bit in there to make sure there's at least one empty sub-object to start with, and the error message has gone away.

Thanks again Wazz and Lagbolt! You've saved a lot of frustrated hair pulling.

 

[wazz]

i think i see.
the example i'm looking at has declared

Private m_PrivateCollection As Collection

and

Public Function Add(FullName As String, PhoneNumber As String, _
                    Rating As Integer) As cContact
    Dim newItem As New cContact
    etc...

    ' add to the private collection
    m_PrivateCollection.Add newItem, Key
    Set Add = newItem
    ...
End Function

Function Item(index As Variant) As cContact
    Set Item = m_PrivateCollection.Item(index)
End Function

leaving no direct access to the collection. this would:

Public Items As VBA.Collection

 

[gemma-the-husky]

i keep saying this

if only vba gave us a pointer variable type we could roll our own functionality to do whatever we wanted.

why cant it - it cant be hard for a compiler, surely.

 

[MarkK]

Wazz: Exactly.
Or you could re-expose the collection from a property for greater control...

Private m_Collection As VBA.Collection

Property Get Items As VBA.Collection
[COLOR="Green"]  'allows error checking or validation--more control than a public variable.[/COLOR]
  Set Items = m_Collection
End Property

This I would express as a Property Get which probably only has the effect of changing the icon you'd see via intellisense or in an object browser, but it doesn't so much perform an operation as return a predefined value or attribute.

Property Get Item(index) as cConact
[COLOR="Green"]'Function Item(index As Variant) As cContact[/COLOR]
    Set Item = m_PrivateCollection.Item(index)
End Function

Canuck: instantiating some objects can be costly in processing time particularly those that support a collection. If your class exposes other classes you can defer the creation of those contained classes until they are referenced. Consider the 'Address' property in this example class...

private m_id as Long
private m_address as cAddress  [COLOR="Green"]'nothing until Address property, below, is referenced[/COLOR]

property get JobID as long
  JobID = m_id
end property

property get Address as cAddress
[COLOR="Green"]  'this property might not be referenced for this object's lifetime
  'in which case this code never runs[/COLOR]
  if m_address is nothing then
[COLOR="Green"]    'but if a consumer does reference this property, create the object[/COLOR] 
    set m_address = new cAddress
[COLOR="Green"]    'and only run potentially costly initialization code in cAddress once
    'on first reference[/COLOR]
    m_address.load GetData("AddressID")
  end if
[COLOR="Green"]  'but always, for subsequent calls, return a valid instance[/COLOR]
  set Address = m_address
end property

public function Load(JobID As Long) as cJob
[COLOR="Green"]  'creates, and if necessary returns, a cJob object[/COLOR]
  m_id = JobID
  set Load = me
end function

private function GetData(field as string) as variant
[COLOR="Green"]  'returns values of specific fields for this instance of a cJob[/COLOR]
  GetData = DLookup(field, "tJob", "JobID = " & m_id)
end function

This way your classes can expose lots of other complex classes but still instantiate very rapidly.

 

[wazz]

hi lagbolt,
i'm having some trouble trapping for nulls. seems to be in here, or similar:

property get Address as cAddress
[COLOR=green] 'this property might not be referenced for this object's lifetime[/COLOR]
[COLOR=green] 'in which case this code never runs[/COLOR]
  if m_address is nothing then
[COLOR=green]   'but if a consumer does reference this property, create the object[/COLOR] 
    set m_address = new cAddress
[COLOR=green]   'and only run potentially costly initialization code in cAddress once[/COLOR]
[COLOR=green]   'on first reference[/COLOR]
    m_address.load GetData("AddressID")   [COLOR=red][B]<---[/B][/COLOR]
  end if
[COLOR=green] 'but always, for subsequent calls, return a valid instance[/COLOR]
  set Address = m_address
end property

i've wrapped GetData's DLookup in 'Nz' and that's helping but of course it returns a value. then i have to write code to determine if what was returned is what i want or is the result of Nz. which leads to other things. not sure if this use of Nz is a good idea; can't seem to account for everything. i also tried changing a bunch of the variables i'm using into variants, and that helped somewhat but errors would still lead back to the GetData method and Invalid Use of Null. perhaps running GetData at the top of the sub before anything else and exiting (or other) if Null? but then a cAddress couldn't be returned...