Assign class module to Form controls

[MChrup]

Hello,

I stumbled across the following problem.
I have a Form with 3 Labels on it, created within Designer Mode.
Let's call them Lbl1, Lbl2, Lbl3.

Additionally, I created a class module "clsLbls" which code is:

'>>>>
Option Compare Database
Option Explicit

Public WithEvents lblTask As Label

Private Sub lblTask_Click()
    lblTask.Caption = "qq"
    MsgBox lblTask.Name
End Sub

Going back to the Form.
I want the above class module to be assigned to Labels mentioned previously. Which I tried by:

Private clsLblClick(1 To 3) As New clsLbls

Private Sub Form_Load()
    Dim i As Integer
    Dim lblThis As Label
    'add class to labels for click event handling
    For i = 1 To 3
        Set lblThis = Me.Controls("Lbl" & i)
        Set clsLblClick(i).lblTask = lblThis
    Next i
End Sub

In result only first of three labels has On Click event or caption changed.
I tried the similar code in Excel and everything works like a charm (just changed Label to msForms.Label). Could you please give me an idea of how do I suppose to code it?

 

[isladogs]

I'm not clear what you are trying to do here but if you are trying to set label properties using a class module, it seems an unnecessarily complex approach.

Please explain the point of this code.

 

[MChrup]

Hi isladogs,

A mentioned code is just an example, not the actual purpose.

The Form I have has 10 labels displaying, value (TaskID) in a Gantt Chart style.
I want to capture the value of a clicked label to update the Form footer controls data source query, also change a caption in form footer label to the caption of the clicked label.
I could hard code every single label as they are not dynamically created, but that creates lines of code. I've done it previously in excel and thought something similar might be available in access.

Please see attached screenshot, comments in yellow.

Edit: currently only 4 labels are visible as working on dummy data.
I want to add class module aka event handler during label initialization on Form_Load.
Interesting thing is that it works for the first label, however nothing is happening to the other 9. Also if using the for loop and starting from the second label nothing is happening either. Why access masters, why?

 

[Uncle Gizmo]

Have a look at videos 6 and 7 on my website here, I think I covered the sort of thing you're on about.

http://www.niftyaccess.com/lock-unlock-controls/

Sent from my SM-G925F using Tapatalk

 

[MajP]

Class TaskLabel

Option Compare Database
Option Explicit

Private WithEvents mLabel As Access.Label
Private mName As String
Public Property Get Name() As String
  Name = mLabel.Name
End Property
Public Property Get TaskLabel() As Access.Label
  Set TaskLabel = mLabel
End Property
Public Property Set TaskLabel(TheLabel As Access.Label)
 ' On Error GoTo ErrHandler
  Set mLabel = TheLabel
  'You can add More Events and more controls here
   mLabel.OnClick = "[Event Procedure]"
  ' ....
  Exit Property
ErrHandler:
   If Not (Err.Number = 459 Or Err.Number = 91) Then
      MsgBox ("Error: " & Err.Number _
            & " " & Err.Description _
            & " " & Err.Source)
   End If
   Resume Next
End Property
Private Sub mLabel_Click()
  MsgBox mLabel.Caption & " " & Me.Name
End Sub

Class TaskLabels

Option Compare Database
Option Explicit

'Class Module Name: TaskLabels
'Developed by: MajP
'
'Purpose: This Class Module is the collection class for the object class "TaskLabels"
'The collection allows you to build a pseudo control array that will react to one or more events.

'************************ Class Code Start ****************************************************

Private mTaskLabels As New Collection
Public Function Add(TheLabel As Access.Control, ctlName As String) As TaskLabel
   Dim newTaskLabel As TaskLabel
   Set newTaskLabel = New TaskLabel
   Set newTaskLabel.TaskLabel = TheLabel
   mTaskLabels.Add newTaskLabel, ctlName
   Set Add = newTaskLabel
End Function
Public Property Get count() As Integer
   count = mTaskLabels.count
