Filtering with 4 combo boxes.

[Guest]

I have this form with 4 combo boxes across the top, a button, and a list box
on the bottom:

cboColor
cboShape
cboSize
cboWeight
btnShowAll
lstResults

I want the list box to show all of the products, color, shape, size, weight.
Then when someone selects a color out of cboColor it filters that list box
to only show products with that color, then if they select a shape, it
filters that list box again to show only the products with the color and size
we selected. Now to make this harder if the end user was to de-select the
color, it would filter only by size. Get what I'm saying? The end user
should be able to filter any way they want according to those drop downs.
The btnShowAll button resets the list box and all of the combo boxes so that
all the information is shown, not filtered. I want the form to work like
AutoFilter works in excel, if that gives you an idea. 4 combo boxes to
filter the content below, all in real time, and a button to reset it back to
normal. Sounds easy butt I cannot figure it out, and every where I look it
all doesn't make much sense to me. All of the information is being pulled
from one table, and the options for the combo boxes are created with a query:

SELECT DISTINCT tblItem.weight
FROM tblItem
ORDER BY tblItem.weight;

This is a list of my tables, forms, and queries:

tblItem
frmProductQryTool
qryColor
qryShape
qrySize
qryWeight

Thanks for all of your help!

-Ryan

 

[Allen Browne]

Visit each combo in turn. If it's not null, add it to the filter string.

The Filter string is effective a WHERE clause, so you need AND between each
one. To make it easy to add as many combos as you need, we tack the AND on
each time, and then chop off the last AND at the end of the procedure.

For a text field, you need to add quote delimiters, or for a date/time
field, add # as the delimiter. For example, if Color is a Text field (not a
Number field), you would change the line to:
strWhere = strWhere & "([Color] = """ & Me.cboColor & """) AND "

The example below shows how you can easily add as many filter combos as you
need. If your listbox is multi-select, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

If you get stuck with dates, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

The 2nd procedure (to clear the filter) is a generic one you can store in a
standard module, and then set the On Click property of the 2nd button to
exactly this:
=ClearFilterAndHeader([Form])
This way the property copies with the button when you copy it to use on
other forms.

The explicit save in these routines forces Access to clear all pending
events before the filter/unfilter, which avoids some problems and cryptic
error messages.

----------------code begins----------------------------
Private Sub cmd_Filter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboColor) Then
strWhere = strWhere & "([Color] = " & Me.cboColor & ") AND "
End If

If Not IsNull(Me.cboShape) Then
strWhere = strWhere & "([Shape] = " & Me.cboShape & ") AND "
End If

'etc for other combos.

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strWhere = Left$(strWhere, lngLen)
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Public Function ClearFilterAndHeader(frm As Form)
On Error GoTo Err_ClearFilterAndHeader
'Purpose: Remove the filter, and clear all the unbound contorls in the
form header.
Dim ctl As Control 'Each controls in the form header.

'Save if necessary.
If HasProperty(frm, "Dirty") Then
If frm.Dirty Then
frm.Dirty = False
End If
End If

'Remove the filter.
frm.FilterOn = False
frm.Filter = vbNullString

'Clear all the unbound controls in the form header.
For Each ctl In frm.Section(acHeader).Controls
If HasProperty(ctl, "ControlSource") Then
If Len(Nz(ctl.ControlSource, vbNullString)) = 0& Then
If Not IsNull(ctl.Value) Then
ctl.Value = Null
End If
End If
End If
Next

Exit_ClearFilterAndHeader:
Set ctl = Nothing
Exit Function

Err_ClearFilterAndHeader:
Call LogError(Err.Number, Err.Description, "ClearFilterAndHeader")
Resume Exit_ClearFilterAndHeader
End Function
----------------code ends----------------------------

Replace the error handler with your own, or use the one at:
http://allenbrowne.com/ser-23a.html

HTH