Iterating through a User Defined Collection 1

[MacDaddyNickP]

I have a Collection Object called SvcTags to which I add a number of class objects called oSvcTag. The code executes fine in adding items to the collection, however, When I try to iterate through the collection of objects in a later step of the Procedure to view properties of the instantiated members of the collection, I only get data from the last added item. The code is below. How do I view data about the 'Jth element' of a collection. My experience with Collections is new.

Option Compare Database
Option Explicit

Public Sub TestClassSvcTag()

Dim SvcTags As New Collection
Dim oSvcTag As New clsSvcTag
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i, j As Long
Dim Item As Object

'Set SvcTags = New Collection

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT [Service Tag Number] FROM D3_Tag_Data;")

j = 0

With rs
.MoveLast
.MoveFirst
For i = 0 To .RecordCount - 1

With SvcTags
'Dim oSvcTag As New clsSvcTag
j = j + 1
.Add Item:=oSvcTag, Key:=CStr(j)
oSvcTag.SvcTagID = rs![Service Tag Number]
oSvcTag.LoadComponents
oSvcTag.LoadSvcTagInfo

Debug.Print oSvcTag.SvcTagID
Debug.Print oSvcTag.Chassis
End With
.MoveNext
Next i
End With

j = 1
For Each Item In SvcTags

Debug.Print SvcTags.Item(j).SvcTagID
j = j + 1

Next Item


Stop

Set oSvcTag = Nothing

End Sub

The Class I am building works fine, It is getting to view/manipulate the various instantiations in the collection I'm unclear about.

 

[Golom]

First, this

j = j + 1
.Add Item:=oSvcTag, Key:=CStr(j)

is redundant. Members are added to a collection with sequential indexes starting at 0 and, optionally, they may also have a Key value associated with them. In this instance you are creating a key that will have the same value (except that it's text) as the index that the collection is creating.

To enumerate the collection

Dim Item As clsSvcTag    [COLOR=green]' Rather than 'Object'[/color]

For Each Item In SvcTags
   Debug.Print Item.SvcTagID
Next

or

For j = 0 To SvcTags.Count - 1
   Debug.Print SvcTags(j).SvcTagID
Next

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Golom]

On second thought ... it may be even worse than redundant.

When you reference a member of a collection using a sub-script (SvcTags(j).SvcTagID, for example), the system examines the value of the subscript ('j' in this case) and determines if it is a number or text. If it's a number then the collection member associated with that internally assigned index is returned. If it's text then the collection member with that key is returned.

Your keys are NOT (as I incorrectly said) the same as the indexes. They are displaced by 1 (i.e. Index '0' has Key '1'). If you then reference

SvcTags(1).SvcTagID

for example. What are you expecting to get? Index #1 or Key Value #1 (which is really Index #0)?

The system is going to interpret the '1' as a number and then give you Index (not Key) #1. In short, you will never be able to reference this collection by Key ... and worse, even if you try it will probably appear to succeed but give you an incorrect member of the collection.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[MacDaddyNickP]

Golom,

Okay, I follow you on the possible confusion about key values versus the inherent index number, so I made a change to add a text key value. Now, however, when I iterate through the collection, or try to, I'm only getting the value of the last record's SvcTagID property.

Public Sub TestClassSvcTag()

Dim SvcTags As New Collection
'Dim oSvcTag As New clsSvcTag
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i, j As Long
Dim Item As Object

'Set SvcTags = New Collection

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT [Service Tag Number] FROM D3_Tag_Data;")

j = 0

With rs
.MoveLast
.MoveFirst
For i = 0 To .RecordCount - 1

With SvcTags
Dim oSvcTag As New clsSvcTag
.Add Item:=oSvcTag, Key:=rs![Service Tag Number]
oSvcTag.SvcTagID = rs![Service Tag Number]
oSvcTag.LoadComponents
oSvcTag.LoadSvcTagInfo

Debug.Print oSvcTag.SvcTagID
Debug.Print oSvcTag.Chassis
End With
.MoveNext
Next i
End With


