Option Groups...
With all the inquiries about Option Groups I decided it might be helpful to have some basic examples. In this sample file (click here) I have included the examples shown below of what you can do with Option Groups.
The difficult I do immediately, the impossible takes a little bit longer.
Controls
Change Background Color
Change Label Background Color
Open Report or Form (Bound and Unbound)
Use as a Form Filter
Private Sub fraExample1_AfterUpdate()
'From https://access-programmers.co.uk/forums/showthread.php?t=301699
With Me.fraExample1
.BackStyle = 1
.BackColor = vbWhite
If .Value = 1 Then
.BackColor = RGB(34, 177, 76) 'Green
ElseIf .Value = 2 Then
.BackColor = RGB(249, 238, 97) 'Yellow
ElseIf .Value = 3 Then
.BackColor = RGB(255, 0, 0) 'Red
End If
End With
End Sub
The first example, inspired by a post in Access World Forums, changes the background color of the control based on which selection you make.
Private Sub fraExample2_AfterUpdate()
With Me.fraExample2
.BackStyle = 1
.BackColor = vbWhite
If .Value = 1 Then
Me.lblOption1.BackColor = RGB(249, 238, 97) 'Yellow
Me.lblOption2.BackColor = RGB(255, 255, 255) 'White
Me.lblOption3.BackColor = RGB(255, 255, 255) 'White
ElseIf .Value = 2 Then
Me.lblOption2.BackColor = RGB(250, 157, 20) 'Orange
Me.lblOption1.BackColor = RGB(255, 255, 255) 'White
Me.lblOption3.BackColor = RGB(255, 255, 255) 'White
ElseIf .Value = 3 Then
Me.lblOption3.BackColor = RGB(84, 222, 123) 'Green
Me.lblOption1.BackColor = RGB(255, 255, 255) 'White
Me.lblOption2.BackColor = RGB(255, 255, 255) 'White
End If
End With
End Sub
This example changes just the Label color. While the code below shows how to do that, download the sample file to see how to *reset* it when going to a new record.
There are two different examples for Option Group 3 showing the difference in code when using a BOUND Option Group and an UNBOUND Option Group.
Private Sub fraExample3_AfterUpdate()
With Me.fraExample3
If .Value = 1 Then
DoCmd.OpenReport "rpt1", acViewPreview
DoCmd.Maximize
ElseIf .Value = 2 Then
DoCmd.OpenReport "rpt2", acViewPreview
DoCmd.Maximize
ElseIf .Value = 3 Then
DoCmd.OpenForm "frmLegend"
End If
End With
End Sub
Private Sub fraExample3_AfterUpdate()
Select Case Me.fraExample3
Case 1
DoCmd.OpenReport "rpt1", acViewPreview
DoCmd.Maximize
Case 2
DoCmd.OpenReport "rpt2", acViewPreview
DoCmd.Maximize
Case 3
DoCmd.OpenForm "frmLegend"
End Select
End Sub
Private Sub fraExample4_AfterUpdate()
Select Case Me.fraExample4
Case 0
Me.FilterOn = False
Case 2
Me.Filter = "[ogStatusID] = 2"
Me.FilterOn = True
Case 3
Me.Filter = "[ogStatusID] = 3"
Me.FilterOn = True
Case 1
Me.Filter = "[ogStatusID] = 1"
Me.FilterOn = True
End Select
End Sub
And the final example shows how to filter a Continuous Form based on the Option Group selection.