Solved

Collections

Posted on 2009-03-31
14
473 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

757 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

20 Experts available now in Live!

Get 1:1 Help Now