End Property
Public Property Get Item(ByVal index As Variant) As TaskLabel
   Set Item = mTaskLabels(index)
End Property
Public Sub Remove(index As Variant)
   mTaskLabels.Remove (index)
End Sub
Public Sub Remove_ByName(ByVal TheName As String)
   Dim i As Integer
   For i = 1 To mTaskLabels.count
     If mTaskLabels(i).Name = TheName Then
        Remove (i)
        Exit Sub
     End If
   Next
End Sub

Private Sub Class_Terminate()
 Set mTaskLabels = Nothing
End Sub
Public Sub Clear()
    Set mTaskLabels = New Collection
End Sub

Public Property Get Item_ByName(ByVal TheName As String) As TaskLabel
   Dim Tasklbl As TaskLabel
   For Each Tasklbl In mTaskLabels
     If Tasklbl.Name = TheName Then
       Set Item_ByName = Tasklbl
     End If
   Next
End Property

Demo

Dim MyTasks As New TaskLabels

Private Sub cmdDemo_Click()
  'Demo by index
  MsgBox MyTasks.Item(1).Name
  'Demo by name. Note did not add a caption property to TaskLabels
  MsgBox MyTasks.Item_ByName("Label7").TaskLabel.Caption
  'Demo Remove
  MsgBox "Count " & MyTasks.count
  MsgBox "Last " & MyTasks.Item(1).Name
  MyTasks.Remove (MyTasks.count)
  MsgBox "Count " & MyTasks.count
  'Demo Remove by Name
  MyTasks.Remove_ByName ("label19")
  MsgBox "Count " & MyTasks.count
End Sub

Private Sub Form_Load()
  Dim ctl As Access.Control
  For Each ctl In Me.Controls
     If ctl.Tag = "T" Then
        MyTasks.Add ctl, ctl.Name
      End If
  Next ctl
End Sub

 

[MChrup]

Hi MajP,

I tried your solution.
Added mLabel.Caption = "qq" in Set TaskLabel under TaskLabel, to make sure it is running through it. And yes it is changing all Labels caption to qq but not assiging the _Click event.

What is the purpose of cmdDemo_Click?
As I can see from the code you still calling controls by Names or Index in the collection, so again it needs to be hardcoded for every single button on the Form.

Why is it so different from Excel?
In Excel I can create UserForm with 3 Labels on it (Lbl1 to 3), then create a Class module for a _Click event and assign this class to the labels on Form_Load.
Class module would contain a procedure I want to run when any of the labels been clicked.

I am kinda giving up on this one.
The only solution is to add a sub and then point all on click event for every single label into that sub. :/

 

[Gasman]

Worked for me, when I clicked the label?

 

[Gasman]

PMJI MajP,

How would one add the event for DblClick please.?

Private Sub mLabel_DblClick(Cancel As Integer)
MsgBox mLabel.Caption & " " & Me.Name & " double clicked"
End Sub

I've added that code to the class TaskLabel and then tried to insert

mLabel.OnClick = "[Event Procedure]"
mLabel_DblClick = "[Event Procedure]"

but it complains 'Argument not optional', so I then change to

mLabel.OnClick = "[Event Procedure]"
mLabel_DblClick(Cancel As Integer) = "[Event Procedure]"

I then get 'Expected list seperator or )' error

TIA

 

[MajP]

I tried your solution.
Added mLabel.Caption = "qq" in Set TaskLabel under TaskLabel, to make sure it is running through it. And yes it is changing all Labels caption to qq but not assiging the _Click event.

What is the purpose of cmdDemo_Click?
As I can see from the code you still calling controls by Names or Index in the collection, so again it needs to be hardcoded for every single button on the Form.

You are totally missing the point of a Class module. The class model can capture all events. The click event is assigned in the class module and captured in the class module.

Private Sub mLabel_Click()
  MsgBox mLabel.Caption & " " & Me.Name
End Sub

