Unkown Cls/Collection Holding An Event

[dalski]

I wrapped all textboxes in my frm.description in a cls to assign a right-click CommandBar menu to all textboxes to save me doing it individually. Then added them to user-defined collection so the right-click event would stay around.

The trouble is I was playing around with the cls names & collection names I now no longer know what it's been assigned to; and if it is only a single instance.

How do i list all collections belonging to the form/ wrapped class & tidy up my form? Unsure of the name of the associated cls/ collection.

 

[MajP]

You are probably going to have to provide more info. I cannot follow what you are asking. Maybe an example would help.
Not sure if this is what you are asking but I will take a guess. When I build a custom collection class to hold instances I usually put a property in there called ToString. I also put a ToString property in the class itself. This prints out all of the items and information about them.

Here is my toString in my Pet class

Public Property Get ToString() As String
  ToString = "I am a:" & Me.AnimalType & ", My Name is:" & Me.Name & ", I am: " & Me.AgeInYears & ", The Sound I make: " & Me.Sound
End Property

Here is the property in the Custom collection

Public Property Get ToString() As String
  Dim strOut As String
  Dim i As Integer
  For i = 1 To Me.Count
    strOut = strOut & Me.Item(i).ToString & vbCrLf
  Next i
  ToString = strOut
End Property

Here is a test

  Dim myPets As New Pets
  myPets.Add "Toto", 5, "Dog", "Bow wow"
  myPets.Add "Flipper", 8, "Dolphin", "Squeak whistle"
  'Using the other add method
  Dim myPet As New Pet
  myPet.Initialize "Dino", "100", "Dinosaur", "Roar"
  myPets.Add_Pet myPet
  Debug.Print "I have " & myPets.Count & "Pets"
  Debug.Print myPets.Item(1).Name
  'The default works on the collection
  Debug.Print myPets(2).Name
  'The default works on the Pet class and you can use key
  Debug.Print myPets("dino")
  For Each myPet In myPets
    myPet.MakeSound
  Next myPet
  Debug.Print myPets.ToString
  myPets.Remove ("toto")
  Debug.Print "I have " & myPets.Count & " pets"
  myPets(1) = "Tiger"
  Debug.Print myPets(1)
End Sub

Here are the results

I have 3Pets
Toto
Flipper
Dino
I am a:Dog, My Name is:Toto, I am: 5, The Sound I make: Bow wow
I am a:Dolphin, My Name is:Flipper, I am: 8, The Sound I make: Squeak whistle
I am a:Dinosaur, My Name is:Dino, I am: 100, The Sound I make: Roar

I have 2 pets
Tiger

 

[dalski]

Thanks bud, it is not a collection class, just a plain old collection. I was wrapping Text Box controls in a class. Assigning an Event to the class & then adding them to a standard collection so the event/ class would stick around. But as i said unhelpfully changed the names lots of times so I'm now unaware what collection they are stored in.

I want to list all user-defined Collections (standard not class-collections) in my current db. From there I should be able to find the culprit (famous last words).

E.g. I assigned the onMouseUp event to the controls, so i could easily go in VBA & remove the assigned function from each control; BUT I have at least 1 rogue collection which contains the controls so the user-defined collection (plain old collection; not a class-collection) would remain in the form/ class. I created the collections both in the class & in the form so I imagine the collections could be in both the form & in the unknown class'. Imagine I need to find out what classes & collections are open in the currentDb; how do I find this out. There does not seem to be a Collections.Collection nor a Classes collection.

Sorry I know I'm not explaining it well as my lack of understanding.

 

[MajP]

Could you simply do a "find" in vba searching for
"collection"

i.e. dim Something as Collection

You will then find all the collections you declared in code.

 

[dalski]

The problem is I created many different collections and classes relating to this particular form. So whatever is currently in the VBA code is irrelevant if you get my drift.

Somewhere in the CurrentProject/ CurrentDb I have rogue instances of a Collection and or Classes which I need to find. I thought MSys would help me out but they only show Access standard objects I think.

