Introduction
For different reasons, we often need to address more than one, or a few, controls at the same time. Having one line of code performing enabled=false (and an additional enabled=true) per each control, in addition to the if then else/select case construct, might seem a bit much. This faq contains some alternative methods using the method of looping thru the form controls collection to perform such.

The first challenge is that not all controls share the same properties. For instance labels and buttons doesn't have a .Value property, so it might be wise to only address the controls having the types one need to alter/toggle properties of, else one ends up getting errors. The often occuring error, would probably be 438 - ôObject doesn't support this property or methodö.

To find out more about which controltypes are available, type controltype in VBE and hit F1. Using the Controltype property of controls to limit which controls different actions are performed against, is the method IÆll use here. Another method, is to allow an exception (error), using On Error Resume Next.

I've deliberately not included any reference to which event such snippets might be used in. Some of them might fit in the on current event of the form, some in the before or after update events of certain controls, button click etc. You'll have to decide upon the appropriate event to call them.

In the following samples, I've used

For Each ctl In Me.Controls

which implies looping thru all the controls residing on the form. One can also limit these loops to sections of the form, so that for instance the following samples:

For Each ctl In Me.Detail.Controls
For Each ctl In Me.Section("Detail").Controls

Would work on the section named ôDetailö in the form (names of sections are usually available thru the intellisense dropdown in addition to the Properties Dialog when in the forms design view). HereÆs also a more dynamic approach:

For Each ctl In Me.Section(acHeader).Controls
For Each ctl In Me.Section(acDetail).Controls

This will address the section regardless of name. What then if youÆd like to loop only the controls residing on for instance a tab control? Or just one of the pages of the tab control. ThatÆs possible too, for instance using the following syntax:

For Each ctl In Me("MyTab").Pages("Page1").Controls

This would loop only the controls residing on the page ôPage1ö on the tab control ôMyTabö on the current form. Here too, there are possibilities of addressing more dynamic, but IÆll leave that to you.

CONTENTS

ôClear the formö samples
Enable/Lock/Visible properties samples
Toggle properties based on grouping/tag sample
Using the .Tag property when validating
ôControls Arrayö sample
Carry over values to new record (using DefaultValue)
Changing properties of attached labels

1. ôClear the formö
Note - to clear the form means removing the contents of the controls. This should only be performed on unbound forms, or on unbound controls on the form (see samples using the .Tag property further down in this faq). To ôClearö a bound form, either use some gotonew record variant, or for instance toggle the .DataEntry property of the form.

HereÆs a sample clearing all the text controls on an unbound form:

Dim ctl As Control
For Each ctl In Me.Controls
    If (ctl.ControlType = acTextBox) Then
        ctl.Value = Null
    End If
Next ctl

Controls having a calculated controlsource will provide an error in the above sample. There are several ways of avoiding such, check out the usage of the .Tag property further down, or perhaps this alteration:

Dim ctl As Control
For Each ctl In Me.Controls
    If (ctl.ControlType = acTextBox )Then
        If (Left$(ctl.ControlSource, 1) <> "=") Then
            ctl.Value = Null
        End If
    End If
Next ctl

Note - in this alteration, controls bound to a field in the forms recordsource would also be set to Null. Replacing the controlsource test with:

If (Len(Trim$(ctl.ControlSource & vbNullString)) = 0) Then

would ensure that only controls without any controlsource is cleared. Note however to excluce controls not having a controlsource property before attempting this test, else it will throw an exception.

This works also for removing the selection in comboboxes and listboxes (having .Multiselect property ônoneö) and option/checkboxes having triple state set to Yes.

For listboxes with .Multiselect see next sample (resetting the rowsource works for deselecting), demonstrating different actions on different controltypes.

Dim ctl As Control
For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acListBox
            If (ctl.MultiSelect = 0) Then
                ' Values of the multiselect property:
                '   0 û None
                '   1 û Simple
                '   2 û Extended
                ctl.Value = Null
            Else
                ctl.RowSource = ctl.RowSource
                ' Found in TheAceMan1's faq, thanx
            End If
        Case acCheckBox
            ctl.Value = False
    End Select
Next ctl

