Monitoring Controls Using WithEvents

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
shachoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
shachoAuthor Commented:
Gimme a few minutes.  I'll create a DB for testing.

Mike
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I will be back online circa 20:30 CA PDT
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

shachoAuthor Commented:
Here are two files: an Excel version that works, and an Access version that doesn't.

FormControls.xls
0
shachoAuthor Commented:
EE is not letting me upload the Access file.  Not sure why.  Gonna try a different browser.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Only see Excel attached ...

mx
0
shachoAuthor Commented:
Can't get zip or mdb to upload in any browser.  I've embeded a zipped mdb in this Excel file.
Access.xls
0
shachoAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shachoAuthor Commented:
>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
shachoAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
shachoAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
shachoAuthor Commented:
>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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
shachoAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" 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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" 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
shachoAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Text box tab control on subform:

Screen.ActiveControl.Parent.Parent.Parent.Name

gives the subform name still.

mx
0
shachoAuthor Commented:
i know. more on thursday.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, I probably don't fully understand what Mike is trying to do.
0
shachoAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
shachoAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.