First Published 4 Jan 2024


In recent versions of Access, it has been possible to get the label name associated with a control such as a textbox, combobox or listbox from the control property sheet.


However, it is often useful to get the name or caption of a label associated with a control using code.
Doing this allows you to modify the label properties without having to specify its name directly

Similarly, at times it is useful to get the properties of the parent control from the label.
Unfortunately, this is not available from the label property sheet but it can also be done in code.




a)   Label Name from Control

      Syntax:      ControlName.Controls(0).Name

      Example Code:

' label name for specified textbox on the current form
Me.txtLastName.Controls(0).Name

' label name for active control on the current form
Me.ActiveControl.Controls(0).Name

b)   Label Caption from Control

      Syntax:      ControlName.Controls(0).Caption

      Example Code:

' label caption for specified combobox on the current form
Me.cboGender.Controls(0).Caption

' label caption for active control on the current form
Me.ActiveControl.Controls(0).Caption

c)   Control Name from Label

      Syntax:      ControlName.Controls(0).Parent.Name

      Example Code:

' name of parent control for specified label on the current form
Me.FirstName_Label.Controls(0).Parent.Name

' name of active control by clicking on its associated label on the current form
Me.ActiveControl.Controls(0).Parent.Name

d)   Control Name

      Syntax:      ControlName.Name

      Example Code:

' name of active control on the current form
Me.ActiveControl.Name

e)   Control Caption e.g. Command Button

      Syntax:      ControlName.Caption

      Example Code:

' caption of active control on the current form
Me.ActiveControl.Caption

f)   Control Type

      Syntax:      ControlName.ControlType

      This returns a number corresponding to the control type e.g. 109 (acTextBox), 110 (acListBox), 104 (acCommandButton) etc
      Example Code:

'control type for specified textbox on the current form
Me.txtCompany.ControlType      ' returns 109

'control type for specified command button on the current form
Me.cmdPrint.ControlType      ' returns 104

'control type for active control on the current form
GetControlTypeName(Me.ActiveControl.ControlType)      ' returns e.g. Combo Box (etc)

These number values are not particularly meaningful for most users so it may help to convert to a description of each control type:

Public Function GetControlTypeName(lngCtrlType As Long) As String

' Based on code at https://www.devhut.net/access-vba-convert-controltype-to-name/

On Error GoTo Err_Handler

Select Case lngCtrlType

      Case acAttachment       '126
           GetControlTypeName = "Attachment"

      Case acBoundObjectFrame      '108
            GetControlTypeName = "Bound Object Frame"

      Case acCheckBox      '106
            GetControlTypeName = "Check Box"

      Case acComboBox       '111
            GetControlTypeName = "Combo Box"

      Case acCommandButton       '104
            GetControlTypeName = "Command Button"

      Case acCustomControl       '119
            GetControlTypeName = "ActiveX"

      Case acEmptyCell       '127
            GetControlTypeName = "Empty Cell"

      Case acImage       '103
            GetControlTypeName = "Image"

      Case acLabel       '100
            GetControlTypeName = "Label"

      Case acLine       '102
            GetControlTypeName = "Line"

      Case acListBox       '110
            GetControlTypeName = "List Box"

      Case acNavigationButton       '130
            GetControlTypeName = "Navigation Button"

      Case acNavigationControl       '129
            GetControlTypeName = "Navigation Control"

      Case acObjectFrame       '114
            GetControlTypeName = "Unbound Object Frame"

      Case acOptionButton      '105
            GetControlTypeName = "Option Button"

      Case acOptionGroup       '107
            GetControlTypeName = "Option Group"

      Case acPage       '124
            GetControlTypeName = "Page"

      Case acPageBreak       '118
            GetControlTypeName = "Page Break"

      Case acRectangle       '101
            GetControlTypeName = "Rectangle"

      Case acSubform       '112
            GetControlTypeName = "SubForm"

      Case acTabCtl       '123
            GetControlTypeName = "Tab"

      Case acTextBox       '109
            GetControlTypeName = "Text Box"

      Case acToggleButton       '122
            GetControlTypeName = "Toggle Button"

      Case acWebBrowser       '128
            GetControlTypeName = "Web Browser"

End Select

Exit_Handler:
      Exit Function

Err_Handler:
      MsgBox "Error " & Err & " in GetControlTypeName procedure: " & Err.Description
      Resume Exit_Handler

End Function

      Using the above code, we can identify the control type description

g)   Control Type Description

      Syntax:      GetControlTypeName(ControlName.ControlType)

      Example Code:

'control type for specified textbox on the current form
GetControlTypeName(Me.txtCompany.ControlType)      ' returns Text box

'control type for specified listbox on the current form
GetControlTypeName(Me.lstClasses.ControlType)      ' returns List Box

'control type for active control on the current form
GetControlTypeName(Me.ActiveControl.ControlType)      ' returns e.g. Command Button (etc)

