MS Access global event handler

I think the problem may be that you are declaring TextboxGroup as an Access.Textbox, but you are setting it with an array of Textboxes.  Maybe some other expert can get this working, but I think you may need to make a separate event for each textbox, though of course you can call the same procedure from each control, perhaps using Me.ActiveControl.Name as an argument.

Hi Helen,
I've come across your stuff for Outlook VBA -- and used it.  Thank you!
I let Intellisense pick the declaration as just 'Textbox'--and it didn't work--so I tried forcing the declaration with Access.Textbox.  Still doesn't work.  The other option for 'textbox' from the object browser is 'MSForms.Textbox'  No go, either.

I could create 336 separate
Private Sub txtBookingSomeDudeX_DblClick(Cancel As Integer)
call MyEventproc(me.ActiveControl.Name)
End Sub

but that is EXACTLY what I am trying to avoid.
http://j-walk.com/ss/excel/tips/tip44.htm shows that it can be done for Excel.
http://www.vbaexpress.com/forum/showthread.php?t=13482 suggests that it can be done for Word--but then includes no code.
http://www.programmersheaven.com/mb/vba/323244/323244/creating-an-event-handler/ got it working for Word as well, but then posted no working code.

Thanks.
Nick67

OK you should be able to do something like the attached. I haven't tested this out, but had it from some old notes.
JimD.

' with a class of TextBoxEvents
Private WithEvents mTextBox as TextBox 
 
Public Property Set TextBox(ByVal objTextBox as TextBox) 
    mTextBox = objTextBox 
    mTextBox.OnDblClick = "[Event Procedure]"
End Property 
 
Private Sub mTextBox_DblClick(Cancel As Integer) 
    Msg "DBL Click Handled"
End Sub 

' and now in your form
Private mCollectionOfTextBoxes As Collection 
 
Private Sub Form_Init() 
 
    Set mCollectionOfTextBoxes = New Collection 
 
    Dim objControl As Control 
    For Each objControl In Me.Controls 
       If TypeName(objControl) = "TextBox" Then 
            Dim objTextEventControl As TextBoxEvents 
            Set objTextEventControl = New TextBoxEvents 
            Set objTextEventControl.TextBox = objControl 
            mCollectionOfTextBoxes.Add oEventHandler 
        End If 
     Next objControl 

Select allOpen in new window

<<Don't think you really need a Class module for this ... just a Function in a standard module, then call the Function directly the property sheet of each of the text boxes Double Click (or whatever) event.>>
  That is the alternative to a class module and if that's the road he wants to take, I have code here to loop through the controls on the form and set the event property as required.
JimD.

Understood.  But in the course of 5 seconds, I can see all 336 (WOW) text boxes, then paste in the function name in the event line.

On the other hand, the Class idea should be able to be made workable. I have a custom resizable Zoom box that uses a class module can be called in various ways from a text or combo box on a form.  Pretty sure there are similarities here.  If I have time, I will look into this.

mx

"I can see all " >>> I can Select all ....

mx

<<"I can see all " >>> I can Select all ....>>
 Sometimes not easily<g>; that's why I've got the code.  Or I should say, that I modify a lot of things in forms and I've found it easier for the most part to do it via code.
JimD.

Agreed that the code approach is more .. elegant.

mx

<<.. elegant.>>
  LOL You haven't see the code yet ;)
JimD.

Thanks All!
@JDettman:
Your code goes bang at oEventHandler  because it's not defined in the code snippet.
@DatabaseMX
The controls value is going to play into what the event does, so a bare function won't be quite as nice as I'll need to snag ActiveControl or something.  The Class and array approach does get the control name into the class module

"The controls value is going to play into what the event does, so a bare function won't be quite as nice as I'll need to snag ActiveControl or something"

ok ... so, are you saying that you need to know which control is calling the function? (my guess) ...

mx

If so, you can do this - as sort of suggested by Helen:


Public Function mYourSpecialDblClickFx(ctl As Control) As Boolean

    MsgBox ctl.Name

End Function

each text box double click event on property sheet (per my image):

=mYourSpecialDblClickFx([Screen].[activecontrol])

mx

@DatabaseMX
You'll get half the points because you have presented something that meets what I wanted to accomplish ie Have 336 controls double-click events handled without filling the form module with 336 separate events.

I selected all 336 and set OnDblClick to '=HowToHandleTheDoubleClick()'
and put the following in a module

Public Function HowToHandleTheDoubleClick()
MsgBox Screen.ActiveControl.Name & " dblClick"
End Function

and I get what I need.  I'd still like to get a class-based solution posted too, because I google A LOT for 'ms access global event handler' and came up dry -- so it would be good to get a complete solution posted up for posterity

Nick67

<<Your code goes bang at oEventHandler  because it's not defined in the code snippet.>>
  That should be objTextEventControl  not oEventHandler.
