Introduction.

This is about running a User-Defined Function (say myFunction(Parameter)) on the Mouse Move Event Property of Text Boxes.  When the Mouse Move Event occurs the Text-Box name must be passed as Parameter to the function dynamically.

This question was asked in an Access User's Forum (www.accessforums.net), in the Forms Category of Posts, by a member, seeking suggestions for a solution.  A demo database was posted by me there, twice on page-5, but the last one is the final version. 

I thought it will be useful to my readers and presented here for you, with details of this difficult requirement and how this Object Oriented Programming approach solved the puzzle.

Manual Option.

This is easy to set up, if it is manually entered =myFunction("Text1")  on each Text Box's Mouse Move Event Property. 

But, the requirement is to pass the Text-Box Name as parameter dynamically to the Function.  It means that we should somehow get the Text Box Name from the Mouse Move Event and pass it as parameter to the function, placed on the same Mouse Move Event Property..

To get to know the real situation that demands this method, digest the following requirement of an Access Application:

Complexity of Requirements.

Assume that you are developing a database for a movie ticket booking Application and needs around 350 or more text boxes on the Form, for a graphical design of the seating arrangement.  Each Text-Box represent a single seat in the cinema hall, in an arrangement of several rows & Columns (i.e. each row have several seat positions) and each Seat is having a unique identity number (that is text box name), indicating it's position in the auditorium, like Row-A, Seat No.5 (A5) or B1 etc. The text-box text will show Booked or Vacant  depending on it's current status.

The idea is, when the mouse moves over the textbox (Seat) it should display the Seat Number (A5, or B1 etc.) on a dedicated Label on the Header or Footer Section of the Form, to help the customer to look for his choice of Seat Numbers and book the Seat(s).

A simple Form with several Text Boxes and a label on the top is given below to get an idea of sample arrangement of Text Boxes, to try out methods to solve this problem.

PS: The technical details presented above may have some lapses or may form suggestions in the minds of the reader.  That is not important, the core point is how do we manage to get the Text-Box Name on the Mouse Move Event and pass the name as a string parameter to the =myFunction() Function, placed in the Mouse Move Event Property.

Why Manual Method not Acceptable.

So, writing =myFunction("A5") or =myFunction("B1") etc. in each one of  350 Text Box's Event Property is lots off work.  Besides that, if any change of arrangement of Seats or reworking of the Seat Numbering scheme become necessary then all the text box Properties have to undergo manual changes. 

Another option available is to set the Control Tip Text Property with the Text Box Name. When the mouse-pointer rests on the Text-Box, after a brief delay (the delay is not acceptable), the Seat Number is displayed from Control tip text property by the System.  Modifying the Control Tip Text Property is easy and can be done dynamically on the Form_Load() Event Procedure. 

But, the database designer insists on passing the Text Box Name as parameter to the Function.  Besides displaying the Text-Box Name on the designated Label Caption and the Function may have other issues in the program to take care off as well, on the Mouse Move Event. 

The Difficult Question.

Even though it sounds like a simple issue, the difficult question is how do we get the Text-Box name, say Text1, from the Name Property, when the mouse moves over that Text-Box and pass the name as parameter to the Calling Program?   Remember, the Mouse Move Event fires repeatedly, at every mouse-point coordinates on the text box (or on any other control it moves)  and this Event have some default parameters: Button, Shift, X and Y coordinates of the Mouse Pointer on the Control.  But not the Control Name among them.

The Programming Road-Blocks.

There are times that we face road-blocks on solving issues, when conventional programing approaches doesn't give the correct solutions.  But such issues can be easily handled by few lines of Code through Object Oriented Programming.  This is a classic example, easy to understand and does the job with few lines of code.

Access Class Module Objects.

We have already covered earlier the fundamentals of Access Class Modules and Objects based programming.  If you are not familiar with stand-alone Access Class Modules and Objects then  the links are given at the bottom of this page for you to start learning the basics.

