How to Filter Form with Option Group linked to Combo Box

[fargus47]

Hi,



I am trying to filter a form utilizing an option group that filters the results of an unbound combo box in the form's header (the combo box assists the user in quickly locating a name). I've tried many variations of a Select Case Statement I found on a forum and just keep getting more confused. Thank you in advance for any help you can offer!

Private Sub fraAdmitStatusOptionGroup_AfterUpdate()
' filters residents' records in combo box based on selection in option group box


    Dim strCriteria As String
    Dim strSQL As String
 
    
    Select Case Me!fraAdmitStatusOptionGroup
        'Me! refers to frame/option group name


        Case "Active"
            ' Option button Active = 1
             strCriteria = "Active"
'
        Case "Bed Hold"
            ' Option button Bed Hold =2
            strCriteria = "Bed Hold"
'
        Case "Expired"
            ' option button Expired =3
            strCriteria = "Expired"
'
        Case "Discharged"
            ' option button Discharged = 4
            strCriteria = "Discharged"
'
        Case "LOA"
            ' option button LOA (leave of absence) = 5
            strCriteria = "LOA"
'
        Case "Potential"
            ' option button Potential = 6
            strCriteria = "Potential"
'
    End Select
    
        strSQL = "SELECT [tbl_Clinical_Residents].Resident_ID, [tbl_Clinical_Residents].Last_Name, " & _
                "[tbl_Clinical_Residents].First_Name, [tbl_Clinical_Residents].Admission_Number, " & _
                "[tbl_Clinical_Residents].Admit_StatusID, FROM tbl_Clinical_Residents WHERE " & _
                "[tbl_Clinical_Residents].Admit_StatusID = '" & strCriteria & "';"
        Debug.Print strSQL
        


'    'Sets combo box row source equal to SQL select statement
    Me!cboSearchByLastName.RowSource = strSQL
End Sub

[Bob Fitz]

Perhaps this will work:

Select Case Me!fraAdmitStatusOptionGroup
        'Me! refers to frame/option group name


        Case 1
            ' Option button Active = 1
             strCriteria = "Active"
'
        Case 2
            ' Option button Bed Hold =2
            strCriteria = "Bed Hold"
'
        Case 3
            ' option button Expired =3
            strCriteria = "Expired"
'
        Case 4
            ' option button Discharged = 4
            strCriteria = "Discharged"
'
        Case 5
            ' option button LOA (leave of absence) = 5
            strCriteria = "LOA"
'
        Case 6
            ' option button Potential = 6
            strCriteria = "Potential"
'
    End Select
    
        strSQL = "SELECT [tbl_Clinical_Residents].Resident_ID, [tbl_Clinical_Residents].Last_Name, " & _
                "[tbl_Clinical_Residents].First_Name, [tbl_Clinical_Residents].Admission_Number, " & _
                "[tbl_Clinical_Residents].Admit_StatusID, FROM tbl_Clinical_Residents WHERE " & _
                "[tbl_Clinical_Residents].Admit_StatusID = '" & strCriteria & "';"
        Debug.Print strSQL
        


'    'Sets combo box row source equal to SQL select statement
    Me!cboSearchByLastName.RowSource = strSQL
End Sub

[fargus47]

Hi Bob,

Thank you for the quick response! When I the code, I'm still getting an error "The SELECT statement includes a reserved word or an argument that is misspelled or missing, or the punctuation is incorrect". Any ideas?

Thanks!