Introduction.
This is about running a User-Defined Function (say =myFunction(Parameter)) on the Mouse Move Event Property of TextBoxes. The difficult part is that when the Mouse Move Event occurs the Event running TextBox 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 TextBox Name as a 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 a 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:
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 design of the seating arrangement. Each TextBox represents a single seat in the cinema hall, in an arrangement of several rows & Columns (i.e. each row has several seat positions) and each Seat is having a unique identity number (that is the text box name), indicating its 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 its 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 look for his choice of Seat Numbers and book the Seat(s).
A simple Form with several TextBoxes and a label on the top is given below to get an idea of textbox arrangements and try out this method to solve the 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 we manage to 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.
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 of work. Besides that, if any change of arrangement of Seats or reworking of the Seat Numbering scheme becomes 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 TextBox, after a brief delay (the delay is not acceptable), the Seat Number is displayed from the Control tip text property of 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 a parameter to the Function. Besides displaying the TextBox Name on the designated Label Caption and the Function may have other issues in the program to take care of 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 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 that we face roadblocks to solving issues when conventional programming approaches don't give the correct solutions. 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 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 a 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 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, respectively. The txt Property is declared with the WithEvents keyword to capture Events originating from TextBoxes 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 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 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 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 passes the TextBox 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() 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 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 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 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 TextBox Object, with a different internal reference, related to each Text Box 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 initializing steps are performed and all the Text Box 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 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 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, 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 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 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 Text Box Object instance 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 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 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.
- 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 ClsEmployees2007.zip
