Automating Microsoft Access with VBA

Option groups, of course, are controls that can contain other controls. Specifically, you can put any of these controls inside of an option group by dropping the control on the option group when you're designing the form:

• Check box

• Option button

• Toggle button

Only one control within an option group can be selected at a time. When you click on one option button, for example, it becomes selected and all the other controls in the option group become deselected. Each control in the option group has its own Option Value property, and the Value property of the option group is equal to the Option Value of the selected control.

TIP

Because only one control in an option group can be selected at a time, they don't work well when you have a series of check boxes and want to allow the users to check more than one at the same time. In that case, you can construct a fake option group by positioning the check boxes inside of a rectangle control.

None of the fields in the TimeTrack database is especially suited for representation by an option group, so we've constructed the Chapter13OptionGroup form to show you some useful code. Figure 13.3 shows this form.

The code behind this form shows you how to perform three tasks:

• Report the value of the option group in an unbound text box

• Set the value of the option group by entering a value in the text box

• Disable all the option buttons in the option group as a single operation

To handle the first of these tasks, you have to catch the AfterUpdate event of the option group, which is fired whenever the users click one of the option buttons:

Private Sub grpOption_AfterUpdate() ' Show the option group value ' in the text box txtValue = grpOption End Sub

The line of code that does the assignment from the option group to the text box uses the fact that each control has a default property. When you specify a control name in a context where only a property makes sense, VBA uses this property. For both the option group and the text box, the default property is the Value property, so this bit of code assigns the value of one to the value of the other.

To handle the second requirement, you need code attached to the Change event of the text box, so that it takes effect whenever the users type a character:

Private Sub txtValue_Change() ' Update the option group ' from the text box On Error Resume Next grpOption = CInt(txtValue.Text) End Sub

The On Error Resume Next statement takes care of the case where the users type something nonsensical, such as q, in the text box. In that case, the code simply leaves the option group alone.

Finally, disabling all the option buttons at once introduces several new concepts:

Private Sub cmdDisable_Click() ' Disable the entire option group Dim ctl As Control For Each ctl In grpOption.Controls If ctl.ControlType = acOptionButton Then ctl.Enabled = False End If Next ctl End Sub

Controls that contain other controls (like the option group) have their own Controls collection. By using a For Each loop, the VBA code visits each control in this collection in turn when the users click the button. But if you look at Figure 13.3, you'll see that the option group contains both label controls and option button controls. Trying to disable a label control raises a runtime error. Rather than simply suppress the errors, this code takes a more elegant approach. Every control on an Access form or report has a ControlType property that returns a constant indicating the type of the control. So this loop steps through all the controls, tests each one to determine whether it is an option button, and then disables the control only if it is an option button.