• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Trouble With WithEvents

In access, I have a form where I'm trying to assign the same
method to a bunch of different Labels (acting as buttons);
without creating event code for each label. I'm trying to do
this by putting Event Code inside of a class module, and assigning
a control to the new object.

Below is what I've cobbled together just to see if I could
get 1 label to fire code when clicked.

To save my life, I cannot get this to work.
Thanks:

'Form_Assets Form Module
Public cb as cButton

Private Sub Form_Load()
set cb = New cButton
set cb.lbBUTTON = Me.lblPARCELS_NEW

End Sub

Open in new window



'cBUTTON Class Module
Public WithEvents lbBUTTON As Label

Private Sub lbBUTTON_Click()
    MsgBox "WORK STUPID!!!"
End Sub

Open in new window


0
JustinW
Asked:
JustinW
  • 9
  • 9
  • 3
  • +2
1 Solution
 
FarWestCommented:
you can try using object array
also I think this can help

http://support.microsoft.com/kb/137119
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Well problem one is labels don't have any events.

 Problem two is that Access has an optimization built-in that if it doesn't see '[Event Procedure]' for an event property, it doesn't raise an event for a control.  So you need to set that.  You can do this in the class Set:


Public Property Set TextBoxGroup(txt as Textbox)

    Set TextBoxGroup = txt
    TextBoxGroup.OnClick = "[Event Procedure]"

End Sub

Jim.
0
 
JustinWAuthor Commented:
I figured I'd store in a collection.

Is there any reason why my code doesn't work
when I click on the label?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I figured I'd store in a collection.>>

You might want to look at this thread:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_26250569.html

Jim.
0
 
JustinWAuthor Commented:
@JDettman;

when I do this:
Public Property Set TextBoxGroup(txt as Textbox)

    Set TextBoxGroup = txt
    TextBoxGroup.OnDblClick = "[Event Procedure]"

End Sub 

Open in new window


Seems to form a recursive loop, that blows up on stack space
when I do this:

dim tbg as TbGroup
set tbg = New TbGroup

set tbg.TextBoxGroup = User_Form.Textbox1

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Well problem one is labels don't have any events."
?  Labels not attached to a control have Events.

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
>> Well problem one is labels don't have any events
That's an occcasional problem.  Stand alone labels do indeed have their own events.  Labels "attached" to another control (i.e. with a parent control - such as a textbox or a command button) are relieved of their events and adopt the parent controls actions.

I've an example of using Label controls as command buttons (i.e. sinking the click event) in the example found in the page linked to in my profile (which I might as well copy here).

However... "User_Form.Textbox1"
You're talking about doing this on UserForms?  (The example cited is for Access forms only).
0
 
Leigh PurvisDatabase DeveloperCommented:
Sorry for any dup info - distracted by life before posting.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
mx/Leigh are correct; Labels in A2003 and above do have events, which I had totally forgotten.    I've been stuck in the stone ages for some time now (Access 2000 and prior do not).

As for the classes, Leigh's example is an excellent one of exactly what you are trying to do.  The only thing I would add is the addition of the "[event procedure]" to the OnClick property.  However again that may have been something that was changed.  I don't work with classes all that often in VBA.

Jim.
 
0
 
Leigh PurvisDatabase DeveloperCommented:
Assigning the Event Procedure to the event property is absolutely required still.  Either as a permanent property in design view, or at runtime in the code.  No change there.
And still something I also wholeheartedly endorse as doing in the class assigning code to ensure that the event is raised.
<aside>
The example of mine used to demo only Clicking.  I've obviously failed to include it again when I added the "hover" functionality a while back.  Thanks for noticing that Jim, I'll repost it shortly (as several of the "buttons" are now doing nothing when clicked as it used to be handled in the class! :-s).
</aside>

I still have a note of concern over the line refering to:
User_Form.Textbox1
It's possible that User_Form is just the name of another class object you've created - but somehow I doubt that. :-s
0
 
JustinWAuthor Commented:
@LPurvis, New to Access, sorry; the Form Class Module is named
"Form_Assets".

so if I wanted to do this for 1 test label, I would do this?:

New Class Module named cBUTTON
Public WithEvents lbl as Label

Private Sub lbl_Click()
      MsgBox "YOU CLICKED " & lbl.name
End Sub

Open in new window


and inside of "Form_Assets"
Private Sub Form_Load
public cB as cBUTTON
set cb = New cBUTTON
set cb.lbl = Me.lblASSETS_NEW
End Sub

Open in new window


or did I miss something?
0
 
