CODEWITHSUNDEEP
vbaclassms-accesscollections
CJ_London
CJ_London

Reputation: 21

Access VBA listing collection items in a class module

Although I'm reasonable experienced VBA developer, I have not had the need to use class modules or collections but thought this may be my opportunity to extend my knowledge.

In an application I have a number of forms which all have the same functionality and I now need to increase that functionality. Towards that, I am trying to reorder a collection in a class module, but get an error 91 - object variable or with block not set. The collection is created when I assign events to controls. The original code I obtained from here (Many thanks mwolfe) VBA - getting name of the label in mousemove event

and has been adapted to Access. The assignments of events works well and all the events work providing I am only doing something with that control such as change a background color, change size or location on the form.

The problem comes when I want to reorder it in the collection - with a view to having an impact on location in the form. However I am unable to access the collection itself in the first place.

The below is my latest attempt and the error occurs in the collcount Get indicated by asterisks (right at the bottom of the code block). I am using Count as a test. Once I understand what I am doing wrong I should be able to manipulate it as required.

mLabelColl returns a correct count before leaving the LabelsToTrack function, but is then not found in any other function.

As you will see from the commented out debug statements, I have tried making mLabelColl Private and Dim in the top declaration, using 'Debug.Print mLabelColl.Count' in the mousedown event and trying to create a different class to store the list of labels.

I feel I am missing something pretty simple but I'm at a loss as to what - can someone please put me out of my misery 

Option Compare Database
Option Explicit

'weMouseMove class module:
Private WithEvents mLbl As Access.Label
Public mLabelColl As Collection
'Dim LblList as clLabels

Function LabelsToTrack(ParamArray labels() As Variant)
Set mLabelColl = New Collection 'assign a pointer
Dim i As Integer

    For i = LBound(labels) To UBound(labels)

        'Set mLabelColl = New Collection events not assigned if set here
        Dim LblToTrack As weMouseMove 'needs to be declared here - why?
        Set LblToTrack = New weMouseMove 'assign a pointer

        Dim lbl As Access.Label
        Set lbl = labels(i)

        LblToTrack.TrackLabel lbl
        mLabelColl.Add LblToTrack 'add to mlabelcoll collection

        'Set LblList as New clLabels
        'LblList.addLabel lbl

    Next i
    Debug.Print mLabelColl.Count 'returns correct number
    Debug.Print dsform.countcoll '1 - incorrect

End Function

Sub TrackLabel(lbl As Access.Label)

    Set mLbl = lbl

End Sub

Private Sub mLbl_MouseDown(Button As Integer, Shift As Integer, x As Single, Y As Single)
Dim tLbl As Access.Label

    'Debug.Print LblList.Count 'Compile error - Expected function or variable (Despite Count being an option
    'Debug.Print mLabelColl.Count 'error 91
    'Debug.Print LblList.CountLbls 'error 91
    Debug.Print collCount

End Sub

Property Get collCount() As Integer

    *collCount = mLabelColl.Count* 'error 91

End Property

Upvotes: 0

Views: 634

Answers (1)

A.S.H
A.S.H

Reputation: 29352

In order to have all the weMouseMove objects reference the same collection in their mLabelColl pointer, a single line can achieve it:

LblToTrack.TrackLabel lbl
mLabelColl.Add LblToTrack
Set LblToTrack.mLabelColl = mLabelColl ' <-- Add this line.

But please be aware that this leads to a circular reference between the collection and its contained objects, a problem that is known to be a source of memory leaks, but this should not be an important issue in this case.

Upvotes: 0

Related Questions