The other day on LinkedIn, Shashvat Gupta posed a deceptively difficult challenge:
Suppose there are 10 labels on a form and I want to write a common function that shows the label name on which double click event is triggered.
Now, in double click event for each label, I have call to the common function, to which I have to pass label name.
Is it possible to have a line of code in the double click event of each label that can dynamically give me the label name (instead of passing a hard-coded value) ?
Label Controls: A Unique Challenge
The deceptively challenging part is that Labels cannot receive focus.
Why does that matter? It means that you can't use one of my favorite tricks for adding generic event handlers to form controls: the
ActiveControl function. For example, in my recent article on adding modern on/off switches in Access, I set the following expression in the AfterUpdate event of the toggle button:
This approach lets you select multiple controls and set their event handler properties at one time.
Unfortunately, since Labels cannot receive focus, they can never be the ActiveControl.
AccHitTest: An Elegant Solution
In the comments, Access MVP Karl Donaubauer offered a solution I had never even heard of: the undocumented form function
That's a typical use case for the undocumented form function AccHitTest, which could look like this in the class module of the form:
Private Type POINTAPI x As Long y As Long End Type Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" _ (ByRef lpPoint As POINTAPI) As Long Private Function fctClickLabel() Dim pt As POINTAPI Dim accObject As Object GetCursorPos pt Set accObject = Me.AccHitTest(pt.x, pt.y) If Not accObject Is Nothing Then Debug.Print accObject.Name End If End Function
In the (double) click property line of the labels you write:
AccHitTest function is a method of the form object. That's why it's called as
Me.AccHitTest in Karl's sample code above. That also means that the sample code must be called from the form's code-behind module.
Creating a Generic Function
The sample approach works well for one form, but what if you wanted to create a generic function that you could call from multiple forms.
The code is almost identical, except that you will need to pass an instance of the form object to the function:
'Copy and paste the following code into a Standard module
Private Type POINTAPI
x As Long
y As Long
Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" _
(ByRef lpPoint As POINTAPI) As Long
Function ShowControlName(Frm As Form)
Dim pt As POINTAPI
Dim accObject As Object
Set accObject = Frm.AccHitTest(pt.x, pt.y)
If Not accObject Is Nothing Then
MsgBox "You just clicked on " & accObject.Name
Here's how you would call the above function from a control's event handler:
- On Click:
I asked Karl how he learned about this hidden feature and he replied to me via email. Here was his response (shared with permission):
I learned about AccHitTest in discussions in the German NNTP newsgroups. The function is certainly older, but I saw it first mentioned there in 2004 by the Viennese (of course ;-) Gottfried Lesigang.
Later it was "popularized" as a solution in our discussions (e.g. here combined with a timer) by the German colleague Jörg Ostendorp, who then also mentioned it in the script and an example db of an API talk for my German-language conference AEK in 2007. Lucky you, who learned some German. ;-) [NOTE: Karl is referring to the one year of German I took in college, of which I remember roughly nichts <-- I even had to Google "nichts"🤦♂️]
If you activate the hidden members in the VBA object browser and search for it, you can see that it exists for many types of controls.
Here's what Karl meant about activating hidden members in the Object Browser and searching for "AccHitTest":
Origins of the AccHitTest Function
The "Acc" in AccHitTest stands for "Accessibility" and NOT "[Microsoft ]Access."
The versions of this function in the Access object model are almost certainly a wrapper around the
IAccessible:accHitTest method from the Windows API:
The IAccessible::accHitTest method retrieves the child element or child object that is displayed at a specific point on the screen.
Now, if you'll excuse me, I take my leave in search of nails to pound with my newfound hammer...