I would've thought there would be a way to list all user-declared Collections & Classes.

 

[MajP]

But as i said unhelpfully changed the names lots of times so I'm now unaware what collection they are stored in.

Maybe I am missing something in the explanation but it does not seem to hard.

1. Search for Collection and find all of the collections you declare.
write them down if you have to
2. Now search each one for the add method.
If you have a collection "someCollection" search for "somecollection.add"
Now you know all the places you add something to a collection.
3. Now search all places you assign a collection to your collection just in case
"someCollection = "

 

[dalski]

I believe that would be helpful if I knew the Collection Name & if it was the same as is currently written in the VBA code, BUT I do not & the code is not the same. As I said I changed the names lots of times. So we don't know what the Collection's name is.

So the collection's compiled name and currently living in Access' memory is unknown. I really played around assigning collections to the Form itself & to the random classes I was created so really don't know where it is & how many are out there.

So it brings this expansive question of how does one ascertain what user-defined Classes & Collections are in the CurrentDb? Caused either by incompetent memory leaks with circular pointers & ones which have been properly executed.

 

[GPGeorge]

I believe that would be helpful if I knew the Collection Name & if it was the same as is currently written in the VBA code, BUT I do not & the code is not the same. As I said I changed the names lots of times. So we don't know what the Collection's name is.

So the collection's compiled name and currently living in Access' memory is unknown. I really played around assigning collections to the Form itself & to the random classes I was created so really don't know where it is & how many are out there.

So it brings this expansive question of how does one ascertain what user-defined Classes & Collections are in the CurrentDb? Caused either by incompetent memory leaks with circular pointers & ones which have been properly executed.

I think what Pete is suggesting is that you use the search term "collection", not the specific name of any given collection. But I may be reading his suggestion wrong.

 

[MajP]

I believe that would be helpful if I knew the Collection Name & if it was the same as is currently written in the VBA code, BUT I do not & the code is not the same. As I said I changed the names lots of times. So we don't know what the Collection's name is

Are you suggesting that you named a collection "A" and added objects to it. Then you changed the name to "B". You think there are phantom objects floating around in "A". That is not a thing. Does not work that way.

My only guess is what you think is going on is not what is really happening. The only objects that exist are the ones that are declared in vba now. If it is not declared in vba now, I can guarantee there is no floating around circular reference or memory leak if that is even a thing.

 

[sonic8]

So it brings this expansive question of how does one ascertain what user-defined Classes & Collections are in the CurrentDb?

Collections are not persistent objects, they are just variables. As every other variable, they exist as "plain text" in your VBA code and at runtime of the code in memory of the Access process.

 

[MarkK]

You can create a leak if objects in the collection hold a reference to the collection itself. Obviously the collection holds references to its contents, but if the contents also hold a reference the collection, you have a circular reference, which, if initially declared and constructed in a form, will not go out of scope automatically when the form is closed.

 

[MajP]

@dalski,
I think you need to explain the problem and how the problem manifests. We can suggests ways to error check or track it down.
I do not think your troubleshooting is focusing on the correct approach. Especially hoping to determine which objects are held in memory.

For example when you add something to a collection it may it is simple as putting some debug.print to log when and what was added to the collection. Or add a debug when you declare a collection variable.

 

[dalski]

Thanks for your patience guys. The trouble is I have done much experimenting making it impossible to explain exactly what I've done. Again thanks for your patience. Also not helped by my first play with a CommandBar...

Are you suggesting that you named a collection "A" and added objects to it. Then you changed the name to "B". You think there are phantom objects floating around in "A". That is not a thing. Does not work that way.

Something along them lines. In @jwcolby54 generous book 'Event Driven Programming In Access' (high-calibre coder like yourself @MajP & the vast majority on this forum) he wraps controls to format them generically. To clarify I'm not saying you or J Colby is wrong here obviously, it's me. But I'm trying to learn so bear with me. As the class dies on completion of code he adds the items to a collection; so the pointer to memory stays lives on & associated events. I definitely caused a memory leak as I overloaded the Stack, so be it caused by an objVar pointing to a cls & the clsObjVar pointing to the form... whatever; a memory leak definitely happened. My fault completely & still learning so bear with.

