CODEWITHSUNDEEP
ms-accessvba
Matt Hall
Matt Hall

Reputation: 2412

Loop through particular type of controls on a form (e.g. all text boxes)

How would I loop through all text boxes on form (ignoring any other type of control that might be on the form).

For example, I'd quite like to do some validation that gives a list of the text boxes that are empty on a form.

This sort of gives me what I'm after:

Public Sub emptyFields()

    Dim txt As Control
    Dim msgText As String

    msgText = ""

    For Each txt In Me.Controls

        If _
            IsNull(txt) _
        Then

            msgText = msgText & vbCrLf & txt.Tag

        End If

    Next txt

    MsgBox "Please complete the following:" & vbCrLf & msgText    
End Sub

However, I can tell this is looping through all of the other controls on the form as these are getting a blank line in the MsgBox text.

I'd also prefer to use the .Caption property of the text boxes, which doesn't seem to be available in the above method (probably because not all controls have captions), hence why I've used the .Tag property instead.

Upvotes: 1

Views: 2221

Answers (1)

Fionnuala
Fionnuala

Reputation: 91316

You can check the ControlType.

Sub AllText()
Dim ctl As Control

For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        Debug.Print "textbox"
    End If
Next

End Sub

Only Labels have a Caption property, however, Item 0 of the control's Controls Collection is the label, so:

 Debug.Print ctl.Controls.Item(0).Caption

Upvotes: 2

Related Questions