Leigh PurvisDatabase DeveloperCommented:
Fix uploaded.  Apologies to any who'd just downloaded it and wondered why the clicks didn't look like clicks! :-s
0
 
Leigh PurvisDatabase DeveloperCommented:
I presume you have an [Event Procedure] property already assigned for the label's click event?
If you then you need to assign it, making your class something like

Public WithEvents mlbl As Access.Label

Public Property Set lbl(objTextBox As Access.Label)
    Set mlbl = objTextBox 
    mlbl.OnClick = "[Event Procedure]"
End Property 

Public Property Get lbl() As Access.Label
    Set lbl = mlbl
End Property 

Private Sub mlbl_Click()
      MsgBox "YOU CLICKED " & mlbl.name
End Sub

Open in new window


Bear in mind that if the label is attached to another control - then its own events aren't raised.
0
 
Leigh PurvisDatabase DeveloperCommented:
(Aircode - hence weird naming convention - but should still work ;-)
0
 
JustinWAuthor Commented:
I feel dumb,
when you do mlbl.OnClick = "[Event Procedure]"

can you call a private method in the class, say to do:

msgbox mlbl.Name?
0
 
JustinWAuthor Commented:
For whatever reason, when I click on the label, with this bit of code omitted; nothing fires.
If I leave it in, like this:

mlbl.OnClick = "[Work]"

Access says:

Microsoft Office Access can't find the object 'work',
even though I have a subroutine named work
0
 
JustinWAuthor Commented:
This is killing me too.
All of my labels and methods are so standardized that this
works like a charm; its just really ugly : (

Sub h8()
    Dim ctrl As Control
    Dim col As Collection
    
    Dim sFORM As String
    Dim sPROC As String
    
    Set col = New Collection
    
    
    For Each ctrl In Form_Assets.Controls
        If TypeName(ctrl) = "Label" And Left$(ctrl.Name, 2) = "lb" Then
            col.Add ctrl.Name, ctrl.Name
        End If
    Next ctrl
    
    
    For Each Key In col
        sFORM = mRegExp.rxFIND(Key, "[A-Z].*?(?=_)", False)(1)
        sPROC = mRegExp.rxFIND(Key, "[A-Za-z]+$", False)(1)
        Debug.Print "Private Sub " & Key & "_Click()"
        Debug.Print "dForms(" & sQUOT & sFORM & sQUOT & ")." & sPROC & "_"
        Debug.Print "end Sub"
    Next Key
End Sub

Open in new window

0
 
Leigh PurvisDatabase DeveloperCommented:
I've no idea where you're at now.
I don't see what you were trying to do with the [Work] reference?  What went wrong with the "[Event Procedure]" method?
You can't call a private method in the class module of the instantiated object, but then part of the purpose of the single object is to consolodate your functions.  (Do you have a similarly named function in each of several different forms?  If not then just move your function to the class or a standard module.

Cheers.
0
 
JustinWAuthor Commented:
So in the class module, if if have a method named ReturnName,
how do I call it from this:

Public WithEvents mlbl As Access.Label

Public Property Set lbl(objTextBox As Access.Label)
    Set mlbl = objTextBox 
    mlbl.OnClick = "[Event Procedure]"
End Property 

Public Property Get lbl() As Access.Label
    Set lbl = mlbl
End Property 

Private Sub mlbl_Click()
      MsgBox "YOU CLICKED " & mlbl.name
End Sub

PUBLIC SUB ReturnName()
   MsgBox "You Clicked " & mlbl.name
END SUB

Open in new window

0
 
Leigh PurvisDatabase DeveloperCommented:
Your example calling code was:
Private Sub Form_Load
    Dim cB as cBUTTON
    set cb = New cBUTTON
    set cb.lbl = Me.lblASSETS_NEW
End Sub

Open in new window


This would just become:
Private Sub Form_Load
    Dim cB as cBUTTON
    set cb = New cBUTTON
    set cb.lbl = Me.lblASSETS_NEW
    cb.ReturnName
End Sub

Open in new window

0
 
JustinWAuthor Commented:
Still doesn't fire on click.
Maybe if you can show me on the attached
mock up file, it would be easier for me to get my hands
around.
Code-Work.accdb
0
 
Leigh PurvisDatabase DeveloperCommented:
It was all there except the assigning of the OnClick event again. :-s
(And of course - you need to have enabled code in the application - or have it in a trusted location / as a trusted file - version depending.)

I realize that the adding of colour hovering in my command label example may make things less clear and actually obfuscate how simple this can be - but it is all in there. (It is again anyway :-)
Code-Works.accdb
0
 
JustinWAuthor Commented:
Perfect!
Thanks for the time and effort!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 9
  • 9
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now