Grouping form fields together

Posted on 2012-08-13
Medium Priority
Last Modified: 2012-08-13
I have a form where I wish to "expose" certain fields dependant on the value entered in a combo box.
I have the code working fine, but there are several fields for each option and I wondered if there was any way to group fields together (with a name designation) so that the code can sort of say:

If cboBox = "Value" Then
FieldGroup.BackColor = -2147483633
End If

or something like that.
Just thinking about readability of the code and ease of maintenance.


Question by:rltomalin
LVL 61

Assisted Solution

mbizup earned 668 total points
ID: 38287553
Controls have a "Tag" property that you can add any text to in order to specify the controls in your group.

Try this:

1.  Set the tag property of the controls in the group to "Group A"
2.  Add the following code to the After Update event of your combo box and the current event of the form:

Dim ctl as control
    for each ctl in Me.Controls
          if ctl.Tag = "Group A" then
             If me.cboBox = "Value" then
                  ctl.backcolor =  -2147483633
                  ctl.backcolor =  something else
             end if
         end if

Open in new window

LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 664 total points
ID: 38287556
not sure what you mean by "group together".  Are you implying that depending on the value of the combo, certain fields should be enabled or disabled?

If so, you could do this via a subform control, and have separate subforms for the various values of your combo box.

Or, you could simply use code similar to the following:
Private Sub cbo_yourCombo_AfterUpdate

    me.control1.Enabled = IsIn(me.cbo_yourCombo.value, "test1", "test2")
    me.control2.enabled = IsIn(me.cbo_yourCombo.value, "test1", "test3")
    me.control3.Enabled = IsIn(me.cbo_yourCombo.value, "test2", "test3")
    me.control4.enabled = IsIn(me.cbo_yourCombo.value, "test4")

End Sub

Open in new window

This enables and disables controls at the same time by returning either a true or false from the function IsIn(), which operates under the same principle as the SQL In() clause, but requires that you pass it the value to test, then an array of values to test it against.  It looks like:
Public Function IsIn(TestValue As Variant, ParamArray ArrayOfValues() As Variant) As Boolean

    'Tests to see whether a value is in a list of other values.
    'Similar to both the SQL IN ( ) clause and SELECT Case statements
    'Accepts a value of Variant data type, followed by an array of values.
    'Checks to see if the first value is in the array.
    Dim intLoop As Integer
    IsIn = False
    If IsNull(TestValue) Then Exit Function
    For intLoop = LBound(ArrayOfValues) To UBound(ArrayOfValues)
        If TestValue = ArrayOfValues(intLoop) Then
            IsIn = True
            Exit For
        End If
End Function

Open in new window

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 668 total points
ID: 38287568
you can use the Tag property of the controls, use tags that you can identify with the values selected from the combo box.. you then use this codes

dim ctl as control

for each ctl in me.controls
    if ctl.tag="somevalue" then
   end if

Author Closing Comment

ID: 38287588
Hello Guys
Wow - that was quick.  I have two solutions here that I will checkout and select which I would prefer.
Thanks for your excellent (as always) support.


LVL 61

Expert Comment

ID: 38287630
Another way to do this with logically grouped controls is to define the group with a consistent naming convention using sequential numbers, and loop through the controls based on name.

For example, name a group of textboxes txtThing01, txtThing02, txtThing03 ... etc, and use code like this:

Dim I as integer

For  I = 1 to 10 
             If me.cboBox = "Value" then
                  Me.Controls("txtThing" & Format(I,"00")).backcolor =  -2147483633
                  Me.Controls("txtThing" & Format(I,"00")).backcolor =  something else
             end if

Open in new window


Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question