Solved

Monitoring Controls Using WithEvents

Posted on 2011-02-22
28
492 Views
Last Modified: 2013-11-28
I want to monitor all textboxes on a form without creating event handlers for each one.
The method below works in Excel but I can't seem to get it to work in Access.

'clsFormControl
Public WithEvents ValueBox As Access.TextBox
Private Sub ValueBox_Click()
    MsgBox 1
End Sub

'Form_MyForm
Private ValueBoxes As Collection
Private Sub Form_Load()
    Dim FCT As clsFormControl
    Set ValueBoxes = New Collection
    Set FCT = New clsFormControl
    Set FCT.ValueBox = Me.MyTextbox
    ValueBoxes.Add RFC
    Set RFC = Nothing
End Sub

With this in place, clicking on MyTextbox should cause a messagebox to display.  But it doesn't.  Any thoughts?
For reference the form is bound to a query and is read only.  I don't think that matters, but you never know.

Mike
0
Comment
Question by:shacho
  • 14
  • 11
  • 3
28 Comments
 
LVL 75
ID: 34957279
Interesting idea.
Can you upload the db ... would like to check it out.

Which event on the text box are you trying to mimic ?  And where is that event being set ?

mx
0
 

Author Comment

by:shacho
ID: 34957287
Gimme a few minutes.  I'll create a DB for testing.

Mike
0
 
LVL 75
ID: 34957291
I will be back online circa 20:30 CA PDT
0
 

Author Comment

by:shacho
ID: 34957327
Here are two files: an Excel version that works, and an Access version that doesn't.

FormControls.xls
0
 

Author Comment

by:shacho
ID: 34957336
EE is not letting me upload the Access file.  Not sure why.  Gonna try a different browser.
0
 
LVL 75
ID: 34957337
Only see Excel attached ...

mx
0
 

Author Comment

by:shacho
ID: 34957349
Can't get zip or mdb to upload in any browser.  I've embeded a zipped mdb in this Excel file.
Access.xls
0
 

Author Comment

by:shacho
ID: 34957373
I'd settle for a workaround, but I can't think of any.  Basically I want an editor form I've created to open up when a user clicks inside any textbox.  Because the form is populated by a crosstab, values cannot be edited directly in the boxes.  An "EDIT" button on the form would do, but the problem is if you click the "EDIT" button, information about the item you want to edit is lost because the textbox loses focus (as EDIT takes it).

Mike
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34957520
Have you seen this article:

http://support.microsoft.com/kb/234907

It goes into detail regarding sinking events for Forms and such.

One thing many people miss is that you must set the event you wish to trap (for example, the Click event of your textbox) to [Event Procedure]. You don't have to add any code, but you do have to set that in order for the code to fire.

<I'd settle for a workaround, but I can't think of any>

I've used a form-level variable that is set everytime a control gets the Focus (i.e. the GotFocus event of each control). This does require you to enter code in each control, but you can do that in bulk. Once you have that value "set", your EDIT button just refers to that form-level variable as needed.
0
 

Author Comment

by:shacho
ID: 34957646
>This does require you to enter code in each control
I've used that approach in the past, but in this case there are over 100 controls, many on tabbed pages.  Until development is done the risk of missing some is great.

I just took a look through the page you sent the link for.  That's exactly what I needed.  I would never have guessed that setting a textbox's Change event property to [Event Procedure] would be needed to activate event notification, but I guess it makes sense.  Too cool.

Thanks!

Mike


0
 

Author Comment

by:shacho
ID: 34957695
Post script to future readers...
You can use this to make sure all of your textboxes are monitored.
While the form is in develop mode, run this code from a standard module, then save the form.
Private Sub AddEventProcs()
    Dim CNT As Access.Control, TBX As Access.TextBox
    For Each CNT In Form_MyForm.Controls
        If TypeName(CNT) = "TextBox" Then
            Set TBX = CNT
                TBX.OnChange = "[Event Procedure]"
            Set TBX = Nothing
        End If
    Next CNT
End Sub
0
 
LVL 84
ID: 34957840
I'm not sure how the whole event sink thing works behind the scenes, but the [Event Procedure] is the trigger that tells the UI that it's time fire the event. Once that happens, your sink code takes over.
0
 

Author Comment

by:shacho
ID: 34957887
Works like a charm.  All events are fired automatically for all controls in the MSForms package, i.e. all the other Office applications with VBA.  The fact that individual events are "properties" on Access forms should have tipped me off.  Thankfully I know know why [Event Procedure] appears when code is present.  On some occasions in the past I have manually coded an event procedure only to have it not fire because the UI didn't properly propagate the presence of the handler to the form's corresponding event "property".  Now I know how to fix that (add [Event Procedure]).  Before I had to go through the "Build Code" dialog.

Mike

0
 
LVL 75
ID: 34957925
"One thing many people miss is that you must set the event you wish to trap (for example, the Click event of your textbox) to [Event Procedure]. "

Which is basically what I ask @ http:#a34957279

mx
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75
ID: 34957956
I'm curious ... while this is all academically interesting, why ... would you want to do this on Access text boxes, which already have full set of Events ... as opposed to in a Class module or elsewhere (some app object model) where you need to create event handlers and/or raise events ?

