Re: Multiple Combo Box Filter

From: Rick (anonymous_at_discussions.microsoft.com)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 13:49:34 -0600

You have to do something similar to what I have done with a form. I pull
all records, but then have several boxes to limit the entries. I also have
a 'refresh' button. When the button is clicked, it builds and applies a
filter. The following code comes from my form. Let me know if you need
clarification.

To help in testing the code, I created a text box (Text121) and had the
newly created filter display there. This helped me a great deal in trouble
shooting.

The WIPType is an option which allows all records, or only incomplete
records to display. Since one of the two options will always be selected,
it starts my Filter String.

Hope this helps!

Rick

Private Sub Refresh_Click()

On Error GoTo Err_Refresh_Click

Dim FilterStr As String

    Select Case Me!WIPType
    Case 1
        FilterStr = "(DateCompleted = date() or not(datecompleted = date())
or datecompleted is null)"
    Case 2
        FilterStr = "(DateCompleted is null Or month([datecompleted]) =
month(now()) and Year([datecompleted]) = year(now()))"
    End Select

    If Not FindClient = "" Then
            FilterStr = FilterStr & " and ([client#] = '" & [FindClient] &
"')"
    End If

    If Not FindUser = "" Then
        FilterStr = FilterStr & " and ([userID] = '" & [FindUser] & "')"
    End If

    If Not FindSupervisor = "" Then
            FilterStr = FilterStr & " and (SupervisorID = '" &
[FindSupervisor] & "' or SupervisorIfBorrowed = '" & [FindSupervisor] & "')"
    End If

  Text121.Caption = FilterStr

DoCmd.ApplyFilter , FilterStr

Exit_Refresh_Click:
    Exit Sub

Err_Refresh_Click:
    MsgBox Err.Description
    Resume Exit_Refresh_Click

End Sub

"Cameron" <anonymous@discussions.microsoft.com> wrote in message
news:140b601c3f7cb$a40ddea0$a501280a@phx.gbl...
> I am wondering if it is possible to create a form which
> contains a subform that displays various pieces of
> information regarding the employees of a company, and
> have a series of combo boxes on the main form which
> controls the entries that it displays. However, I would
> like it so that if the combo boxes are blank all employee
> entries are displayed but they can be limited by using
> the combo boxes in any combination. I am also not sure
> if this requires the use of filters or something else
> altogether.
>
> Cameron