How to access the label of a control in code

Question

Hi!

Often a label control is bound to a textbox or similar. I know that there is a way to access the label through code, but I do not remember now how to do it.

Anyone that knows?

Maybe something like:

Me.txtName.Child!lblName.Value

Or

Me.txtName.Parent!lblName.Value

I will use this when I am enumrating through the controls on a form for validation purpose. Then I want to use the label for a control as information in the error message to show the user where the error is.

Answer 1

With the textbox you can try

Text0.Controls.Item(0).Caption

where Control 0 is the linked label

Answer 2

If its access I think it is

Forms!YourFormName!YourField.Value

Or if you have a sub form its:

Forms!yourMainForm!YourSubForm!YourField.Value

Answer 3

@Astander has provided the correct answer, but keep in mind that not all controls have the same kind of Controls collections.

TextBoxes, ComboBoxes, ListBoxes, CheckBoxes have a maximum of 1 item in their controls collection (the attached label), but if the label isn't attached, they won't even have that, so .Controls(0) will throw an error.

An Option Group has multiple controls, the label and the option button or toggle buttons inside the frame. When you drop an option group on a form from the form tools toolbar, the frame is created with an attached label, so it will be the control with index 0. But if, for instance, you delete the default label, add option buttons and then add back a label, it will not be index 0, but index .Controls.Count - 1.

So, for the caption of an option group lable, you either want to be careful that if you delete the default label, you also delete the controls inside the frame after you add the label back. If that's not the case, you need to name the label and refer to it by name, because the labels for the option/toggle buttons are part of the option group's Controls collection (this surprised me -- I expected them be only in the Controls collection of the option/toggle button to which they were attached).

To avoid this problem, I can imagine convoluted code where you looped through the option group's Controls collection looking for the labels attached to the option/toggle buttons, and then looped through the option group's Controls collection a second time, this time looking only at the labels. Something like this:

  Public Function FindOptionGroupLabel(ctlOptionGroup As Control) As Control
    Dim ctl As Control
    Dim strOptionToggleLabels As String

    If ctlOptionGroup.ControlType <> acOptionGroup Then
       MsgBox ctlOptionGroup.Name & " is not an option group!", _
         vbExclamation, "Not an option group"
       Exit Function
    End If
    For Each ctl In ctlOptionGroup.Controls
      Select Case ctl.ControlType
        Case acOptionButton, acToggleButton
          If ctl.Controls.Count = 1 Then
             strOptionToggleLabels = strOptionToggleLabels & " " & ctl.Controls(0).Name
          End If
      End Select
    Next ctl
    strOptionToggleLabels = strOptionToggleLabels & " "
    For Each ctl In ctlOptionGroup.Controls
      Select Case ctl.ControlType
        Case acLabel
          If InStr(" " & strOptionToggleLabels & " ", ctl.Name) = 0 Then
             Set FindOptionGroupLabel = ctl
          End If
      End Select
    Next ctl
    Set ctl = Nothing
  End Function

Now, this breaks if there is no label attached, so it would probably make more sense for it to return the label name, rather than the control reference:

  Public Function FindOptionGroupLabel(ctlOptionGroup As Control) As String
    Dim ctl As Control
    Dim strOptionToggleLabels As String

    If ctlOptionGroup.ControlType <> acOptionGroup Then
       MsgBox ctlOptionGroup.Name & " is not an option group!", _
         vbExclamation, "Not an option group"
       Exit Function
    End If
    For Each ctl In ctlOptionGroup.Controls
      Select Case ctl.ControlType
        Case acOptionButton, acToggleButton
          If ctl.Controls.Count = 1 Then
             strOptionToggleLabels = strOptionToggleLabels & " " & ctl.Controls(0).Name
          End If
      End Select
    Next ctl
    strOptionToggleLabels = strOptionToggleLabels & " "
    For Each ctl In ctlOptionGroup.Controls
      Select Case ctl.ControlType
        Case acLabel
          If InStr(" " & strOptionToggleLabels & " ", ctl.Name) = 0 Then
             FindOptionGroupLabel = ctl.Name
          End If
      End Select
    Next ctl
    Set ctl = Nothing
  End Function

This could probably be done with a single loop through the option group's Controls collection, but it's late! What's there seems pretty close to bullet-proof, not that anyone gives a rat's ass, of course! :)