Name property of any object on a form when clicked

[deduct]

I need some VBA code that I can include on a form so that whenever I click on any object on the form, a mesage box will tell me the contents of the Name property of that object.

Thanks.

ps. I have tried Me.ActiveControl.Name, but it does not always give me the name of the object on which I am clicking.

Karl

 

[MajP]

certain controls cannot take focus and therefore cannot be clicked. If you try to click a label this cannot be the active control so the name is probably the name of the associated textbox.

 

[PHV]

You may find this interesting:
thread707-1698466

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Build a CLASS module. Call it "CommonControl"
Drop this in

Option Compare Database
Option Explicit

'Class Module Name: CommonControl
'Developed by: MajP
'
'Purpose: This Class Module along with the CommonControls collection allows you to build a
'pseudo control array that will react to one or more events.  I only demonstrated using
'Text boxes, List boxes, Combo boxes, and Labels.  I only demonstrated the OnClick Event and
'the BeforeUpdate event. You can easily add more controls and events by following the code.

'How to use:
'1. Place this code in a CLASS (NOT A STANDARD MODULE) module named "CommonControl"
'2. You can use this code without the custom collection, but it has little utility
'3. Read the instructions for the CommonControls custom collection and place the
'CommonControls class in a CLASS module called "CommonControls"
'4. Place your common event procedures in a standard module or modify the event procedures within
'below code
'5. The example below is one way to use the class. For the controls you want to react to events
'place a ? mark in the tag propery. Do not enclose in parentheses.  Then on the form
'
'************************ Form Code Start *****************************************************
'Option Compare Database
'Option Explicit
'Dim ccControls As New CommonControls
'Private Sub Form_Load()
'  On Error Resume Next
'  Dim ctl As Access.Control
'  For Each ctl In Me.Controls
'    If ctl.ctlType = acTextBox Or ctl.ctlType = acLabel Or ctl.ctlType = acListBox Or ctl.ctlType = acComboBox Then
'      If ctl.Tag = "?" Then
'        ccControls.Add ctl, ctl.Name
'      End If
'    End If
'  Next ctl
'End Sub
'************************ Form Code End *******************************************************
'
'************************ Class Code Start ****************************************************

Private WithEvents mLabel As Access.Label
Private WithEvents mTextBox As Access.TextBox
Private WithEvents mlistBox As Access.ListBox
Private WithEvents mComboBox As Access.ComboBox
Private WithEvents mcheckBox As Access.CheckBox
Private mControl As Access.Control

Private mName As String
Public Property Get CommonControl() As Access.Control
  Set CommonControl = mControl
End Property
Public Property Set CommonControl(ByVal ctlControl As Access.Control)
  On Error GoTo ErrHandler
  Set mControl = ctlControl
  'More Events and more controls could be added here
  Select Case ctlControl.ControlType
     Case acLabel
        Set mLabel = ctlControl
        mLabel.OnClick = "[Event Procedure]"
     Case acTextBox
        Set mTextBox = ctlControl
        mTextBox.OnClick = "[Event Procedure]"
     Case acListBox
       Set mlistBox = ctlControl
       mlistBox.OnClick = "[Event Procedure]"
     Case acComboBox
        Set mComboBox = ctlControl
        mComboBox.OnClick = "[Event Procedure]"
  End Select
  Exit Property
ErrHandler:
   'Not sure why 459 (does not support events"
   'or 91 (object not set) errors are thrown. I think it has to do
   'with using a generic Access.Control object
   If Not (Err.Number = 459 Or Err.Number = 91) Then
      MsgBox ("Error: " & Err.Number _
            & " " & Err.Description _
            & " " & Err.Source)
   End If
   Resume Next
End Property

Private Sub mTextBox_Click()
  Call commonClickProcedure(mTextBox)
End Sub
Private Sub mComboBox_Click()
  Call commonClickProcedure(mComboBox)
End Sub
Private Sub mListBox_Click()
  Call commonClickProcedure(mlistBox)
End Sub
Private Sub mLabel_Click()
  Call commonClickProcedure(mLabel)
End Sub
Private Sub mlistBox_BeforeUpdate(Cancel As Integer)
  Call commonBU_Procedure(mlistBox)
End Sub
Public Property Get Name() As String
  Name = mName
End Property
Public Property Let Name(ByVal strName As String)
  mName = strName
End Property
Public Function commonClickProcedure(ctl As Access.Control)
  MsgBox "Click " & ctl.Name
End Function

Build a Class module, call is "CommonControls"

Option Compare Database
Option Explicit

'Class Module Name: CommonControls
'Developed by: MajP
'
'Purpose: This Class Module is the collection class for the object class "CommonControls"
'The collection allows you to build a pseudo control array that will react to one or more events.

'How to use:
'1. Place this code in a CLASS (NOT A STANDARD MODULE) module named "CommonControls"
'2. Read the instructions for the CommonControl class
'
'************************ Class Code Start ****************************************************

Private mCommonControls As New Collection
Public Function Add(ctlControl As Access.Control, ctlName As String) As CommonControl
   Dim newCommonControl As CommonControl
   Set newCommonControl = New CommonControl
   Set newCommonControl.CommonControl = ctlControl
   newCommonControl.Name = ctlName
   mCommonControls.Add Item:=newCommonControl, Key:=ctlName
   Set Add = newCommonControl
End Function
Public Property Get count() As Integer
   count = mCommonControls.count
End Property
Public Property Get Item(ByVal index As Variant) As CommonControl
   Set Item = mCommonControls(index)
End Property
Public Sub Remove(index As Variant)
   mCommonControls.Remove (index)
End Sub

Private Sub Class_Initialize()
  'MsgBox "class Intialized"
End Sub

Private Sub Class_Terminate()
 Set mCommonControls = Nothing
End Sub
Public Sub Clear()
    Set mCommonControls = New Collection
End Sub

On your form you have to disassociate all labels from the textboxes. Basically delete the labels, and add new ones.

on your forms you will need code like

Public CCs As CommonControls
Private Sub Form_Load()
  Dim ctrl As Access.Control
  Set CCs = New CommonControls
  For Each ctrl In Me.Controls
    CCs.Add ctrl, ctrl.Name
  Next ctrl
End Sub

I only created function for a label, listbox, textbox, and combobox. If you want other controls then you will have to add that to the commoncontrol class.

The beauty of this method is that every control will share this click event, but you can also specify unique click events. So the control in a sense has 2 click events.

 

[deduct]

Wow, I will try this out. Thank You!

Karl

 

[MajP]

This may seem like overkill for what you are doing, but it is part of some larger code I have. The only reason you have to do this is to account for labels as far as I know. If not you could simply select all controls and in the click event property type in the name of a function. Like:

On Click =getName()

then write the function

public function getName() as string
msgbox activecontrol.name
end function

The problem is with labels. A disassociated label has a click event, but cannot get focus. So you will not get the label name. The problem with associated labels is that they do not even have a click event.

if you did not have a lot of labels then you could do those independantly
Write a function

public function getLabelName(strName as string) as string
msgbox strName
end function

then in each label you would have to hand jam the function call

on click =getLabelName("yourLabelNameHere")


In my case the code is already written, so it is a whole lot easier to reuse that code. Most Access programmers would not know how to write code like that.