Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple filters in a form for filtering a subform 1

Status
Not open for further replies.

Johnnycat1

Programmer
May 4, 2005
71
US
I have been trying to filter the data in a subform using multiple filters in the main form. Each of the filters work individually as I have written them but my efforts to combine the criteria of the filters has me stumped. The goal is to filter the date in the subform based on multiple combo boxes in the main form. As always your help is very appreciated.

This is the code for each of the two filters:
With Forms![Job # Project Order # Form]![Job # Project Order # SubForm].Form
.Filter = "[Customer]='" & Forms![Job # Project Order # Form]![CustomerName] & "'"
.FilterOn = True
End With

With Forms![Job # Project Order # Form]![Job # Project Order # SubForm].Form
.Filter = "[ClosedProjects]='" & Forms![Job # Project Order # Form]![FilterClosedProject] & "'"
.FilterOn = True
End With

When I combine them as follows, I get a type mismatch.
With Forms![Job # Project Order # Form]![Job # Project Order # SubForm].Form
.Filter = "[Customer]='" & Forms![Job # Project Order # Form]![CustomerName] & "'" And "[ClosedProjects]='" & Forms![Job # Project Order # Form]![FilterClosedProject] & "'"
.FilterOn = True
End With

Any suggestions???
 
& "'" And "[ClosedProjects]='"
that is goofed up.
& "' And [ClosedProjects]='"

"[Customer]='" & Forms![Job # Project Order # Form]![CustomerName] & "' And [ClosedProjects]= '" & Forms![Job # Project Order # Form]![FilterClosedProject] & "'
 
MajP,

Thank you!!! Your fix was perfect. This is a huge help.
 
I'm trying to do the same as you, but I am not a VB coder, so I'm struggling. I think I can use this code for my filters, but where do I put it?

I've got a form based on a dataset I want to filter by name, date and location. I'm using a combo box to filter by name, but when I add a location combo box the filter doesn't work. I'm thinking I need to write a bit of code like yours, but is it an event at form level? Which object/event does it refer to?

Hope you can understand this. Thanks.
 
brocsman,

This is an event at form level. you need create an event procedure on the After Update event.

One of the issues that I ran into was that I had to write some logic to deal with the scenario where one of the filters was blank and I only wanted to filter on the one combo box.

That is why I have the nz code. see below.

If Nz(Me![FilterClosedProject], 9) = 9 Then GoTo FilterByCustomerNameOnly Else GoTo FilterByBoth

FilterByBoth:
With Forms![Job # Project Order # Form]![Job # Project Order # SubForm].Form
.Filter = "[Customer]='" & Forms![Job # Project Order # Form]![CustomerName] & "' And [ClosedProjects]= '" & Forms![Job # Project Order # Form]![FilterClosedProject] & "'"
.FilterOn = True
End With
Exit Sub

FilterByCustomerNameOnly:
With Forms![Job # Project Order # Form]![Job # Project Order # SubForm].Form
.Filter = "[Customer]='" & Forms![Job # Project Order # Form]![CustomerName] & "'"
.FilterOn = True
End With

Hope this helps...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top