Trying setup this filter on a form. I got this code from someone that was using an option group in a form to filter the data using a regular field. I am trying to setup the same type of filter except i'm usng a field that is basically a combo box where the row source is from another table. The way it is supposed to work is in the form is basically in each record you can select one of four choices for the field using a pulldown. Then you can filter the data to browse all of the records with the selected option using an option group radio button on the form. ther error that comes up when the filter is run is "Run-time error '2001': You canceled the previous operation Also the other difference is that I'm using the query that the Form was built on as the "Const strSQL = "SELECT * FROM " intead of a table as was used in the original code.
The code for the filter is here
Option Compare Database
Option Explicit
'Set default record source of form
Const strSQL = "SELECT * FROM ContractQuery"
Private Sub cmdFilterRecords_Click()
'Variable to hold filtered SQL string
Dim strFilterSQL As String
'Set default record source of form
'Const strSQL = "SELECT * FROM ContractQuery"
Select Case Me!optFilterBy
'Filter record source dependant on option checked
Case 1
strFilterSQL = strSQL & " Where [ContractStatus] = '[To Be Reviewed]';"
Case 2
strFilterSQL = strSQL & " Where [ContractStatus] = '[On Hold]';"
Case 3
strFilterSQL = strSQL & " Where [ContractStatus] = '[Revisions Sent]';"
Case 4
strFilterSQL = strSQL & " Where [ContractStatus] = '[Contract No Fully Executed]';"
'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
Private Sub cmdRemoveFilter_Click()
' This procedure will restore the RecordSource to the original dataset.
Me.RecordSource = strSQL & ";"
End Sub
Start Free Trial