We can combine all of the above code to get all of this information in a message box for any control from e.g. a click or mouse down event.

The example app includes a form with a variety of control types:


Example output:

Textbox	Command Button	Subform
Textbox (no label)	Command Button (no label)	Tab Control Page

All of the above output requires just one line of code for each control, GetControlProperties Me. For example:

CODE:

Private Sub Command11_Click()
      GetControlProperties Me
End Sub

The GetControlProperties procedure is saved in a standard module so it can be used with any form.
Different output is obtained depending on the control type and whether it has an associated label.

CODE:

Sub GetControlProperties(frm As Access.Form)

On Error GoTo Err_Handler

If Nz(frm.ActiveControl.Controls(0).Name, "") <> "" Then 'error 2467 if no attached label

      Select Case frm.ActiveControl.ControlType

      Case acCommandButton
           MsgBox "Control name = " & frm.ActiveControl.Name & vbCrLf & _
                 "Control type = " & GetControlTypeName(frm.ActiveControl.ControlType) & " (" & frm.ActiveControl.ControlType & ")" & vbCrLf & _
                 "Control caption = " & frm.ActiveControl.Caption & vbCrLf & vbCrLf & _
                 "Label Name = " & frm.ActiveControl.Controls(0).Name & vbCrLf & _
                 "Label caption = " & frm.ActiveControl.Controls(0).Caption & vbCrLf & vbCrLf & _
                  "---------------------------" & vbCrLf & vbCrLf & _
                  "Control name from label = " & frm.ActiveControl.Controls(0).Parent.Name, vbInformation, "Control Properties"

      Case acLabel, acImage
            'nothing here - cannot get focus

      Case Else
            MsgBox "Control name = " & frm.ActiveControl.Name & vbCrLf & _
                  "Control type = " & GetControlTypeName(frm.ActiveControl.ControlType) & " (" & frm.ActiveControl.ControlType & ")" & vbCrLf & vbCrLf & _
                  "Label Name = " & frm.ActiveControl.Controls(0).Name & vbCrLf & _
                  "Label caption = " & frm.ActiveControl.Controls(0).Caption & vbCrLf & vbCrLf & _
                  "---------------------------" & vbCrLf & vbCrLf & _
                  "Control name from label = " & frm.ActiveControl.Controls(0).Parent.Name, vbInformation, "Control Properties"

      End Select

Else

ControlNoLabel:
      Select Case frm.ActiveControl.ControlType

            Case acCommandButton
                  MsgBox "Control name = " & frm.ActiveControl.Name & vbCrLf & _
                  "Control type = " & GetControlTypeName(frm.ActiveControl.ControlType) & " (" & frm.ActiveControl.ControlType & ")" & vbCrLf & _
                  "Control caption = " & frm.ActiveControl.Caption & vbCrLf & vbCrLf & _
                  "No associated label", vbInformation, "Control Properties"

            Case acLabel, acImage
                  'nothing here - cannot get focus

            Case Else
                  MsgBox "Control name = " & frm.ActiveControl.Name & vbCrLf & _
                        "Control type = " & GetControlTypeName(frm.ActiveControl.ControlType) & _
                        " (" & frm.ActiveControl.ControlType & ")" & vbCrLf & vbCrLf & _
                        "No associated label", vbInformation, "Control Properties"

      End Select
End If

Exit_Handler:
      Exit Sub

Err_Handler:
     If Err = 2467 Then GoTo ControlNoLabel      'no label
     MsgBox "Error " & Err & " in GetControlTypeName procedure: " & Err.Description
     Resume Exit_Handler

End Sub

NOTE:
Certain controls cannot get focus and so the control properties cannot be obtained by these methods.
Examples include: unassociated labels / image controls / tab controls
These need to be handled separately. How this is done will be covered in a separate article (page link to follow)



Populate Control Properties Table

Another very simple approach uses similar code to loop through all controls and populate the control properties to a table tblControlProps

Sub PopulateControlPropsTable()

On Error Resume Next

      Dim ctl As Control
      Dim rst As DAO.Recordset
      Dim strSQL As String

      strSQL = "SELECT * FROM tblControlProps"

      Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

      With rst
            For Each ctl In Me.Controls
                  .AddNew
                  !ControlName = ctl.Name
                  !ControlCaption = ctl.Caption
                  !ControlType = ctl.ControlType
                  !ControlTypeName = GetControlTypeName(ctl.ControlType)

                  If ctl.ControlType <> 100 Then     ' omit for labels
                        !LabelName = ctl.Controls(0).Name
                        !LabelCaption = ctl.Controls(0).Caption
                  End If

            .Update

            Next ctl

            .Close

      End With

      Set rst = Nothing

End Sub

Click the View All Control Props button to run this code and display the results in form frmControlProps




Download

Click to download the example app:     Control Properties      ACCDB - approx 0.6 MB (zipped)



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 4 Jan 2024

Return to Code Samples Page

Return to Top