Note again, this is performed on unbound controls.

2. Enable/Lock/Visible properties
Altering/toggling properties can be performed using the same technique. There's just one additional element. Some of these properties cannot be altered on controls having the focus, so one needs to track that some way.

The simplistic approach (used here) is to create another textcontrol, here named "txtTst" on the form(s) the routine is applied to, setfocus to this control, and toggle the properties of the rest of the controls (in this sample, the focus is not reset to initial control).

Note - this extra control (here, txtTst) must be visible, one can reduce the size, make it transparent etc to make it invisible for the user...

Dim ctl As Control
Me("txtTst").SetFocus
For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acComboBox, acListBox, acTextBox, acCheckBox
            If (ctl.Name <> "txtTst") Then
                ctl.Visible = Not ctl.Visible
                ' toggling the visible property
            End If
    End Select
Next ctl

But, what if one would like to perform such from a main form having subforms? Well, a subform is also a control, so (with this extra control - txtTst - in all forms/subforms) one could call a recursive sub, something like this from the main form:

call rvsToggleProperties(Me)

The sub might look something like this:

Public Sub rvsToggleProperties(frm As Form)
    Dim ctl As Control
    Frm("txtTst").SetFocus
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
            Case acComboBox, acListBox, acTextBox
                If (ctl.Name <> "txtTst") Then
                    ctl.Visible = Not ctl.Visible
                    ' toggling the visible property
                End If
            Case acSubform
                Call rvsToggleProperties(ctl.Form)
        End Select
    Next ctl
End Sub

One could also pass other information to this routine, for instance False or True, so that the routine sets the control property to what's passed to the routine.

call rvsToggleProperties(Me, False)

- sending the ôchangeö value and use that in assigning the property value

Public Sub rvsToggleProperties(frm as form, bChng as Boolean)
...
    ctl.visible = bChng

3. Toggle properties based on grouping/tag sample
Lets say one wants to toggle only certain controls, for instance some controls should be enabled based on some criteria, some others not, whilst the rest of the controls should remain ôordinaryö.

All controls have a .Tag property, available both in design view and at runtime. This property is not used by Access, so it is available for usage in for instance determining which controls to perform actions on in form looping.

To make this simple, a checkbox named chkCrit is placed on the form. The values of it, can be either True or False.

Then some of the controls have their .Tag property value set to 1, some to 2, the rest have no value in the .Tag property. (to find the .Tag property in design view, open the Properties Dialog, find the ôotherö tab, and it should be the ôlastö property available (at the bottom)).

Dim ctl As Control
Dim chkVal As Boolean
chkVal = Me("chkCrit").Value
For Each ctl In Me.Controls
    If ((ctl.ControlType = acTextBox) And _
        (Len(ctl.Tag & vbNullString) > 0)) Then

        If chkVal Then
            ctl.Enabled = (Val(ctl.Tag) = 1)
        Else
            ctl.Enabled = (Val(ctl.Tag) = 2)
        End If
    End If
Next ctl

Here using what's stored in the .Tag property and the chkValue to toggle the .Enabled property of the controls.

4. Using the .Tag property when validating
Can looping thru the form controls also be used for validation? IÆd say yes. The requirements of the control, form and application should determine how to validate your system, but IÆll have a go at a limited function that could be a starting point for a general validation routine.One of the methods of validation that I really dislike, is the type where a message box pops up per each control not meeting the criterion, and youÆll have to ôclick thruö umpteen boxes to be able to correct. I would really like to know which controls not meeting which criterion, presented in a user friendly way in a form or message box.

The following sample assumes unbound controls, but can be used also on bound controls. Just remember that when a control bound to for instance a date field or a numeric field doesnÆt contain a valid date or number, then one of the form errors will probably pop up. Depending on setup of other events, this routine would then probably not be invoked before the value in the control meets the requirements of the underlying field. IÆll limit it to only controls bound to or ôdesignedö for Text and Dates. The general principles for expanding on the routine should still be there (I hope). In this case, IÆd say the forms Before Update event would probably be a reasonable event to place the call.

