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
Talk To Other Members