Filter subform with one or more selections

J

jimster68

I'm trying to filter a subform with multiple combo boxes and eventually some
text fields tied to one command button to execute the search/filter. I want
the user to determine which filter or combination of filters to use.
I started with the combo boxes and can get one to work, however, when I add
the second one I am forced to use both or an error is produced. The error is
Runtime Error '2448' you can't assign a value to this object.
Here is a sample.

Private Sub cmdFilter_Click()
sfrm_Server_Data.Form.Filter = "Loc_ID=" & cboLoc.Value & _
"AND Cab_ID=" & cboCab.Value

sfrm_Server_Data.Form.FilterOn = True

End Sub

If I add a value in both combo boxes everthing works fine but I don't want
to force the use of the second combo box.

How would I write this to allow for one or more filter selections tied to
one command button?

Thanks,
Jim
 
A

Allen Browne

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The code builds up the filter for the form, using only these boxes where the
user entered something. There's a sample database you can download and pull
apart. It illustrates how to work with combos, text boxes, different data
types, and a date range.
 
M

Marshall Barton

jimster68 said:
I'm trying to filter a subform with multiple combo boxes and eventually some
text fields tied to one command button to execute the search/filter. I want
the user to determine which filter or combination of filters to use.
I started with the combo boxes and can get one to work, however, when I add
the second one I am forced to use both or an error is produced. The error is
Runtime Error '2448' you can't assign a value to this object.
Here is a sample.

Private Sub cmdFilter_Click()
sfrm_Server_Data.Form.Filter = "Loc_ID=" & cboLoc.Value & _
"AND Cab_ID=" & cboCab.Value

sfrm_Server_Data.Form.FilterOn = True

End Sub

If I add a value in both combo boxes everthing works fine but I don't want
to force the use of the second combo box.

How would I write this to allow for one or more filter selections tied to
one command button?
Click to expand...


You should check to see if there is a value in each combo
box:

Dim stWhere As String

If Not IsNull(cboLoc) Then
stWhere = " AND Loc_ID = " & cboLoc
End If
If Not IsNull(cboCab) Then
stWhere = " AND AND Cab_ID = " & cboCab
End If
sfrm_Server_Data.Form.Filter = Mid(stWhere, 6)
sfrm_Server_Data.Form.FilterOn = True
 
J

jimster68

Guys - thank you for the response. Got things working just fine now.
Thank you!
 
Top