Now, as a developer, IÆm using a naming convention on all my objects that would probably confuse the user more than inform (no spaces, abbreviationsà). So how does one provide a general routine with the needed information? Again, the answer might just be the .Tag property.

What information would a general routine need? The name of the control (in a user friendly way), the datatype it should be evaluated against (would be important at least when using unbound controls).

LetÆs say that among all the controls on the form, two controls need validation. txtLName and txtStDate. For only those two controls, specify the .Tag property from the forms design view.

Here IÆll also add a little parameter. True or False, which in the routine will determine whether whatÆs displayed to the user is the text in the .Tag property, or what is found in the controls attached label, if it exists.

txtLName: Last name;Text;True
txtStDate: Start Date;Date;False

Using semicolon (;) to separate the arguments, and within the function, using the Split function available for versions 2000+ of access (for previous versions, check out the ôreplacementö functions in RickSprÆs faq FAQ705-4342)

Call it for instance like this:

Dim strMsg As String
strMsg = rvsValidate(Me)
If Len(strMsg) > 0 Then
    strMsg = "There where some errors..." & vbNewLine & vbNewLine & _
             strMsg & & vbNewLine & vbNewLine & "Please correct"
    MsgBox strMsg, , "Some validation errors..."
End If

The function:

Public Function rvsValidate(frm As Form) As String
Dim ctl         As Control
Dim ctl2        As Control
Dim strProp()   As String
Dim strMsgTmp   As String

For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox
            If (Len(Trim$(ctl.Tag & vbNullString)) > 0) Then
                ' check for contents in the .Tag property
                strProp = Split(ctl.Tag, ";")
                ' fetch the properties into an array
                If (UBound(strProp) > 0) Then
                    ' don't do anything unless there are at
                    ' least two arguments passed in the
                    ' .Tag property

                    If (UBound(strProp) > 1) Then
                        ' If there's a value in the "label
                        ' part", then investigate. If true,
                        ' then there's an attempt to collect
                        ' the text from the assosiated label
                        If (strProp(2) = "True") Then
                            ' This part collects the caption of
                            ' an assosiated label, if it exists
                            ' if so, replacing the name passed
                            ' in the .Tag property
                            If (ctl.Controls.Count > 0) Then
                                For Each ctl2 In ctl.Controls
                                    If (ctl2.ControlType = acLabel) Then
                                        strProp(0) = ctl2.Caption
                                        Exit For
                                    End If
                                Next ctl2
                            End If
                        End If
                    End If

                    ' Now - the validation starts..., first Text

                    If strProp(1) = "Text" Then ' just test for Null
                        If (Len(Trim$(ctl.Value & vbNullString)) = 0) Then
                            strMsgTmp = strMsgTmp & vbTab & strProp(0) & _
                                " can't be Null, enter a value!" & vbNewLine
                        End If
                    End If

                    ' then Date
                    If strProp(1) = "Date" Then ' test for null and IsDate
                        If (Len(Trim$(ctl.Value & vbNullString)) > 0) Then
                            If Not IsDate(ctl.Value) Then
                                strMsgTmp = strMsgTmp & vbTab & _
                                    strProp(0) & " contains a non valid " & _
                                    " date, please amend!" & vbNewLine
                            End If
                        Else
                            strMsgTmp = strMsgTmp & vbTab & strProp(0) & _
                                " can't be Null, enter a value!" & vbNewLine
                        End If
                    End If
                Else
                    ' if there's another control having someting
                    ' in the .Tag property that doesn't qualify
                    ' (can't be split). Could perhaps just drop this?
                    strMsgTmp = strMsgTmp & vbTab & ctl.Name & _
                        " misses at least one arguement in the" & _
                        " .Tag property!" & vbNewLine
                End If
            End If
        Case Else
            ' dont' do anything on controls without
            ' parameters in the .Tag property
    End Select
Next ctl
rvsValidate = strMsgTmp
End Function

5. ôControls Arrayö sample
I've read several places that Access/VBA doesn't support controls array, but since I don't program in VB, I haven't a clear understanding of what a ôControls Arrayö is, but the following is supposed to be an alternative to use in Access.

First, create the number of text controls you need. In this sample, I use 6. Name them all "txtTest" followed by a number.  Here using numbers 1 thru 6. To perform actions on those, one could use a for loop:

