How to test if a label is linked to a textbox

[Seth Schrock]

I'm currently using the following code to reference the label that is linked to a textbox.

Forms(frm).Controls(ctl.Name).Controls.Item(0).ForeColor = vbBlack

frm is a parameter passed to the sub and contains the name of the calling form. ctl is declared as a control and is populated by looping through the controls in the form. Anyway, back to my question. If there isn't a label linked to the textbox, I get an error saying object doesn't exist (as would be expected, since it really doesn't exist). Is there a way to test if the object does exist so that I can do something else with the textbox instead of its label?

[ADezii]

The Parent property of a label control returns an Object Reference to the Control the Label is linked to. If the Parent Property of a Label returns the Form Name, then it is not associated with a Text Box as illustrated below where 3 Labels have no association with any Text Box.

Dim obj As Object
Dim ctl As Control
 
Debug.Print "Label", "Parent"
Debug.Print "--------------------------------------"
 
For Each ctl In Me.Controls
  If ctl.ControlType = acLabel Then
    Set obj = ctl.Parent
      Debug.Print ctl.Name, obj.Name, IIf(obj.Name = "Form2", "NOT LINKED", "")
  End If
Next
 
Debug.Print "--------------------------------------"

OUTPUT:

Label         Parent
--------------------------------------
Label5        State         
Label7        AccountNumber 
Label9        cboProducts   
Label22       txtTest       
Label23       EmployeeID    
Label24       LastName      
Label27       Form2         NOT LINKED
Label28       Form2         NOT LINKED
Label10       Form2         NOT LINKED
--------------------------------------

[Seth Schrock]

My problem is that I'm working from the textbox, not the label. So basically, I need something like

If Exists(Forms(frm).Controls(ctl.Name).Controls.Item(0)) Then
    'Work with the label
Else
    'Work with the textbox
End If

I just need to know how to create the Exists() function. I don't need it to be a separate function, but I need the functionality of it. I just don't know how to do that.

It is useful to know about the parent property of the label though.

[ADezii]

@Seth:
If all else fails, you can always use reverse Logic. I will not bore you with an explanation, but simply Post how this can be done.

1. Function Definition:
   

   Public Function fFindLabel(txt As TextBox, frm As Form)
   Dim ctl As Control
   Dim blnLabelFound As Boolean
    
   For Each ctl In frm.Controls
     If ctl.ControlType = acLabel Then
       Set obj = ctl.Parent
         If obj.Name = txt.Name Then       
           blnLabelFound = True
             Exit For
         End If
     End If
   Next
    
   If blnLabelFound Then
     'Set the ForeColor of the associated Label to Red
     ctl.ForeColor = vbRed
   Else
     'No associated Label, set Forecolor of Text Box to Blue
     txt.ForeColor = vbBlue
   End If
   End Function

2. Sample Calls (with/without associated Label):
   

   'Has an associated Label
   Call fFindLabel(Me![EmployeeID], Me)
    
   'No Label associated with this Text Box
   Call fFindLabel(Me![Text30], Me)

[NeoPa]

I think it works the other way too Seth. If a label has a parent of another control then it is linked to the other control. If a TextBox has a linked label then it'll be in its .Controls collection.

So, if Forms(frm).Controls(ctl.Name).Controls.Count > 0 then I would expect it to have a linked label. If not, not.

[Seth Schrock]

Ah, that is what I was looking for. Works perfectly. Thanks NeoPa.

And thank-you Adezii for your help. I will definitely put your solution in my bag of tricks.

[ADezii]

@NeoPa:
Very nice response, NeoPa. I never even considered this possibility since the Controls Collection is applicable to only Forms, Sub-Forms, Reports, and Sections. Very intuitive on your part. That's what I love about this place, you can learn something new every day! (LOL).

BTW, replacing my dozen and a half lines of Code with one is a great trade-off.

[NeoPa]

ADezii:
...the Controls Collection is applicable to only Forms, Sub-Forms, Reports, and Sections.

I'm not sure that statement is quite right my friend. I didn't before, but looking at the Help page for Controls in Access 2003 (The last version that seems to have a useful Help system) it says :

The Controls collection contains all of the controls on a form, report, or subform, within another control, or attached to another control. The Controls collection is a member of a Form, Report, and SubForm objects.

Easy to misinterpret as it seems to contradict itself to be fair. And further down :

Other Control objects have a Controls collection that can contain an attached label. These controls include the text box, option group, option button, toggle button, check box, combo box, list box, command button, bound object frame, and unbound object frame controls.

In this case I didn't look into Help as Seth had already made a reference to it in his original code. All I did was know that if there was a Controls collection there then the .Count property should indicate whether or not it had a label. I couldn't think of another control that a TextBox might be associated with in any other way.

Anyway, all good. An interesting snippet we all learned from I expect :-)