For Each Item In SvcTags

Debug.Print Item.SvcTagID

Next Item


Stop

Set oSvcTag() = Nothing

End Sub

Why can I not iterate the collection?

 

[Golom]

That's because you are creating only one oSvcTag object. The statement

Dim oSvcTag As New clsSvcTag

is executed only once on program load even though you have it in a loop.

The other problem is that you are adding the service tag to the collection before you assign values to its properties.

Try this

Dim oSvcTag As clsSvcTag
With rs
    .MoveLast
    .MoveFirst
    Do Until .EOF
    
        With SvcTags
            Set oSvcTag = New clsSvcTag
            oSvcTag.SvcTagID = rs![Service Tag Number]
            oSvcTag.LoadComponents
            oSvcTag.LoadSvcTagInfo            
            .Add Item:=oSvcTag, Key:=rs![Service Tag Number]
            Debug.Print oSvcTag.SvcTagID
            Debug.Print oSvcTag.Chassis
            Set oSvcTag = Nothing
        End With
    .MoveNext
    Loop
End With

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[MacDaddyNickP]

Thanks Golom. That worked!

 

[MacDaddyNickP]

Golom,

Now the collection appears to contain the correct number of oSvcTag Objects with the corresponding properties assigned correctly. The issue I now have is that while a "For Each Item in SvcTags" loop works, my attempt to use the index value either the 'jth' item or by using the text key value gives me a subscript out of range error. Can you show me how to access the 'jth' object and how to access the object with key value "x"? I've supplied the code as it now runs (note the error is raised on the line: Debug.Print SvcTags(j).SvcTagID.

Code:
Public Sub TestClassSvcTag()

Dim SvcTags As New Collection
Dim oSvcTag As clsSvcTag
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i, j As Long
Dim Item As Object

'Set SvcTags = New Collection

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT [Service Tag Number] FROM D3_Tag_Data;")

With rs
.MoveLast
.MoveFirst
Do Until .EOF

With SvcTags
Set oSvcTag = New clsSvcTag
oSvcTag.SvcTagID = rs![Service Tag Number]
oSvcTag.LoadComponents
oSvcTag.LoadSvcTagInfo
.Add Item:=oSvcTag, Key:=rs![Service Tag Number]
Set oSvcTag = Nothing
End With
.MoveNext
Loop
End With

Debug.Print SvcTags.Count

For j = 0 To SvcTags.Count - 1

Debug.Print SvcTags(j).SvcTagID

Next


For Each Item In SvcTags

Debug.Print Item.SvcTagID

Next Item


Stop

Set oSvcTag() = Nothing
Set rs = Nothing
Set db = Nothing

End Sub

 

[PHV]

For j = 1 To SvcTags.Count

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Sorry. That's my mistake. Collections are 1-based ... not 0-based. You need

For j = 1 To SvcTags.Count

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[MacDaddyNickP]

Thanks PHV. That worked.

 

[MacDaddyNickP]

Now the question is "How do I reference an instance of the collection by its text key? I'm not able to do so using

Debug.Print SvcTags("15VKF91").SvcTagID ... for example.

 

[Golom]

What's happening? Are you getting an error? Nothing printing?

Your reference looks correct. The only things that I can think of that may be a problem are
[li]15VKF91 isn't a valid Service Tag Number[/li]
[li]There may be padding around the service tag numbers that you are loading as keys. Try doing a Trim on them as you set them as keys.[/li]
[li]You may have NULL or empty service tag numbers in your table[/li]

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[MajP]

Golom,
You state that you can use a for next construct with custom collections. Does A2003 support that? In the past you always had to do a pretty substantial work around of exporting the module and editing the source file.
Second I see lots of examples where you are not referencing the item property. This was also a requirement in the past, has that changed?

MacDaddy,
As far as I know the Item property is still required for custom classes. Yes you can do a work around to build your own default properties. So I think
Debug.Print SvcTags.item("15VKF91").SvcTagID