Dim lngCount As Long
Dim strControl As String
strControl = "txtTest"
For lngCount = 1 To 6
    Me(strControl & lngCount).Value = lngCount
Next lngCount

Here, only adding the number of the counter to the text control.

6. Carry over values to new record (using DefaultValue)
A frequently asked question on this site is, ôhow to carry over values to a new record?ö. Most answers to that, relies on using the .DefaultValue property of controls, so that the new record defaults to those values. Here's a sample on doing this thru looping thru the form controls.

Using the before mentioned .Tag property, but a bit simplified. If there's a value in the .Tag property, the controls .DefaultValue property is set.

Dim ctl As Control
For Each ctl In Me.Controls
    If ((ctl.ControlType = acTextBox) Or _
        (ctl.ControlType = acComboBox) Or _
        (ctl.ControlType = acListBox) Or _
        (ctl.ControlType = acCheckBox)) Then

        If (Len(ctl.Tag & vbNullString) > 0) Then
            Select Case True
                Case IsNull(ctl.Value)
                    ' If no value, don't change default value
                    ' this will need to be tweaked according to
                    ' requirements. Perhaps use "", so that the
                    ' ôoldö default value disappearsà
                Case IsDate(ctl.Value)
                    ' Dates - hmmmm - because of different
                    ' regional settings (differing
                    ' from US date format) it is sometimes
                    ' necessary to do some formatting
                    ctl.DefaultValue = "#" & _
                        Format(ctl.Value, "yyyy-mm-dd") & "#"
                Case IsNumeric(ctl.Value)
                    ' No formatting for numbers
                    ctl.DefaultValue = ctl.Value
                Case Else
                    ' Then it should be text, four double quotes...
                    ctl.DefaultValue = """" & ctl.Value & """"
            End Select
        End If
    End If
Next ctl

7. Changing properties of attached labels
When the controls have attached labels, the .Caption of the label can be retrieved like this:

Debug.Print Me!txtBox.Controls(0).Caption

Using this, one can toggle the properties of the attached labels. In this sample toggling first the .Enabled AND the .Locked properties of the control, then several properties of the attached labels, for the controls having an attached label:

Dim ctl As Control
Application.Echo False
For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            ' Toggling enabled and locked properties of
            ' the controls itself
            ctl.Enabled = Not ctl.Enabled
            ctl.Locked = Not ctl.Locked
            If ctl.Controls.Count > 0 Then
                ' Toggling properties of the attached
                ' labels, if there are any
                If ctl.Locked Then
                    ctl.Controls(0).BackStyle = 0 ' Transparent
                    ctl.Controls(0).BackColor = 0
                    ctl.Controls(0).ForeColor = 0
                    ctl.Controls(0).BackColor = 0
                    ctl.Controls(0).FontBold = False
                    ctl.Controls(0).BorderStyle = 1
                    ctl.Controls(0).BorderColor = 0
                Else
                    ctl.Controls(0).BackStyle = 1 ' Normal
                    ctl.Controls(0).BackColor = vbBlack
                    ctl.Controls(0).ForeColor = vbWhite
                    ctl.Controls(0).FontBold = True
                    ctl.Controls(0).BorderStyle = 1
                    ctl.Controls(0).BorderColor = vbWhite
                End If
            End If
        Case Else
            ' Dont' do anything?
    End Select
Next ctl
Application.Echo True

The ctl.Controls.Count > 0 ensures this is only attempted on controls having a label attached, for another way of testing, see the sample on validation (section 5).

Usage of Application.Echo can be dangerous. IÆd advice to implement some error handling routine and apply Application.Echo True in the exit part, else there might be headaches.

In my opinion usage of such methods as demonstrated here, might save both a bit of development time, number of code lines and increase the readability.

Do you have any suggestions on improving this faq, don't hesitate to send a comment.
Special thanks to CajunCenturion for suggestions.

Good Luck in the form control looping!

04/05/2004 û submitted
07/17/2004 û edited; typos, layout, new validation section after the original .Tag sample

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

Microsoft: Access Forms FAQ

Form Basics

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!