Use Option Group to Filter Combo Box Values

[dgj32784]

I have five options in an option group: [A-E], [F-J], [K-O], [P-T], [U-Z]. These "ranges" represent alphabetical ranges that I would like combo box values to be "filtered" on. The combo box is a listing of counties that occur within the state. If the user hits the option [A-E], I'd like the combo box to only display counties that begin with a letter between A and E. How do I accomplish this with VBA? Is this a select case scenario? It would be easy to get the first letter of the county using Left, I just don't know how to take that value and have it "filter" the combo box choices.

Any pointers would be appreciated.

Thanks,


David

[Bob Fitz]

I think I would use select case to get the value of the option group and then set the Row Source of the combo box.
To filter the combo box, include a WHERE statement.
Something like: WHERE (((TableName.FieldName) Between "A" And "Ez"))

[dgj32784]

Thanks for the advice Bob. I toyed with getting your suggestion to work for a while and then ended up going with the following code to serve the purpose following feedback from another source.

 
Private Sub grpCountyListCategories_AfterUpdate()
 
    Dim strCriteria As String
    Dim strSQL As String
 
    'code to narrow the county option group values by first letter
    Select Case Me!grpCountyListCategories
        Case 1
            'code to define variable value between A - E
            strCriteria = "'[A-E]'&'*'"
        Case 2
            'code to define variable value between F - J
            strCriteria = "'[F-J]'&'*'"
        Case 3
            'code to define variable value between K - O
            strCriteria = "'[K-O]'&'*'"
        Case 4
            'code to define variable value between P - T
            strCriteria = "'[P-T]'&'*'"
        Case 5
            'code to define variable value between U - Z
            strCriteria = "'[U-Z]'&'*'"
        Case 6
            'code to define variable value between A - Z
            'sets combo box values back to full list
            strCriteria = "'[A-Z]'&'*'"
    End Select
 
    'SQL statement to select values from the query that fall within the ranges specified in option group cases listed above
    strSQL = "Select strCountyName From qryNorthCarolinaCounties Where Left ([strCountyName],1) Like(" & strCriteria & ");"
 
    'Sets combo box row source equal to SQL select statement
    Me!cboCountyName.RowSource = strSQL
 
End Sub

Thanks again for your post though. It got me thinking more about the workflow.