Form - looping thru the controls

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 [tt]controltype[/tt] 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 [tt]On Error Resume Next[/tt].

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

[tt]For Each ctl In Me.Controls[/tt]

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:

[tt]For Each ctl In Me.Detail.Controls
For Each ctl In Me.Section("Detail").Controls[/tt]

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:

[tt]For Each ctl In Me.Section(acHeader).Controls
For Each ctl In Me.Section(acDetail).Controls[/tt]

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:

[tt] For Each ctl In Me("MyTab").Pages("Page1").Controls [/tt]

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
[ol][li]ôClear the formö samples[/li]
[li]Enable/Lock/Visible properties samples[/li]
[li]Toggle properties based on grouping/tag sample[/li]
[li]Using the .Tag property when validating[/li]
[li]ôControls Arrayö sample[/li]
[li]Carry over values to new record (using DefaultValue)[/li]
[li]Changing properties of attached labels[/li][/ol]


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:

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

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:

[tt]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[/tt]

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

[tt]If (Len(Trim$(ctl.ControlSource & vbNullString)) = 0) Then[/tt]

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.

[tt]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[/tt]

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...

[tt]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[/tt]

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:

[tt]call rvsToggleProperties(Me)[/tt]

The sub might look something like this:

[tt]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[/tt]

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.

[tt]call rvsToggleProperties(Me, False)[/tt]

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

[tt]Public Sub rvsToggleProperties(frm as form, bChng as Boolean)
...
ctl.visible = bChng[/tt]

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)).

[tt]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[/tt]

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:

[tt]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 [/tt]

The function:

[tt]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[/tt]

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:

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

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.

[tt]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[/tt]


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

[tt]Debug.Print Me!txtBox.Controls(0).Caption[/tt]

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:

[tt]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 [/tt]

The [tt]ctl.Controls.Count > 0[/tt] 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 [tt]Application.Echo[/tt] can be dangerous. IÆd advice to implement some error handling routine and apply [tt]Application.Echo True[/tt] 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