JimD.

We should be able to get the class solution working ... if you want to hang on a bit.  I'm always interested in class modules, as I have several that get used frequently.

mx

In case anyone is wondering what's with all the controls, I need a form that's going to graphically display a dozen guys appointments spread across the work week.  Time from Sunday to Saturday running left to right, guys running down the form.  It's going on a big 1080p display that can be seen by the guys booking the calls so they can see at a glance who has how much booked vs free time at a glance.

The controls will get dynamically placed on the form according to guy, length of booking, and start time at load--but if they want to bring up detail on the booking, they'll just double-click the appropriate control.

What about the girls ???

@JDettman
Changed that.  It compiles now--but still no events.

Let me try it here.
JimD.

Oilfield Non-destructive testing personnel.
No girls.
None have ever applied for a postion.

Anybody have an idea for a more elegant way to handle something like that.  I've had the Logon hours type graphic/control in my brain for a long time--but how to implement it in Access?
logon.png

For posterity,
If anyone is wondering how I got 336 controls on a form, nicely named to that -- when I go to populate the form I can use some nice loop logic ( x number of employees, y number of bookings),  CreateControl rocks.
Form1 is open in design view.  The following code is in a public module and run from there.  It creates
12 x 28 controls 0.5" wide by 0.7" in a grid on the form;

Public Function AddBookingTextBox()
Dim x As Integer
Dim y As Integer
Dim ctl As Control
Dim myleft
Dim mytop
Dim mywidth
Dim myHeight
Dim myformname As String

myformname = "Form1"


For y = 1 To 12
    For x = 1 To 28
    
    myleft = 0.5 * 1440 * (x - 1)
    mytop = 0.7 * 1440 * (y - 1)
    mywidth = 0.5 * 1440
    myHeight = 0.7 * 1440
    Set ctl = CreateControl(myformname, acTextBox, acDetail, , , myleft, mytop, mywidth, myHeight)
    ctl.Name = "txtBookingEmployee" & y & "Box" & x
    Next x
Next y
End Function

Select allOpen in new window

<<Anybody have an idea for a more elegant way to handle something like that.  I've had the Logon hours type graphic/control in my brain for a long time--but how to implement it in Access?>>
 Ask another question please.
JimD.

@JDettman
<<Anybody have an idea for a more elegant way to handle something like that.  I've had the Logon hours type graphic/control in my brain for a long time--but how to implement it in Access?>>

That's just background musing.  Sometimes a person wonders "why the heck are they doing someting like this"  I haven't googled across anything more elegant than what I am now pursuing.  i am just wondering if someone wouldn't say "Hey dumbarse..." when they understand the endgoal of my question is.

@JDettman
There is no Form_Init event in Access.  Open, Load, Activate, yes.  Init. No.
Putting the code in the open or load events changes nothing though.

Nick67

Ok, I got it to work.  I went back to http://j-walk.com/ss/excel/tips/tip44.htm and created an Excel workbook exactly as described there -- and the code worked -- for Excel.
Next, in Access I created clsHandledControls and copied in the code.  I also ADDED what I wanted to handle, which was textboxes:

Option Compare Database
Public WithEvents ButtonGroup As CommandButton
Public WithEvents TextBoxGroup As TextBox

Private Sub ButtonGroup_Click()
    MsgBox "Hello from " & ButtonGroup.Name
End Sub

Private Sub TextBoxGroup_DblClick(Cancel As Integer)
    MsgBox "Hello from " & TextBoxGroup.Name
End Sub

I built a new test form, threw on some cmdButtons and some Textboxes and put in this code in its module:

Option Compare Database
Option Explicit
Dim Buttons() As New clsHandledControls
Dim Textboxes() As New clsHandledControls
Private Sub Form_Open(Cancel As Integer)

    Dim ButtonCount As Integer
    Dim ctl As Control
'   Create the Button objects
    ButtonCount = 0
    For Each ctl In Me.Detail.Controls
        If TypeName(ctl) = "CommandButton" Then
            If ctl.Name <> "OKButton" Then 'Skip the OKButton
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons(1 To ButtonCount)
               Set Buttons(ButtonCount).ButtonGroup = ctl
            End If
        End If
    Next ctl


    Dim TextboxCount As Integer
'   Create the Button objects
    TextboxCount = 0
    For Each ctl In Me.Detail.Controls
        If TypeName(ctl) = "Textbox" Then
            If ctl.Name <> "OKButton" Then 'Skip the OKButton
                TextboxCount = TextboxCount + 1
                ReDim Preserve Textboxes(1 To TextboxCount)
               Set Textboxes(TextboxCount).TextBoxGroup = ctl
            End If
        End If
    Next ctl

End Sub

