Option buttons don't appear selected in group

[beacon]

Hi everybody,

I have a form that has a combo box that asks the user to select the program they work on. Once the user selects the program, a SQL statement populates the row source for 4 staff member combo boxes.

I have an option group that has 4 option buttons for the user to indicate how many staff members to include. Once the user selects one of the option buttons, the appropriate number of combo boxes become enabled and contain the staff members for the correct program.

All of that works like a charm. The issue that I've run into is that when the user selects one of the option buttons, the option button doesn't appear to be selected, i.e. the option button doesn't turn green. I can tell the buttons work because the combo boxes become enabled and the value of the option buttons is entered on the underlying table.

The option group has a value of zero, staff one has 1, staff two has 2, and so on. If the user attempts to select a staff member before selecting a program, there's code that tells the user that they need to enter a program first and sets the focus on the program field.

Anyway, here's all the code that relates to these fields:

 
Private Sub optionGroup_AfterUpdate()
 
    ' resets the option button if the option button is selected before program
    Me.optionGroup = Me.optionGroup.OldValue
 
    ' set the focus on the program field
    Me.Program.SetFocus
 
End Sub
 
Private Sub optionGroup_BeforeUpdate(Cancel As Integer)
 
    Dim UserChoice
 
    ' check to see if program has been entered...notify the user...go to afterupdate()
    If IsNull(Me.Program) Then
        MsgBox "You must enter a program prior to selecting staff from a particular program", vbExclamation + vbOKOnly, "Program Required"
        Exit Sub
    End If
 
    UserChoice = [optionGroup].Value
 
    ' selects the appropriate number of combo boxes to enable/disable based on user selection
    Select Case UserChoice
    Case 1
        Me.Staff1.Enabled = True
        Me.Staff2.Enabled = False
            If Me.Staff2.Enabled = False Then
                Me.Staff2.Value = Null
            End If
        Me.Staff3.Enabled = False
            If Me.Staff3.Enabled = False Then
                Me.Staff3.Value = Null
            End If
        Me.Staff4.Enabled = False
            If Me.Staff4.Enabled = False Then
                Me.Staff4.Value = Null
            End If
    Case 2
        Me.Staff1.Enabled = True
        Me.Staff2.Enabled = True
        Me.Staff3.Enabled = False
            If Me.Staff3.Enabled = False Then
                Me.Staff3.Value = Null
            End If
        Me.Staff4.Enabled = False
            If Me.Staff4.Enabled = False Then
                Me.Staff4.Value = Null
            End If
    Case 3
        Me.Staff1.Enabled = True
        Me.Staff2.Enabled = True
        Me.Staff3.Enabled = True
        Me.Staff4.Enabled = False
            If Me.Staff4.Enabled = False Then
                Me.Staff4.Value = Null
            End If
    Case 4
        Me.Staff1.Enabled = True
        Me.Staff2.Enabled = True
        Me.Staff3.Enabled = True
        Me.Staff4.Enabled = True
    Case Else
        Me.Staff2.Enabled = False
            If Me.Staff2.Enabled = False Then
                Me.Staff2.Value = Null
            End If
        Me.Staff3.Enabled = False
            If Me.Staff3.Enabled = False Then
                Me.Staff3.Value = Null
            End If
        Me.Staff4.Enabled = False
            If Me.Staff4.Enabled = False Then
                Me.Staff4.Value = Null
            End If
    End Select
 
End Sub
 
 
Private Sub Program_BeforeUpdate(Cancel As Integer)
 
    Dim programVal, strSQL
 
    programVal = Me.Program
 
    ' set a SQL string based on selection in program field
    Select Case programVal
    Case "CAP"
        strSQL = "SELECT[tblStaffCAP].[Staff Name] FROM tblStaffCAP;"
    Case "EEP"
        strSQL = "SELECT[tblStaffEEP].[Staff Name] FROM tblStaffEEP;"
    Case "GPP"
        strSQL = "SELECT[tblStaffGPP].[Staff Name] FROM tblStaffGPP;"
    Case "RIDR"
        strSQL = "SELECT[tblStaffRIDR].[Staff Name] FROM tblStaffRIDR;"
    Case "SBP"
        strSQL = "SELECT[tblStaffSBP].[Staff Name] FROM tblStaffSBP;"
    End Select
 
    ' set the rowsource equal to the SQL string based on program
    Me.Staff1.RowSource = strSQL
    Me.Staff2.RowSource = strSQL
    Me.Staff3.RowSource = strSQL
    Me.Staff4.RowSource = strSQL
 
End Sub
 

It's like the option buttons are reset when the SQL is set. I don't understand if or how I can fix it.

Thanks for the help,
beacon

[beacon]

Nevermind...I figured it out.

My afterupdate() event was resetting the optiongroup value everytime and putting the focus on the program field.

I changed it to the following and everything is working now:

Private Sub optionGroup_AfterUpdate()
 
    If IsNull(Me.Program) Then
        Me.optionGroup = Me.optionGroup.OldValue
        Me.Program.SetFocus
    End If
 
End Sub
 

Sorry for wasting everyone's time.

- beacon

[NeoPa]

No worries Beacon. Thanks for popping back with the solution :)