Class / collection - Subforms issue

[Xenith57]

Hey all,

Not sure if anyone here can assist, but I've scoured the internet trying to find an answer for hours to no avail..

I've got a form with multiple comboboxes, I've made a class and collection for them. When I open the form by itself, it works, events trigger fine. However if I place that form in my main form as a subform, nothing triggers anymore.

My collection still returns the number of comboboxes, so I know it running the code, but it simply won't trigger the class code like it does when it's a stand alone form.

Any thoughts for this? I really want to avoid making indidual afterupdate() 's for each individual combobox.

Thanks

[OBP]

I admire what you have done to get your VBA working for a class of Combos, because it is very useful and I know others who have wanted to do the same thing.
But I have to ask when you say "I really want to avoid making indidual afterupdate() 's for each individual combobox." why?
You have already spent more time in this than just doing the programming.

To try and help with your question I would need a bit more info.
Where in the single form's events is the working code?
Where is it when the form is a subform?
What is the part of the code that triggers the individual combos?

[Xenith57]

Hey OBP,

I've effectively adapted the code from https://sitestory.dk/excel_vba/calendar.htm

I've got:

Form: 'WFO'
Form: 'WorkForce'
Module: 'PublicVariables'
Class Module: 'clLabelClass'


WFO is the working form, WorkForce is the Main form that WFO is then placed in as a Sub Form


In the PublicVariables Module:

Public colLabelEvent As Collection
Public colLabels As Collection


In the WFO Form, I can't recall exactly where it's at, Open, or Load.. I'm not at work right now.


Dim ctl As Control
Dim InputLblEvt As clLabelClass


Set colLabelEvent = New Collection
Set colLabels = New Collection




For Each ctl In Form_WFO.Controls


If TypeOf ctl Is Access.ComboBox Then


Set InputLblEvt = New clLabelClass



Set InputLblEvt.InputLabel = ctl



colLabelEvent.Add InputLblEvt



colLabels.Add ctl, ctl.Name
End If
Next


in the Class Module:


Public WithEvents InputLabel As Access.Combobx
Private Sub InputLabel_AfterUpdate()




With InputLabel
msgbox ("It Worked")
End With

End Sub



Reason is similar to the calendar, there's just alot, and also, I'm learning as I'm going, so, I'd like to know for future projects.

Hope this helps clarify,

Thanks.

[OBP]

OK, hopefully this is just a naming convention issue.
When the WFO form becomes a subform it's VBA naming convention should become

Forms!WorkForce.WFO.Form.Controls

See this article on naming conventions
http://access.mvps.org/Access/forms/frm0031.htm

[Xenith57]

Yea, I discovered that in my searching too. Pretty sure I've tried it.

With that said though:
I took the code out of the WFO form and placed it in the Workforce form and execute it on open I've got a message box that pops up showing that it's running)
I changed the line from: for each ctl in WFO.controls to : for each ctl in Forms!Workforce.WFO.Form.controls

I then have it return the count in the collection, they're there.. But it still doesn't trigger the class. :(

[OBP]

If the code is in the mainform you should be able to use the

Me!WFO.Form.controls

Have you tried the msgbox without the "with inputlabel" to see if the module actually operates?
Because you may have to put something in front of the InputLabel to identify it.

[Xenith57]

Me!WFO.Form.controls also adds to the collection.

Sorry, in the class module it actually is just a msgbox, I added the with clause in error on here(was 6 am and from memory) heh.
I'm at work, and the afterupdate sub is indeed only a msgbox, no with statement.

[Xenith57]

As a side note, I've also just now added :

InputLblEvt.InputLabel.AfterUpdate = "[Event Procedure]"

After the: Set InputLblEvt.InputLabel = ctl
Line

To make sure each control has an event procedure, cause I know it won't trigger without it.

[PhilS]

The description of the issue does not indicate that as a likely source of the problem but nevertheless, make sure each combo box has its AfterUpdate property set to [Event Procedure].
You can even do that in code:

InputLblEvt.InputLabel.AfterUpdate = "[Event Procedure]"

Two general hints:
- The naming of the variables and their property is misleading/confusing.
- Your collection colLabels seems to be completely redundant.

[Xenith57]

Hey PhilS

Yes, I know it's misleadinf/confusing, it was just literally a copy paste of code, I could rename the items

The colLabels actually is redundant and was from the original code, I've since removed it

In regard to the event procedure, I did that exact thing I think while you were responding, heh Ty for the suggestion.

[PhilS]

In regard to the event procedure, I did that exact thing I think while you were responding,
Saw that only after posting. - Great! One more possible cause ruled out.

My next best guess: You moved the code around a bit, using copy&paste, I guess. - Is it possible that the line Set colLabelEvent = New Collection is executed twice in different contexts? That could overwrite the collection of the desired controls with an entirely different collection of controls.

In general I would avoid Global/Public variables whenever possible. From what I can see, it is definitely advisable in this case . - I would put the whole code into the subform itself (with the required adjustment to the form reference) and then make colLabelEvent a private member of the class module of the subform.


PS: I just remembered: I wrote a text on the subject of handling events from multiple sources (controls) (https://codekabinett.com/rdumps.php?Lang=2&targetDoc=vba-handling-events-indefinite-sources) some time ago. I don't think this contains the solution to this very problem, but nevertheless it might contain some valuable additional info.

[Xenith57]

I did a project level search for it, there's no duplication. I generally cut vs copy, or comment it out, but alas, no duplication.

I'll tinker with the private stuff.

Later this evening when I get home, I can create a small Access file and share it on my Google drive, demonstrating the issue if that would help.

At this point I'm starting to think Ms doesn't let you use class controls for subforms, lol

[OBP]

A Database example always helps.
It must be a "Trigger" problem, I must admit I have almost no experience of using Class modules, especially for individual Form controls.

[Xenith57]

I'll post one when I get home(y'all will probably be sleeping by then) ;) thanks for your help thus far, really appreciate it.

[Xenith57]

Well fellas....

I am beside myself...

I got home from work...

prepared a sample db with the code to get to you guys....

and with Me!WFO.Form.controls in the main form...

it works...


I am without words on how happy .. and upset I am... that it works...

I'm gonna have to really check my DB at work now..... until then... I'm going to have to chalk it up to the Ghost in the Machine....

Thank you folks again!


I've uploaded the file anyways in case someone can benefit from it.

https://drive.google.com/open?id=1993Xs0bFmADKTV2fuEYIIUjSxc9c47X-