Solved

Collections

Posted on 2009-03-31
14
481 Views
Last Modified: 2013-11-27
I am a newbie to using classes in vba and i have a question.
I have created a class object with 4 properties.
I then populate a collection with instances of the class object specifying individual keys for collection entries.
I understand how to loop through the collection to extract the class object property values.
My question is how do i access the class object property values for a specified (by key) entry in the collection?
0
Comment
Question by:JoseJalapeno
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
14 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027352
Not sure what you mean here, you want to identify a class instance by a key?

when you create the class, you could assign a value in that class. Then use the get method on it to get back the value. Would need to define a variable in that class to hold it

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027369
eg in your class

private theKey as String


Public Property Get MyKey() As String
    MyKey = theKey
End Property

Public Property Let MyKey(ByVal sKey as String)
    theKey = sKey
End Property

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027386
So now when you insantiate this class you can set the value like this

    Dim x As New Class1
   
'Set a value
    x.MyKey = "Hello"
   
'Get the value
    Debug.Print x.MyKey

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:JoseJalapeno
ID: 24027452
Maybe i am not doing it right in the first place.
Form module creates a collection filling it with instances of the class module as defined.
As you can see i can loop through cSerials in SerialNumbers to fill a listbox.
I want to be able to reference a particular cSerial in SerialNumbers by the key value perhaps.
Code as follows:
Option Compare Database
 
Dim mID As Integer
Dim mSN As String
Dim mItemID As Integer
Dim mDeliveryNoteID As Integer
 
Public Property Get ID() As Variant
    ID = mID
End Property
 
Public Property Let ID(ByVal vNewValue As Variant)
    mID = vNewValue
End Property
 
Public Property Get SN() As Variant
    SN = mSN
End Property
 
Public Property Let SN(ByVal vNewValue As Variant)
    mSN = vNewValue
End Property
 
Public Property Get ItemID() As Variant
    ItemID = mItemID
End Property
 
Public Property Let ItemID(ByVal vNewValue As Variant)
    mItemID = vNewValue
End Property
 
Public Property Get DeliveryNoteID() As Variant
    DeliveryNoteID = mDeliveryNoteID
End Property
 
Public Property Let DeliveryNoteID(ByVal vNewValue As Variant)
    mDeliveryNoteID = vNewValue
End Property
-------------------------------------------------------------
'Declarations for the form
Dim SerialNumbers As Collection
Dim cSerial As Serials
 
'In a form function
 
'Fill collection with class instances
Set SerialNumbers = New Collection
Do While Not rs.EOF
        Set cSerial = New Serials
            cSerial.ID = rs!ID
            cSerial.SN = rs!SN
            cSerial.ItemID = rs!ItemID
            cSerial.DeliveryNoteID = rs!DeliveryNoteID
        SerialNumbers.Add cSerial, "Key_" & CStr(SerialNumbers.count + 1)
        Set cSerial = Nothing
        rs.MoveNext
Loop
'loop through collection and fill listbox
For Each cSerial In SerialNumbers
        Me.List2.AddItem "" & cSerial.SN & ""
Next

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027599
Why return as variant when you have that variable defined as a integer and string anyway

when u instantiate this class, set the id. Im assuming that is what identifies a unique value

can you show the bit where you instantiate the class and place in your collection? how do you determine the key to use?
0
 

Author Comment

by:JoseJalapeno
ID: 24027671
See bottom of code "'Fill collection with class instances"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027729
lol, sorry. I didnt scroll down
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027757
ok here, assuming ID is unique, why cant you save the ID as the collection key?

so instead of
            SerialNumbers.Add cSerial, "Key_" & CStr(SerialNumbers.Count + 1)

do this
            SerialNumbers.Add cSerial, "Key_" & rs!ID


Now to get the class, get the key, remove the prefix Key_ then using whats left you can find your instance

if rs!SN is unique, you can use that instead of storing "Key_" & rs!ID

does that make sense?
0
 

Author Comment

by:JoseJalapeno
ID: 24027788
Yes it does but the issue isn't creating the key, it's referencing one item in the collection and extracting the class properties from that single item.
Hope the question is clearer.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027930
I think so. Basically you want to get at the class

Given a index you can get at the class
eg

        Set cSerial = SerialNumbers.Item(i)


Say the SN was unique and you want to find the class given a SN (in variable strSN)

    'Get Serial Number Apple2
    Dim bLoop As Boolean
    bLoop = True
    i = 1
    While bLoop

'
        Set cSerial = SerialNumbers.Item(i)
        If cSerial.SN = strSN Then
            MsgBox "found it"
            bLoop = False
        Else
            i = i + 1
            If i > SerialNumbers.Count Then
                MsgBox "cant find it"
                bLoop = False
            End If
        End If
    Wend


Is this what u mean? Sorry to sound so thick in not understanding
0
 

Author Comment

by:JoseJalapeno
ID: 24027979
Probably me that doesn't understand.
Can i not reference a collection item directly without looping through them?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24028058
Well yes. given the key you can do this

    Dim clsSerial As Serials

    Set clsSerial = SerialNumbers.Item("KeyValue")

so lets go back to using either the SN or ID as the key

if SN

sSN = "MySerialNumber"

    Set clsSerial = SerialNumbers.Item(sSN)

if ID

sKey = "Key_" & 5

    Set clsSerial = SerialNumbers.Item(sKey)

the Item method can be used to pass in index or the keyname and returns you that class


0
 

Author Closing Comment

by:JoseJalapeno
ID: 31564748
Thankyou for your help!
I never thought to use the SET command...DUH!!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24028138
Cool :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question