The purpose of the demo is that I can put 250 (in my demo) labels on a form and load them into my collection. That is what happens on the onload event. I can click on any of them and fire the click event. The demo then shows if you have a collection you can still add a label or remove a label from your custom collection like any other collection. Basically I am showing you how to simulate a control array like in Excel for VBA.

 

[MajP]

@Gasman

How would one add the event for DblClick please.?

This piece of code is very interesting, in understanding how event handlers work

mLabel.OnClick = "[Event Procedure]"

When you are designing a form in Access and you go to an event property you can choose

[Event Procedure]
[Embedded Macro]
=SomeFunction()

For example the onclick property, It is telling access when you physically click on a control to "announce" the event. The property is assigning something to listen to the event. So [event procedure] is saying to announce the event and that a vba event procedure is listening. If there is nothing in the property the event is never announced. You can test this yourself. Build an event on a form, and run it. Then go to the form and delete "[event Procedure]". Your code is still there, but nothing happens. So even though you have code listening for an event to take place, you no longer tell it to announce the event.

So first do
'You can add More Events and more controls here

mLabel.OnClick = "[Event Procedure]"
mLabel.OnDblClick = "[Event Procedure]" 'Tells the control to announce the event

Now add in the event. In the class you can actually choose from the pulldowns.

Private Sub mLabel_Click()
  MsgBox mLabel.Caption & " " & Me.Name
End Sub

Private Sub mLabel_DblClick(Cancel As Integer)
  MsgBox mLabel.Caption & " Has been double clicked"  'Handles the event
End Sub

However, that will likely not work if you have both a click event and double click event. For a label the click event will happen when you try to double click and the double click will be blocked. So you would need to pick one or the other.

 

[Gasman]

Thank you MajP,
I commented out the OnClick for now and have got the double click to work.

 

[MajP]

I commented out the OnClick for now and have got the double click to work

This example with a label is not very exciting since a label has so few events. But if you do this with a grid of textboxes (like in a home made calendar control) it is more instructive because you can have many textboxes and many events and you can assign all the events to the class. So instead of many hard code event procedures you have one in the class for each event.

 

[Gasman]

Yes, I realise that. I have not touched classes at all, and it is unlikely I will now, but I still wanted to get a smattering on how it all went together, which I think I have now got from your example.

 

[MChrup]

Hi MajP,

I finally had some time to play with the code.
You were right, it is working, exactly as I wanted to.
I set a new Form with 3 Labels on it and no issues.
Now I just need to figure out why is it not working on my original Form.

Thanks a lot.

 

[MajP]

FYI. Some of this can be accomplished by using a common Function instead of a class module. If you write a function instead of an event procedure, you can have the function handle many events from different controls.
For example make a public event

Public Function HelloWorld()
  msgBox "Hello World"
End Function

Then highlight as many controls as you want, and in an event such as onclick type

=HelloWorld()

They will all produce that message on click.

 

[Cronk]

Indeed, in fact using screen.activecontrol.name (or .tag), different action could be handled in the same function, depending on which control had been clicked.

 

[Uncle Gizmo]

Over the years I have built, and improved a particular Class Module to handle both the screen active control and the active form.

Incidentally, a particular control may not be on the active form it may be on a subform within the active form. My class module enables you to grab this form (Sub Form) as well! It's here on my website:- CallCalled Class Module

 

[Gasman]

Page says 'Product not found'?
Same for Pop Up Forms ?

 

[Uncle Gizmo]

Page says 'Product not found'?
Same for Pop Up Forms ?

Ah yes! I haven't got around to adding a sample database yet. However the code is at the bottom, you just paste it straight into a Class Module.

The sample database is HERE:-
https://access-programmers.co.uk/forums/showpost.php?p=1591952&postcount=6

 

[Uncle Gizmo]

The sample database is HERE:-
https://access-programmers.co.uk/forums/showpost.php?p=1591952&postcount=6

Please note the sample database on the access world forums link uses a slightly older version of a class module.