You can create a leak if objects in the collection hold a reference to the collection itself.

Thanks, I think it's something along these lines but an instance of a class & object variable not being terminated properly. How does one go about locating these in memory?

@dalski,
I think you need to explain the problem and how the problem manifests.

Trouble Is I have done many different permutations, so the below is inaccurate:

1. Instantiated a class
2. Looped controls in the Detail section of the form. If it was a text box added it to the collection. Wrapped the controls with the class WithEvents, played around; controls as different classes, populated different classes...
3. Assigned the OnMouseUp event to the wrapped class; many different permutations trying the class itself, the form...
4. Added controls to the collection.

Built A CommandBar

1. In a separate module
2. Assigned a public function to handle the MouseUpEvent (from guidance from your fine article @MajP)

Form Load Event:

Private Sub Form_Load()
  Dim objClsBillAndPage As clsBillAndPageRcTxtbox
  Set objClsBillAndPage = New clsBillAndPageRcTxtbox
End Sub

Cls:

Private WithEvents mObjTxtBox As TextBox
Private mColCtrlsRc As collection

Private Sub Class_Initialize()
  Set mColCtrlsRc = New collection
End Sub
Public Sub fInit(lCtrl As Control)
  Set mObjTxtBox = lCtrl
  mObjTxtBox.OnMouseUp = "=handleRightClickOnBillAndPageF()"
  mColCtrlsRc.Add mObjTxtBox
End Sub

RightClick Module:

'Check if menu exists; deletes & build new one
Private Function ResetCustomBar(BarName As String) As Office.CommandBar
  On Error Resume Next
  On Error GoTo 1
  CommandBars(BarName).Delete
1:
  Set ResetCustomBar = CommandBars.Add(BarName, msoBarPopup, False, True)
End Function

Public Sub RcBuildPageMenu(lFrm As Form)
  Dim Bar As Office.CommandBar
  Set Bar = ResetCustomBar("RcBillPageContextMenu")
 
  With Bar.Controls.Add(msoControlButton)
    .Caption = "+ Page"
    .OnAction = "build menu"
  End With
  Form_TenderBillsAndPagesF.ShortcutMenu = True
  Form_TenderBillsAndPagesF.ShortcutMenuBar = "RcBillPageContextMenu"
End Sub

A - I now have a form where I've disabled the classes, collections... but the MouseUp event is still firing. I only ever assigned this via a class. As all instances of the class are now dead the only way this can stick around is a collection/ unterminated objVar?

B - I can easily remove the associated CommandBar; but I don't understand how the Event is sticking around as supposed to die with the cls.

C - In a basic question one surely could easily list all user-defined collections within MS Access surely? On a sidenote interested in future how does one locate rogue memory items from mismanaged variables...

 

[MajP]

MouseUp event is still firing. I only ever assigned this via a class. As all instances of the class are now dead the only way this can stick around is a collection/ unterminated objVar?

Double check that you do not have a mouse up event in the form itself. In the mouse up event check that you do not have
[Event Procedure] or " = someFunction()"
When I look at your code I can almost guarantee that is the issue
=handleRightClickOnBillAndPageF()

You ran the code and it hung. Then you saved your form from design view and if I now look in your form this is in all the events
=handleRightClickOnBillAndPageF()

 

[MajP]

Also this is wrong

Private WithEvents mObjTxtBox As TextBox
Private mColCtrlsRc As collection

Private Sub Class_Initialize()
  Set mColCtrlsRc = New collection
End Sub
Public Sub fInit(lCtrl As Control)
  Set mObjTxtBox = lCtrl
  mObjTxtBox.OnMouseUp = "=handleRightClickOnBillAndPageF()"
  mColCtrlsRc.Add mObjTxtBox
End Sub

That collection does not go in the class. What example are you looking at?

