Grouping form fields together

Posted on 2012-08-13
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

    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 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    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 119

    Accepted Solution

    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

    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

    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

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now