The critical part as JDettman had noted, is that Access needs to see [Event Procedure] in the OnWhatever property for the desired control.  So I set that for the commandButtons (onClick) and Textboxes (onDblClick) and Voila!  it works.

It's annoying that Access doesn't have control arrays -- and that unbound controls in continuous form detail sections can't be individually manipulated.  With the CreateControl code I posted, and now this, and some good VBA code, I can come pretty close to emulating them on an unbound form without too much pain or scads of repetitive code, which is legible and elegant.

Thanks All!

For any who google this afterward, follow the discussion to it's end.  DatabaseMX's solution works without the need for a class--and his directions for adding something en masse to the onDblClick property are integral to the class solution. JDettman's insight that the onDblClick property would need to be set to '[Event Procedure]' is the other critical part.

Thanks again

Interesting that you got the array to work.  I played around a bit yesterday with the collection approach, but could not get it to work.  I'm sure it's something I'm doing wrong as I know others have used that approach.
I haven't used either (Collections or classes) in VBA, as I use the old time approaches (same as what MX posted), which in effect gives you the same capabilites as using a class for the most part without all the hassel.
I will try and play with the collection approach a bit more today, just because I wanted to learn something new, but with what I see in Access 2007 and 2010, I'm wondering how much longer I'll stick with Access anyway.
JimD.

Yeah, I've stayed on Access 2003.  The ribbon and Nav Pain are really productivity killers for a professional dev.  The introduction of the spilt view destroyed a lot of my really complex reports.  I had a report with seven sub-reports, not all of which had data at the same time.  I then resized them down to nothing and shrank up the detail section to empty the whitespace.  Doing so bombed Access. SRX080217600401 was the case number.  Wasn't going to be fixed in Access 2007.  Period.

Dunno about 2010.  Haven't played with it much yet.  I had a lot of unhappiness with dev'ing in both 2003 and 2007.  Form corruption mainly.  If the references to Microsoft Office 12.0 Object Library would NOT update, or degrade gracefully to 11.0, I might have gone ahead--but they don't.  And while you can detect a busted reference with VBA, you can't actually code a fix.  Looks like you should be able to, but you just can't.  So that meant every time I touched an app with A2007 I had to re-open it with A2003, wait for the code to error out, open the IDE, fix the reference, compile and save.  Pain.

And then there's the Nav Pain.  110 forms.  72 reports.  Never mind queries. One long list.  Nah.
I also do a lot of right-click|properties on a control, get into the event procedure, adjust it, mess with the object, and if it's all good, save it.

In A2007 you can't get the properties window in Form View, and switching into Form View does an automatic save.  Nah.

SaveToPDF. Good.  Rapid form creation.  Good.  Sharepoint--who cares.  The cloud?  What kind of sense does an Access front end and SSEE back-end make in cloud terms?  What kind of sense does the cloud make in general?  I just bought a server with SBS 2008 R2 and 20 CALs and 2TB of storage for $4300.  We have a 1Gb network.  Why the HELL would I put all my data out in the cloud with a 2 Mbps ADSL connection between me and it?  Never mind risk issues.  The cloud is a niche--not the future.

That's my two-bits anyway.

Thanks,
Nick67

"The ribbon and Nav Pain are really productivity killers for a professional dev"
A super giant big nightmare I will be facing with the existing 25 apps currently ... when IT deploys O2010 later this year.  Yes ... productivity KILLER !!!

Glad you got this to work. RE ", as I use the old time approaches (same as what MX posted),"
Well, IF ... I was facing 336 controls, I would most likely go the Class and/or Collections approach, which I use often.  Sorry I didn't have more time to dig into this yesterday.  I just deployed a new database Tue that currently has 45-50 simultaneous users over our killer WAN.  So, I've been busy working with my analysts who are create reports (really queries) for sups and mgrs.

mx

No Problem.
I feel your pain.  I have a lot of forms that were sized to take full advantage of screen real estate.  The ribbon takes up MORE real estate--which isn't too happy--but I've been stalling long enough to get all the small monitors (1024 x 768) out of the operation.
Why we can't have the database window back is beyond me.
A2010 does redo the Quick Launch bar to make it more useful, and the ribbon is much more customizable.  BackStage is good too.  The way Compact And Repair was hidden in the Orb as inane.
But the Nav Pain -- well that's just pain.  The Windows Explorer motif has been around a long time--because it was good.
"Progress" doesn't move forward sometimes :(

The other MONSTROUS annoyance, if you haven't run into it yet, is that A2007 had a default new property of FilterOnLoad set to yes.  Standard A2003 practice, if you've filtered and messed something up, close and re-open.  A2007--not so much.  The filters survive an open and close--and get reapplied as a default.  Watch for that!  I have considered coding for a global OnClose routine that sets Filter ="" because of this.

