Calling a Function from the MouseMove Event Property.
This is about running a User-Defined Function (say =myFunction (Parameter)) on the TextBox MouseMove event property. The difficult part is that when the MouseMove event occurs, the TextBox Name that runs the event must be passed as a parameter to the function.
This question was asked by a member in the Access Users Forum (www.accessforums.net), in the Forms Category of Posts, seeking suggestions for a solution. A sample demo database was posted there twice on page 5, but the last one is the final version.
I thought this would be useful to our readers and have presented it here, with details of how this difficult task was solved using an Object-Oriented Programming approach.
Manual Option.
This is easy to set up if it is manually entered =myFunction("Text1") on each TextBox's Mouse Move Event Property.
But the requirement is to pass the TextBox Name as a parameter dynamically to the Function. It means we should somehow get the TextBox Name from the Mouse Move Event and pass it as a parameter to the function, placed on the same Mouse Move Event Property.
The real situation that demands this method is the following requirement of an Access Application:
The complexity of Requirements.
Assume that you are developing a database for a movie ticket booking Application and need around 350 or more text boxes on the Form, for a graphical seating arrangement design. Each TextBox represents a single seat in the cinema hall, in several rows & Columns (i.e., each row has several seat positions). Each seat has a unique identity number (that is, the TextBox name), indicating its position in the auditorium; for example: Row-A, Seat No.5 (A5) or B1, etc. The TextBox will display its current status either Booked or Vacant.
The idea is that when the mouse moves over the TextBox (Seat), it should display the Seat Number (A5, or B1, etc.) on a dedicated Label in the Header or Footer Section of the Form, to help the customer look for their choice of Seat Numbers and book the Seat(s).
A simple Form with several TextBoxes and labels for sample TextBox arrangements, and try out this method to solve the problem.
PS: The technical details presented above may have lapses or may form suggestions in the reader's mind. That is not important; the core point is how we could get the TextBox Name on the Mouse Move Event and pass the name as a string parameter to the =myFunction() Function, placed on the Mouse Move Event Property of the same TextBox.
Why Manual Method unacceptable.
So, writing =myFunction("A5") or =myFunction("B1") etc., in each one of the 350 Text Boxes' Event Property demands a lot of manual work. Besides that, if any change to the seating arrangement or Seat Numbering scheme becomes necessary, then all the TextBox Properties have to undergo manual changes.
Another option available is to set the Control Tip Text Property with the TextBox Name. When the mouse pointer rests on the TextBox, a brief delay occurs (not acceptable), then it displays the Seat Number from the Control Tip Text property. Modifying the Control Tip Text Property is easy and can be done dynamically in the Form_Load() Event Procedure.
But the database designer insists on passing the TextBox Name as a parameter to the Function. Besides displaying the TextBox Name on the designated Label Caption, the Function may have other issues in the program to take care of.
The Difficult Question.
Even though it sounds like a simple issue, the difficult question is: how do we get the TextBox name, say Text1, from the Name property of the running Form when the mouse moves over that TextBox, and pass the name as a parameter to the Calling Program? Remember, the Mouse Move Event fires repeatedly, at every mouse-point coordinate on the text box (or on any other control it moves) and this Event has 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 Roadblocks.
There are times when we face roadblocks in solving issues, when conventional programming solutions don't work. But such issues can be easily handled by a few lines of code through Object-Oriented Programming. This is a classic example, easy to understand, and does the job with a 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 Page links at the bottom of this page to start learning the basics.
The Easy Solution.
To solve the above-narrated issue, we have used a few lines of code in the Access Class Module Objects (both Form and stand-alone Class Modules) and used a Collection Object to organize several instances of the Class Module Objects, rather than using Arrays.
The General-purpose TextBox 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' Objects are declared in txt and frm object variables, respectively, with Private scope. The txt Property is declared with the WithEvents keyword to capture Events originating from TextBoxes on the form. The next twelve lines of code assign and retrieve objects in TextBox 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 TextBox Object's common feature of 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 subroutines 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 subroutine that overwrites the Text Box's Mouse Move Event Property Value.
txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')"
We can get the TextBox name from the txt Property. The TextBox Mouse Move Event Property (initially set as "[Event Procedure]" in the Form_Load() Event Procedure) is replaced with the Function "=RunMouseOver('" & 'txt.name' & "')", and the TextBox name as a string Parameter. The subsequent Mouse Move Events will call the RunMouseOver() Function in the Standard Module from the Mouse Move Event Property, and never come back to the above sub-routine txt_MouseMove() anymore.
So, the first Mouse Move Event on any Text Box will do the trick, and other TextBoxes will wait for their turn for a Mouse Move Event to take place.
The simple RunMouseOver() Function Code will be presented later on 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 a Collection Object as C.
In the Form_Load() Event Procedure, we scan through Form3 controls and take only TextBox controls. The Form Object and TextBox controls are assigned to the F.pFrm and F.pTxt Properties of the 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 the Class Module instance of ClsTxt for the first time. In the next step, the ClsTxt Object instance F is added to the Collection Object, as its 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 the TextBox Object with a different internal reference of each TextBox added to the Collection Object as its Item.
This process repeats for all the TextBoxes 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 initialization steps are performed, and all the TextBox Controls are enabled with the Mouse Move Event, added to the Collection Object, and stay in memory till Form3 is closed. Each Text Box's Mouse Move Event is handled by its respective ClsTxt Object instance added to the Collection Object.
The RunMouseOver() Function Call.
When the user moves the mouse over a Text Box (say Textbox 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 set to the =RunMouseOver("A1") Function, with the Text Box name A1 as a parameter.
From the second MouseMove event onwards, the event calls the RunMouseOver() Function from the Standard Module1. 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 textbox name as a parameter. The statement addresses the Label0 control directly through the Screen Object ActiveForm route and changes the Label's Caption with the Mouse Moved Textbox Name.
The RunMouseOver() Function can be modified to pass the Form's Name as a 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 TextBoxes, the same procedure is repeated for that TextBox object instance in the Collection Object.
When Form3 is closed, the Collection Object instance C, containing all TextBoxes' ClsTxt Class Object instances, is cleared from memory.
The Function RunMouseOver() assigned to the TextBox's Mouse Move Event Properties is cleared (as they are assigned dynamically), and the Property will remain empty.
Next time Form3 is opened, everything falls into place again and is ready for action. So everything is controlled by Object-oriented Programming and happens dynamically. This sample database is uploaded as a solution to the Access User's Forum Page 5, where several alternative options are suggested by other members.
The Demo Database is attached and may be downloaded; try it out yourself.
Class Module Tutorials.
- MS-Access Class Module and VBA
- MS-Access VBA Class Object Arrays
- MS-Access Base Class and Derived Objects
- VBA Base Class and Derived Objects-2
- Base Class and Derived Object Variants
- MS Access Recordset and Class Module
- Access Class Module and Wrapper Classes
- Wrapper Class Functionality Transformation
- MS Access and Collection Object Basics
- MS Access Class Module and Collection Object
- Table Records in Collection Object and Form
- Dictionary Object Basics
- Dictionary Object Basics-2
- Sorting Dictionary Object Keys and Items
- Display Records from Dictionary to Form
- Add Class Objects as Dictionary Items
- Update Class Object Dictionary Item on Form
Download MouseMoveEvent.zip
