Solved

Collections

Posted on 2009-03-31
14
474 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
  • 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
 

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MsgBox 4 46
Close Print Preview button not active 12 36
Sub Reports 8 22
Parse last 2 characters of text value if they are letters 7 16
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now