The Easy Solution.

To solve the above narrated issue we have used few lines of Code in the Access Class Module Objects (both Form and stand alone Class Modules) and used Collection Object to organize several instances of the Class Module Objects, rather than using Arrays.

The General purpose Text-Box Object Class Module: ClsTxt Code:

Option Compare Database
Option Explicit

Private WithEvents txt As Access.TextBox
Private frm As Access.Form

Public Property Get pFrm() As Access.Form
  Set pFrm = frm
End Property

Public Property Set pFrm(ByRef vNewValue As Access.Form)
  Set frm = vNewValue
End Property

Public Property Get pTxt() As Access.TextBox
   Set pTxt = txt
End Property

Public Property Set pTxt(ByRef vNewValue As Access.TextBox)
   Set txt = vNewValue
End Property

Private Sub txt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
'------------------------------------------------
'The first MouseMove Event, of each TextBox,
'comes into this sub-routine.
'The MouseMove Event Property is set with the Function:
'"=RunMouseOver('Textbox_Name','Form_Name')"
'with the TextBox & Form Names as Parameters.
'Subsequent MouseMove Events Calls the Function
'directly from Standard VBA Module1,
'control will not come into this sub-routine, any more.
'------------------------------------------------

    txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')"

End Sub

Two Class-Module Properties, the Access.TextBox  and Access.Form Object are declared in txt and frm object Variables with Private scope, respectively.  The txt Property is declared with WithEvents key word to capture Events originating from Text Boxes on Form. The next twelve lines of Code are for assigning and retrieving objects in Text Box and Form Properties with Set/Get  Property Procedures.  This will prevent direct access to the Class Module Properties txt and frm from outside.  Up to this point it is the Text Box Object's common feature for assigning and retrieving values to and from the Object Variables.  The frm Property is not used here.

The sub-routine part is what we are interested in.  Any number of Text-Box based Event Procedure sub-routines can be written here rather than directly on the Form's Class Module.

The txt_MouseMove() Event.

The Text Box's first Mouse Move Event transfers control into the txt_MouseMove() Subroutine.  There is only one executable statement in the sub-routine, that over-writes the Text Box's Mouse Move Event Property Value. 

txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')"

We can get the Text-Box name from the txt Property.  The Text-Box Mouse Move Event Property (initially set as "[Event Procedure]" in the Form_Load() Event Procedure) is replaced with the Function "=RunMouseOver('" & txt.name & "')" and passes the Text-Box name as string Parameter.  The subsequent Mouse Move Events will call the RunMouseOver() Function in Standard Module, from the Mouse Move Event Property and never comes back to the above sub-routine txt_MouseMove() any more. 

So, the first Mouse Move Event on any Text Box will do the trick and other Text Boxes will wait for their turn for a Mouse Move Event to take place.

The simple RunMouseOver() Function Code will be presented later in this page.

Form3 Class Module Code.

The Form's (Form3) Class Module VBA Code is given below:

Option Compare Database
Option Explicit

'Declare Class ClsTxt as Object F
Private F As ClsTxt
'Declare Collection Object as C
Private C As Collection

Private Sub Form_Load()
Dim ctl As Control

Set C = New Collection 'instantiate Collection Object

For Each ctl In Me.Controls 'scan through the controls
   If TypeName(ctl) = "TextBox" Then ' Take only Text Boxes
        Set F = New ClsTxt 'instantiate ClsTxt Class Object

            Set F.pFrm = Me 'Assign Form to pFrm Property
            Set F.pTxt = ctl 'Assign TextBox to pTxt property
            'enable mouse move event
            F.pTxt.OnMouseMove = "[Event Procedure]"

        C.Add F 'add ClsTxt Object to Collection

        Set F = Nothing 'remove the ClsTxt object instance from memory
    End If
Next

End Sub

Private Sub Form_Unload(Cancel As Integer)
    'Erase the Collection Object when Form3 is closed.
    Set C = Nothing