mx
0
 

Author Comment

by:shacho
ID: 34958120
>One thing many people miss...
Indeed, that's what I was missing.

>would you want to do this on Access text boxes...
Not sure I understand your question.  Perhaps what I'm doing is not quite clear.
In a nutshell, the table is bound to a pivot query so edits cannot move from the form back to the source table.  My generic edit routine works like this:
1) A user double clicks any of 100+ text boxes on the form.
2) The event sink class sends a message back to the form's "Public Sub UserDblClicked(ControlName as String)" procedure.
3) The procedure instantiates a single field editor form that IS linked to the underlying datum in the master table.
4) The form requeries.
The benefit of doing it this way is that I have one and only one event handler in the event sink class, as opposed to pages and pages of event handlers for each of the 100+ textboxes, which would all individually have to invoke UserDblClicked.

Mike
0
 
LVL 75
ID: 34958170
Couldn't use just use Screen.ActiveControl to tell you which control was double clicked ... and still just have the one common routine ?

mx
0
 

Author Comment

by:shacho
ID: 34958366
Cool.  Never heard of the "Screen" object before.  That will surely come in handy in the future.
In this case the control name is actually not enough.  Many of the textboxes are on subforms so I also need to get a handle on the specific recordset it's associated with, and information about the state of other associated objects at the time it was double-clicked.  Information that cannot be referenced using the control alone.  I used UserDblClicked(ControlName as String) in the above example for convenience, but the bounce back handler actually sends back the sink class object itself, which contains all the references I need in its public properties.

Mike
0
 
LVL 75
ID: 34958611
" I used UserDblClicked(ControlName as String) "
Where exactly is that?

From Screen.ActiveControl - which is the Object, you can extract all the properties that apply to that Control type ... which you can easily determine.
And Screen.ActiveControl.Name gives you the Name of the control.

mx
0
 
LVL 75
ID: 34958643
" Many of the textboxes are on subforms so I also need to get a handle on the specific recordset it's associated with'

Screen.ActiveControl.Parent.Name will give you the name of the subform

Screen.ActiveControl.Parent.Form.RecordSource  will give you the value in the RecordSource property

and so on ...

mx
0
 

Author Comment

by:shacho
ID: 34958684
not if it's on a tab control. then parent is the tab control. and also not if the box is on the main form.  to iterate through all the possibilities of how to properly traverse each textbox object's hierarchy just to get at it's source table is more work than simply specifying it when activating monitoring.  i'll make a mock up tomorrow to demonstrate.
0
 
LVL 75
ID: 34958733
Text box tab control on subform:

Screen.ActiveControl.Parent.Parent.Parent.Name

gives the subform name still.

mx
0
 

Author Comment

by:shacho
ID: 34958890
i know. more on thursday.
0
 
LVL 84
ID: 34959804
<Couldn't use just use Screen.ActiveControl to tell you which control was double clicked ... and still just have the one common routine >

Maybe I'm thinking of this wrong, but how would you use Screen.ActiveControl to fire an event? That information is available in Access, but in this case, what would you do with it?

0
 
LVL 75
ID: 34962559
Well, I probably don't fully understand what Mike is trying to do.
0
 

Author Comment

by:shacho
ID: 34966434
OK.  Here is a db you can look at to get an idea of what I've done.  Open the form "MainForm" to test.  Don't pay attention to the structure or content - it's not a strict analog for the real project.  Just note that I can turn on monitoring and abstract away ALL of the variable details of the control that gets clicked (which form it's on, what recordset it belongs to, if it's on a tab control or not, etc.) with just a few lines of code.  No handlers for every box.  No interrogating the box at click time to figure out how many "Parents" I have to go through to get at it's recordset.  Clean and quick.  If I were to pass back only a message saying that the user "double-clicked something" and use Screen.ActiveControl to grab it, I would have to work out all those ugly variable details before I could do anything useful with the information.  Yes, it could be done, but it's less clean or inefficient (I think).

Cheers,

Mike



BoxMonitor.mdb
0
 
LVL 75
ID: 34967372
Well ... that's interesting for sure.
So ... why isn't the code in Module1 in the class module ?

And you have to manually put [Event Procedure] in each On Dbl Click event line for a Controls ?

mx
0
 

Author Comment

by:shacho
ID: 34968631
Funky, eh?

>So ... why isn't the code in Module1 in the class module ?
No reason.  This isn't how the real DB is built.  Just whipped this up in a hurry for the discussion.

>And you have to manually put [Event Procedure] in each On Dbl Click event line for a Controls ?
Not exactly.  Well, not "manually" that is...

Private Sub AddEventProcs()
    Dim CNT As Access.Control
    With Forms("MyForm")
        DoCmd.OpenForm .Name, acDesign
            .HasModule = True
            For Each CNT In .Controls
                If TypeName(CNT) = "TextBox" Then CNT.OnDblClick = "[Event Procedure]"
            Next CNT
        DoCmd.Close acForm, .Name, acSaveYes
    End With
End Sub
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

746 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

10 Experts available now in Live!

Get 1:1 Help Now