Multiple Combo Box Filter

  • Thread starter Thread starter Cameron
  • Start date Start date
C

Cameron

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
 
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
 
Hi Rick, thanks for your post. I know my response is a
little late, but some clarification would be helpful. I
have had little experience with the use of filters and an
explanation of what they are and what they entail would
be helpful. Also I would appreciate it if you could
explain your code, and perhaps how you are utilizing the
filters through it. I'm not quite sure how different
your fields are from mine and if that would actually make
a difference.

Thanks,
Cameron
-----Original Message-----
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






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
Click to expand...


.
Click to expand...
 
Back
Top