End Sub

In the global declaration area Class Module ClsTxt is declared as Object F and Collection Object as C.

In the Form_Load() Event Procedure we scan through Form3 controls and takes only Text Box controls.  The Form Object and Text-Box controls are assigned to the F.pFrm and F.pTxt Properties of ClsTxt Object.

The F.pTxt Object's OnMouseMove() Event Procedure is enabled, so that when it happens the control goes to the txt_MouseMove() sub-routine of Class Module instance of ClsTxt for the first time. In the next step ClsTxt Object instance F is added to the Collection Object, as it's Item.  In the next step the ClsTxt Object instance F is cleared from memory.  A new F object instance is created for the next Text Box.  This is necessary to identify each instance of Text Box Object, with a different internal reference, related to each Text Box added to the Collection Object as it's Item.

This process repeats for all the Text Boxes on Form3.

When the Form is closed the Form_Unload() Event executes and the Collection Object is cleared from Memory.

When Form3 is open these initializing steps are performed and all the Text Box Controls are enabled with the Mouse Move Event, added to the Collection Object and stays in memory till Form3 is closed.  Each Text Box's Mouse Move Event is handled by their respective ClsTxt Object instance added in the Collection Object.

The RunMouseOver() Function Call.

When the User moves the mouse over a Text Box (say Text-box name A1) for the first time the Mouse Move Event executes and calls the txt_MouseMove() Event Procedure in the ClsTxt Object instance, for that Text Box, in the Collection Object item.  In this procedure the TextBox's MouseMove Event Property is modified and inserted with the =RunMouseOver("A1")  Function with the Text Box name A1 as Parameter.

The second Mouse Move Event onwards it calls the RunMouseOver() Function from the Standard Moule1.  The VBA Code of this Function is given below.

Option Explicit

Public Function RunMouseOver(strN As String)
    Screen.ActiveForm.Controls("Label0").Caption = strN

End Function

The RunMouseOver() Function receives the text-box name as parameter.  The statement addresses the Label0 control, directly through the Screen Object ActiveForm route  and changes the Label's Caption with the Mouse Moved Text-box Name.

The RunMouseOver() Function can be modified to pass the Form's Name as second parameter and can be used to address the Label0 control as Forms(strForm).Controls("Label0").Caption = strN.  This is avoided to keep the parameter expression simple.

When the Mouse is moved over other Text Boxes the same procedure is repeated for that Text Box Object instances in the Collection Object.

When Form3 is closed the Collection Object instance C, containing all Text Box's ClsTxt Class Object instances, is cleared from memory.

The Function RunMouseOver() assigned to Text Box's Mouse Move Event Properties are cleared (as they are assigned dynamically) and the Property will remain empty.

Next time when Form3 is open everything falls into place again and ready for action.  So everything controlled by the Object oriented Programming and happens dynamically.  This sample database is uploaded as solution to the Access User's Forum Page 5, where several alternative options are suggested by other members of the Forum.  You may visit this Group for suggestions to solve your issues and for help on matters related to Queries, Reports, Forms etc.

The Demo Database is attached and may Download and try it out yourself.

Class Module Tutorials.

1. MS-Access Class Module and VBA
2. MS-Access VBA Class Object Arrays
3. MS-Access Base Class and Derived Objects
4. VBA Base Class and Derived Objects-2
5. Base Class and Derived Object Variants
6. Ms-Access Recordset and Class Module
7. Access Class Module and Wrapper Classes
8. Wrapper Class Functionality Transformation
9. Ms-Access and Collection Object Basics
10. Ms-Access Class Module and Collection Object
11. Table Records in Collection Object and Form
12. Dictionary Object Basics
13. Dictionary Object Basics-2
14. Sorting Dictionary Object Keys and Items
15. Display Records from Dictionary to Form
16. Add Class Objects as Dictionary Items
17. Update Class Object Dictionary Item on Form