Apply Filters on Multiple Fields 2

[OzzieTheOwl]

Hi

I am trying to apply a filter to a form using VBA, I need to the filter to work off ComboBox values the user has selected, and then apply the filter to several fields depending on what the user requests. I have written the code below, which works fine for one parameter, but when I select 2 parameters I get the following error message

Run-time error '2001'

You cancelled the previous operation.

Private Sub cmdApplyFilter_Click()
Dim txtRegFilter
Dim txtDateFilter
Dim txtProjectFilter
Dim txtTaskFilter
    txtRegFilter = Form_Information.cmbRegSelect.Value
    txtDateFilter = Form_Information.cmbWeekSelect.Value
    txtProjectFilter = Form_Information.cmbProjectSelect.Value
    txtTaskFilter = Form_Information.cmbTaskSelect.Value
    If txtRegFilter <> "" Then Me.Form.Filter = "[Reg Number]='" & txtRegFilter & "'"
    If txtDateFilter <> "" Then Me.Form.Filter = "[Week]='" & txtDateFilter & "'"
    If txtProjectFilter <> "" Then Me.Form.Filter = "[Project Code]='" & txtProjectFilter & "'"
    If txtTaskFilter <> "" Then Me.Form.Filter = "[Task Number]='" & txtTaskFilter & "'"
    Me.FilterOn = True
    Form_Information.cmbRegSelect.Value = ""
    Form_Information.cmbWeekSelect.Value = ""
    Form_Information.cmbProjectSelect.Value = ""
    Form_Information.cmbTaskSelect.Value = ""
End Sub

I am probably missing something simple, any help would be appreciated.

Thanks

Barney

 

[rjoubert]

You can try AND'ing your filter criteria, and filtering the form just once, depending on what is selected.

Try something like this...

Dim strFilter as String

strFilter = "1=1"

If txtRegFilter <> "" Then
  strFilter = strFilter & " AND [Reg Number]='" & txtRegFilter & "'"
End If

If txtDateFilter <> "" Then 
   strFilter = strFilter & " AND [Week]='" & txtDateFilter & "'"
End If

If txtProjectFilter <> "" Then 
   strFilter = strFilter & " AND [Project Code]='" & txtProjectFilter & "'"
End If

If txtTaskFilter <> "" Then 
   strFilter = strFilter & " AND [Task Number]='" & txtTaskFilter & "'"
End If

Me.Form.Filter = strFilter

 

[OzzieTheOwl]

Thanks Mate

Worked perfectly.

Have a Star on Me

Cheers

Barney