Solved

Trouble With WithEvents

Posted on 2011-09-14
23
372 Views
Last Modified: 2012-05-12
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
Comment
Question by:JustinW
  • 9
  • 9
  • 3
  • +2
23 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 36536985
you can try using object array
also I think this can help

http://support.microsoft.com/kb/137119
0
 
LVL 57
ID: 36537033
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
 
LVL 1

Author Comment

by:JustinW
ID: 36537041
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
 
LVL 57
ID: 36537090
<<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
 
LVL 1

Author Comment

by:JustinW
ID: 36537398
@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
 
LVL 75
ID: 36537704
"Well problem one is labels don't have any events."
?  Labels not attached to a control have Events.

mx
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 36537831
>> 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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 36537834
Sorry for any dup info - distracted by life before posting.
0
 
LVL 57
ID: 36538129
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 36538213
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
 
LVL 1

Author Comment

by:JustinW
ID: 36538255
@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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 36538274
Fix uploaded.  Apologies to any who'd just downloaded it and wondered why the clicks didn't look like clicks! :-s
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 36539053
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 36539063
(Aircode - hence weird naming convention - but should still work ;-)
0
 
LVL 1

Author Comment

by:JustinW
ID: 36539248
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
 
LVL 1

Author Comment

by:JustinW
ID: 36539262
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
 
LVL 1

Author Comment

by:JustinW
ID: 36539670
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 36539776
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
 
LVL 1

Author Comment

by:JustinW
ID: 36540425
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 36541343
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
 
LVL 1

Author Comment

by:JustinW
ID: 36542062
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
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 36542143
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
 
LVL 1

Author Closing Comment

by:JustinW
ID: 36542753
Perfect!
Thanks for the time and effort!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now