Additional Info - Help with Multiple Filters/Queries

[Derek]

Okay here goes.

I have created a form which allows the client to select
one up to five different criteria to search on. ex.
1. Customer Name
2. Project Description
3. Location
4. Cost Range
5. Date Range

"Customer Name" is a pick list from the customer database
"Project Description" is a text box that allows the client
to enter a single key word from the description of the
project.
"Location" is a pick list from the related database of
projects for the customer
"Cost Range" is a two text box entry allowing the client
to enter a range of values which he/she thinks the cost
was.
"Date Range" is a two text box entery allowing the client
to enter a date range when the project was completed.

I want to allow the client to pick multiple criteria to
search for a project. ie. Customer Name and Date Range
or Project Description and Cost Range. Then press a
submit button. In my mind what should happen in VBA is
that the Recordset would be filtered using
{recordset}.filter = first criteria then this recordset
saved somehow and then this new filtered recordset is
filtered again with the next criteria and so on. I can
program the computer to apply a single filter for any one
of my conditions. I am unable to find a way to capture
this filtered recordset so that I can apply an additional
filter to it. I have tried the me.recordsetclone but that
captures the original recordset not the filtered one. Any
direction would be better than the direction I am in now.
If you need any additional info let me know.

 

[Allen Browne]

This example shows how to apply a filter to a form, based on *unbound* boxes
where the user enters their criteria:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen as Long

If Not IsNull(Me.cboCustName) Then
strWhere = strWhere & "([Customer Name] = """ & Me.cboCustName &
""") AND "
End If

If Not IsNull(Me.txtProjDescrip) Then
strWhere = strWhere & "([Project Description] = """ &
Me.txtProjDescrip & """) AND "
End If

'etc. for other boxes.

'Now chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else

'Apply the filter.
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
Me.Filter = Left(strWhere, lngLen)
Me.FiliterOn = True
End If

End Sub