The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
October 2008

Option Groups 103 - Reacting to User Input

It's fall in New England and it's absolutely gorgeous! The leaves have changed from a rich, dark green to a mix of golden red, orange and yellow tones. It's as if an artist has come along and brushed the oaks, maples, and birches to turn the countryside into a painting. It's a truly amazing sight.

The leaves, which once exhibited their green color as they turned the sun's rays into chlorophyll to feed the tree, now begin to drop to the ground where they will provide food for the tree and other plants as they decompose.

In the same way that the leaves on the trees serve more than one purpose, an option group can do more than one task. This month we'll continue our review of option groups and take a look at an unbound option group that reacts to user input.

In this Issue
  • Shameless Self-Promotion Department
  • Background
  • A Specific Use
  • The Nitty Gritty of Reacting to the Option Group Selection
  • Conclusion
  • Tip Of The Month: Easily Add Multiple Controls Of The Same Type.

  • Background

    To review, option groups provide the user with a small set of choices to select from. It's an excellent tool to allow a user to manipulate the data shown on the screen.

    In the July and August Wizards, I reviewed the creation of option groups and how to bind them to a field in a table or query. Although binding an option group gives great advantage, simply doing that doesn't begin to utilize the power that's available.

    A Specific Use

    Below we have a design view of the option group that we've been using over the last few months.

    Take a look at the properties of the option group which I've named grpDepts. You can see that the control source is currently blank.

    In order to bind the option group to a field on a form we would fill in the control source. If we leave the control source blank, then the option group remains unbound (which doesn't mean that we can't use it, just that its value will not be stored). An unbound option group may still be used to react to the user requests and allow to the user to make choices such as filtering records on a form.

    Consider the case of a Summer Camp where we have a form that lists campers who are signed up for a number of activities for the day. The form shows the campers, their selected activities and the department that each activity is in. If we put this unbound option group on a form we can use it to filter our results. For instance, if the user chooses land sports, we show only those activities that are in the land sports department.

    The Nitty Gritty of Reacting to the Option Group Selection

    Option groups have events, just as other controls have events. By opening your form in design view and double-clicking on the option group, you'll get to the properties of the option group. When you click on the events tab, you can see the events available to the option group. The one that we'll use is the After Update event.

    The After Update event for the option group is the code that runs after the user has made a selection of an option within the option group. Click on the After Update event and then click on the ellipsis and you'll go to the code window where you'll find that the shell for the After Update event has been created for you.

    Below I have filled in some of the code that will be called when the user makes a selection within the option group.

    Private Sub grpDepts_AfterUpdate()
      Select Case Me.grpDepts
        Case Is = 1
        'Filter to Land Sports
        Case Is = 2
        'Filter for water sports
        Case Is = 3
        'Filter for Arts and Crafts
        Case Else
        'Handle the unexpected case
       End Select
    End Sub

    In order to react to the event, we're using the select case method, which I reviewed in the September 2008 Wizard.

    Essentially, the code says: Take a look at the option group. If the user has selected the first option, whose value is one, then take the action of filtering to land sports. You'll note that I haven't actually created the code to filter the form, but rather have just made a comment to indicate what will happen. I'll be covering how to filter a form with code in a future Access Wizard.

    In a like way, if the user chooses the second option, the form will be filtered for water sports and so on. Also note that I have included a Case Else statement to handle an unexpected selection. Although there is no such code in the sample above, in an actual application I would have the software show some type of error message that could then aid in debugging.


    This month we've seen that an unbound option group can be a very powerful tool and that one of its uses can be to react to the user request and filter the form.

    Of course we're not limited to filtering our form; you can do whatever you like after the user has made his selection. The next time you want to make your forms flexible and friendlier, give some thought to the option group as a way for the user to easily interact with the data.

    Tip Of The Month: Easily Add Multiple Controls Of The Same Type.

    Frequently when you are working with forms you'll be adding several text boxes or combo boxes. Most likely you'll open the control toolbox, click on the control that you want to add, and then click on the form. For the next instance of that same control, you'll repeat the process; if you want to add five controls you'll repeat the process five times.

    There's an easier way to do this. Rather than single clicking on the control in the control toolbox, double- click on the control in the control toolbox. Then click in your form wherever you want the control to land, and then immediately click again on the form. You'll find that your control gets repeated for each click within the form. When you're done, click on the control in the control toolbox once more to deselect it.

    Essentially, when you double-click a control in the control toolbox, it will stay selected until you click it again to deselect it. This is a nifty little tip that can save you time when creating multiple instances of the same control.

    Shameless Self-Promotion Department

    If you're reading this before October 14, I'd like to invite you to the Access Developer's Group Meeting in Waltham. I'll be giving a presentation on managing multiple lists in Access with a common list engine at the developer's session in the evening.

    See htt p://www.bostonaccessday.com/index.cfm for details on the location and the agenda.

    If you're a developer, you'll pick up a valuable tool that will empower your end users to manage existing lists without intervention from you.

    If you're a power user, you'll learn a technique that will help you streamline your applications by setting up a system that allows you to add list elements as well as new lists without adding additional code or tables to your applications.

    This event is free and open to the public, so if you're in the area, please drop bye and say "hi".

    Quick Links...

    If you can't see all the Pictures Click Here for Online Version

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    Safe Unsubscribe
    This email was sent to jim@custom-software.biz by jim@custom-software.biz.

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886