You need a collection outside of the class to hold the instances. Now you are declaring a bunch of instances of your cls that has a property of a collection that only holds an instance of itself. Not only does that do nothing, but I guess it is a circular reference. You created a property in a class that references the class..

 

[MajP]

But unless there is more to it, there is no need for the class. Why not this on the form load event.

dim ctrl as access.control
for each ctrl in me.controls
 if ctr.tag "HRC" then
  ctrl.onMouseUp = "=handleRightClickOnBillAndPageF()"
 end if
next ctrl

 

[MarkK]

Thanks, I think it's something along these lines but an instance of a class & object variable not being terminated properly. How does one go about locating these in memory?

The definition of a memory leak is that the objects are no longer accessible in code. The variables they were assigned to on creation no longer exist, but their references with each other are still valid, so garbage collection does not destroy them. You could only find them back from memory if you used the ObjPtr() function to save the actual memory address of the object before all your known variables go out of scope.

In addition, a WithEvents object variable maintains a collection of references to its subscribers so it knows what handlers to call when the event fires. If you have multiple WithEvents variables that expose the same instance of an object--like some kind of global notification object--then you have to explicitly destroy your WithEvents variables too.

If I create circular references in code in a form, I handle the Form_Unload() event and destroy that structure explicitly, for instance, if I create a collection with member objects that hold a reference to the collection itself, then do something like...

Private LeakyCollection As VBA.Collection

Private Sub Form_Load()
    ' create leaky collection here
End Sub

Private Sub Form_Unload(Cancel As Integer)
    With LeakyCollection
        Do While .Children: .Remove 0:  Loop
    End With
End Sub

 

[MajP]

Or if you want to trap the event in the class then trap it in the class. You show withevents, but you do not trap the event in the class. You would add it like this in your class.

Private Sub mObjTxtBox_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

End Sub

I hate to say it but looks like you took three separate examples and kind of mashed them together all incorrectly.
1. Using a function to handle multiple controls
2. Using a collection outside of the class to hold instances of the class
3. Using with events to trap a controls event in the class.

 

[MarkK]

Your class would make more sense if it encapsulated the HandleRightClickOnBillAndPageF functionality directly, like...

Private WithEvents tb_ As Access.TextBox

Public Function Init(tb As Access.TextBox) As cTextBoxMouseUpOnBillAndPageFStrategy
  Set tb_ = tb
  Set Init = Me
End Function

Private Sub tb__MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  If Button = vbKeyRButton Then
    ' perform HandleRightClickOnBillAndPageF() functionality here
    ' no need to call some Public Sub
  End If
End Sub

With this approach, the functionality you call at handleRightClickOnBillAndPageF() is encapsulated within the class. This promotes tidy code, because it reduces the pollution of your global namespace with a proliferation of standard module based Public Sub HandleRightClickOnBillAndPageF-like methods.

For even more flexibility, create a cTextBoxRightClickHandler class, and pass in a cBillAndPageFStrategy class ( which exposes an Execute() method ). This way you can decouple generic right-click textbox handling from what actually happens when the right click occurs, which is provided by the Strategy class at runtime.

 

[MarkK]

This class takes a textbox and a strategy, and on TextBox.RightClick it executes the strategy...

Private WithEvents tb_ As Access.TextBox
Private stg_ As Object

Public Function Init(tb As Access.TextBox, stg As Object) As cTextboxRightClickStrategyHandler
  Set tb_ = tb
  Set stg_ = stg
  Set Init = Me
End Function

Private Sub tb__MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Button = vbKeyRButton Then stg_.Execute
End Sub

This class encapsulates a strategy...

Private obj1_ As Object

Public Function Init(obj1 As Object) As cMyStrategy
    ' pass in the objects you need to execute the strategy in
    ' this constructor
End Function

Public Sub Execute()
    ' execute a strategy here using objects like obj1_
End Sub

Now you can write multiple, and very simple, strategy classes that don't care who calls their Execute methods. At runtime, determine which strategy you want to run, pass the right one to the textbox right click handler, and the rest is simple.

This decouples user interface logic from business logic, giving you useful tools in both domains, but without dependencies.