databasedev.co.uk - database solutions and downloads for microsoft access

Using an Option Group to Filter a Microsoft Access Form

What is a Microsoft Access Option Group Control?

You can use an option group on a Microsoft Access form, report, or data access page to display a limited set of alternatives.

An option group makes selecting a value easy because you can just click the value that you want.
Only one option in an option group can be selected at a time.

In a form or report, an option group consists of a group frame and a set of check boxes, option buttons, and toggle buttons.

The following example uses an option group, that when a value is selected, the records can be filtered to only display the selected options associated records.

Below we see the completed form, displaying the records in the form, and also the option group that will be used for filtering the records displayed:

Completed form, showing all records, and including option group to apply filtering
Completed form, showing all records, and including option group to apply filtering

The form is based upon the following Record Source:

SELECT tblStudentInformation.strCourseID,
            tblStudentInformation.strStudentID,
            tblStudentInformation.strFirstName,
            tblStudentInformation.strLastName,
            tblStudentInformation.strCity,
            tblStudentInformation.strCounty FROM tblStudentInformation;

When one of the options is selected and the user clicks on the Filter Records button, the record source for
the form is replaced by using the following Select Case statement:

Private Sub cmdFilterRecords_Click()

'Variable to hold filtered SQL string
Dim strFilterSQL As String

'Set default record source of form
Const strSQL = "SELECT tblStudentInformation.strCourseID,
        tblStudentInformation.strStudentID,tblStudentInformation.strFirstName,
        tblStudentInformation.strLastName,tblStudentInformation.strCity,
        tblStudentInformation.strCounty FROM tblStudentInformation"

    Select Case Me!optFilterBy
    'Filter record source dependant on option checked
        Case 1
            strFilterSQL = strSQL & " Where [strCourseID] = 'MM';"
        Case 2
            strFilterSQL = strSQL & " Where [strCourseID] = 'PROG';"
        Case 3
            strFilterSQL = strSQL & " Where [strCourseID] = 'PCS';"
        Case 4
            strFilterSQL = strSQL & " Where [strCourseID] = 'MCSD';"
        Case 5
            strFilterSQL = strSQL & " Where [strCourseID] = 'ISD';"
        Case 6
            strFilterSQL = strSQL & " Where [strCourseID] = 'TSE';"
    'If filter applied with no option selected use default record source
        Case Else
            strFilterSQL = strSQL & ";"
    End Select
    
' Set record source with filtered SQL
    Me.RecordSource = strFilterSQL
    Me.Requery

End Sub

Depending on the option selected, the WHERE condition is added to the initial SQL statement, which then limits the results returned.

For example, selecting the PROG option will return only the following records:

Form, showing filter applied using the Option Group
Form, showing filter applied using the Option Group

Using the Remove Filter command button, will once again reset the record source of the form to that of the original. The code used for this is:

Private Sub cmdRemoveFilter_Click()
' Restores the Record Source back to that of
' of the original form
    Me.RecordSource = strSQL & ";"
End Sub

To see an example of this, please download the Microsoft Access 2000 database from the Microsoft Access Forms page or from the Microsoft Access Downloads section.