My one A2007 user has the pain of de-filtering every object that had been filtered before I push out changes to the front-end.  Highly annoying.  fontunately, she's bright and knows now that if the [RecordCount] control I have on most forms isn't  a very large and familiar number that she has to toggle the filter.

Nick

<<The ribbon and Nav Pain are really productivity killers for a professional dev.  >>
  Ditto here.  Outside of the obvious issues, it pains me that the way to deal with the ribbion is with XML, although I believe that's changed in 2010. And so far, every user that I've had use Office 2007 finds the ribbon very non-intuitive.
<<Yeah, I've stayed on Access 2003. >>
  I've actually stalled on A2000 as 2003 had a number of burps in it that made life very difficult for me.  Most revolved around the new printer object.  I had so may porting issues when I tried it, I gave up.
  For a while I tried Visual Fox Pro, and loved it.  EVERYTHING is built on class and it was full class implementation, not the half baked one in VBA/VB.  of course VFP had it's flaws too, but overall a pretty darn good development tool.  It was even possible to do true 3-tier designs with it, which you can't do with Access.
 So after spending almost two years learning it and spending a couple of thousand on frameworks and what not, Microsoft went ahead and annoced that it was "dead".   Now that doesn't mean that every Foxpro written app would simply stop working the next day, but try explaining to a client that you want to develop a new app using  a language that is now officaly dead; it doesn't fly.
  Casting around, I don't see a whole lot of alternatives to VS, so I guess I'm going to have to bite the bullet and go that route sooner rather then latter.
<<SaveToPDF. Good.  Rapid form creation.  Good.  Sharepoint--who cares.  The cloud? >>
  Ditto on all that.  Microsoft continues to make way to much hay with Sharepoint.  It's a fantastion workflow tool, but that's what it is, a workflow tool.    You can't use it for everything and anything.   Wang tried to do that with their workflow tool and went no where fast.  Will be interesting to see if Microsoft endsup in the same boat.
  And yes, the cloud is way pre-mature.  There are so many issues with it, but the main one is security.  Everytime I think of the cloud, I think of gmail; everyone loves it, but doesn't really consider the fact that Google has a copy of every e-mail they send and receive; yikes.  Gives me the shutters.   Store documents there with Google Apps, like taxes, letter to a lawyer, or other things of personal nature?  You've got to be kidding.
  The cloud concept has a long way to go.
Mx,
  <<Glad you got this to work. RE ", as I use the old time approaches (same as what MX posted),"
Well, IF ... I was facing 336 controls, I would most likely go the Class and/or Collections approach, which I use often.>>
  Why bother?  Functionally you get the same thing, with a lot less complexity and as you yourself point out, changing 336 controls is really no different then changing 1.  Might take an extra second or two at most.
    Now if they had full inheritance I would sing a different tune, but the way VBA is now,  classes in VBA are cumbersome at best.
JimD.

"although I believe that's changed in 2010."

Really.  Do you know to what?
mx

<<Really. Do you know to what?>>
I thought I had read where they had provided a simpler method for manipulating it, but that was early in the beta and although I downloaded it, I never did install it.
Haven't seen anything on it since, so I may be confusing it with something else that was introduced.
JimD.

It's not that underpinings aren't still XML.  It's that they added a decent GUI to creating a custom ribbon tab and permit UTTER re-arrangement of everything.  See this for a shot of ribbon with the custom tab being the second tab (after file, which I think can't and shouldn't be displaced)
http://www.ditii.com/2009/11/11/ribbon-customization-with-office-2010/

This article talks about cutomizing the ribbon
http://www.groovypost.com/howto/microsoft/customize-the-office-2010-ribbon-word-excel-outlook/

Now that I have got the solution working, no I wouldn't go back to just setting the Onwhatever event = MyCustomFunction.  This is now highly elegant and compact.  The code that loop through and adds the control to the class also sets the Value, ControlTextTip, Tag, Left and Width.  The class handles the events--and because it's a class, I can create multiple instances, which turned out VERY nicely.

I grew the textboxes with the inspectors' names the full width of the form and added them to one class instance.  Now, clicking one open space prompts whether you want to create a new job in the time space, or assign that person to any of the jobs that exist and span that point in time.

The other boxes, which represent booked jobs, respond to double-click as intended--but I should also be able to simulate drag-and-drop for re-assigning them to different personnel /timeframes.  The end-users would LOVE that--and I should be able to do that in a compact and extensible fashion.

I LIKE it.  A couple of DIm declarations and some loops in the form module, and 60-80 lines in a class module.  Nice and compact--and because the class picks up all the properties of the controls added to it, the references in the class module are quite simple.

Thanks for the Ribbon links ... checking them out now.

"and that unbound controls in continuous form detail sections can't be individually manipulated."
Well, to a very small degree than can be with Conditional Formatting, but that's about it.

mx