Apply multiple filters, building off previous filter

[bustersports]

All,

Thanks in advance for any assistance you may be able to give.

My scenario is this - A user will select charges they want to process based on 3 combo boxes. The results will be displayed in a subform. The user will then select from the subform a particular record they want to use against as the rate to apply. This all works fine, no issues. Where the problem lies is that the recordset maybe hundreds or thousands of records. I have an option box to allow them to filter one of 5 ways to narrow the results. What I need to be able to do is apply a filter (the filters could be applied in any order, i.e. 5, then 3, then 2), allow the user to select another filter that will filter the records used in the 1st pass filter, and so on until they have reached the record they are looking for. See example below.

1. User makes 3 combobox selections. Depending on selections, 1 of about 10 queries will run.
2. User decides they want to narrow the list (it is not always necessary to do so, as the original list may be short enough the use finds desired record immediately) by selecting option 5. Note: The filters are based on fields in the original dataset. Ex: Original dataset may have Service Code - DD, Time - 1700. The user may want to 1st narrow down to only Service Code of DD.
3. User determines they need to narrow it further, so they select option 3. This would then filter data from step 2 (option 5 selection). This could continue for up to 5 filters being applied to the original dataset.

I can only get it to do the first filter, any additional filters will filter the original dataset losing the filtering done by the previous filter.

So my questions are:

1. Can I give the user the ability to fine tune their filters to the level they are looking for without having to go to each individual field and apply a filter? I would like for them to just have to click a button. Using my example above, click Service Level button and it would filter down to Service Level without the user having to go to the field and do the filter thru the dropdown list.
2. If so, do the filter buttons have to be on the subform as the subform is a datasheet?

thread702-955623 - this was closest I could find in solving the problem, but still quite a ways off.

Again, thanks for your help.

 

[kjv1611]

I've done something like this a couple of times in the past oh 6 months, I guess... maybe year.. Anyway..

There are 2 ways I can think of for doing this:
1. Using a Table
2. Using a Public Array Variable

Whichever you choose, you basically store the values (and remove values) with the table or array based on user selection on the form.

Then after any change in the filters (or a requested change in the filters, b/c you're actually the one making the changes)... you would combine it all.

Let's assume you wanted to use a table. So it could be done like this:
1. Build a table with one field (or 2 if you want an ID field)
2. The important field will be a Text field for the filter text.
3. Each record input into this table will be your filter text.

For the example, I'll just say "Filter1", "Filter2", etc for the filters - you can put the actual text in.

Code to apply the filter each time:

Function fCurrFilter() As String
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strFilter As String

   Set db = CurrentDb
   Set rs = db.OpenRecordset("MyFiltersTable")

   Do While Not rs.EOF
      If strFilter = vbNullString Then
        strFilter = rs.Fields("Filter")
      Else
        strFilter = strFilter & " AND " & rs.Fields("Filter")
      End If 
   Loop

   If rs Is Nothing Then Else rs.Close: Set rs = Nothing
   If db Is Nothing Then Else db.Close: Set db = Nothing
   
   ApplyFilters = strFilter
End Function

Then... set and use the "current" filter..

Private Sub cmdApplyFilter_Click()
   Form.Filter = fCurrFilter
   Form.FilterOn = True

Of course you'd use that anywhere. Also, of course, you'd need to handle adding and removing filters.

I like the table method so that you can set it up for all sorts of different usage. For instance, if you want it to maintain the same filterset upon the next time the form is opened, rather than the user having to rebuild the filters the next time.

Also, you'll still need to setup the code for building your filters... adding/removing records from the table.

Post back if you need more help there. There are a couple of ways you could do that... using a recordset again, or just running queries or SQL statements in VBA.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

I am not sure if I understand this.

Can I give the user the ability to fine tune their filters to the level they are looking for without having to go to each individual field and apply a filter? I would like for them to just have to click a button. Using my example above, click Service Level button and it would filter down to Service Level without the user having to go to the field and do the filter thru the dropdown list.

Filters have one or more fields and a value. But you want to click a button without providing a value. So I can only assume that the field values are in the original filter but "OR"ed.

I interpret this to be something like this. The initial recordset is based on a where condition that is OR based.

Where ServiceCode = 'DD' OR dtmTime = #11:00:00 AM# OR SomeField = 3 OR SomeField2 = 'ABCD'

So in the subform if I click on the button "Service Code" I want it to return only values where ServiceCode = 'DD'.
If that is the case this can be done by simply applying the filter "ServiceCode = 'DD'" to the above recordset.
The trick is finding the value associated with the field. But that should be avaialable from the original filtering. You might have to store that in a global variable if not persisted in the control.

If you want to see only ServiceCode = DD AND SomeField = 3 then you could click on the "SomeField" button and it would build the filter
"ServiceCode = 'DD' and SomeField = 3

 

[kjv1611]

Another thing to consider is this: Whether your "filters" are best handled by form filters or the underlying recordset itself.

For instance, in one of my scenarios, I handled some pieces via the filter, and some via the recordset itself.

In my case, I had to do the sorting via the recordset, b/c the Form Sort options (blew my mind) just would not work in that case... drove me batty.. so I just started doing the sort in the recordset.

Also, for what MajP pointed out, are you saying you'd want to one-click only see everything with a given service level? Or are you saying you want to summarize the data, grouped on the Service Level? If a grouping type setup, then for those, you may need something totally separate. If just filter on a certain Service Level, then you'd need to have your code look to the field containing the Service Level on the form, and filter based on that..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[bustersports]

Thanks to both of you for your responses. Let me try to clarify a little bit.

The user will select the criteria for the records they want displayed, in this case they would select:
- Fee Code
- Orig Port
- Dest Port

A list of records will be displayed. The user will click on each record to disposition it, basically approving the rate paid, adjusting the amount, or disapproving all together. The subform1 will be updated based on the record selected. The dates of the shipment, weight, delivery time, etc change from record to record, so I am updating subform2 with my best quess as to the records they want to review. If they do not find a desired match, they would then like to filter the subform2 based on Service Type, Delivery Time, and 3 other fields, 1 at a time, as the users want the flexibility to not have to filter in any particular order. I think would require 5 buttons, 1 for each filter. Please note that the first pass of filtering could still result in hundreds of records, which is why the users would like to filter the list down as they see appropriate.

Below is an example which I hope will explain better. This is after the user has made their Fee Code, Orig Port, and Dest Port.

subform 1
Fee Code Ship From Port Ship To Port
240 AMS MDW
BEY AMS MDW

subform 2
Origin Port Dest. Port Fee Code Rate
AMS MDW 240 1
AMS ORD 240 5

The user may want to see additional destination ports that could be used to match the rate billed.

Thanks again for your help and please let me know if this is not clear.

 

[MajP]

If it was me I would rethink the interface. What you are asking sounds complex because once you get a recordset you want the ability to either further filter it or relax the filter.

If it was me my filter form would look like this for maximum search power and ease.
Fee code :multi select listbox
Port: multi select listbox
Destination: multi select listbox
Fee Code: multi select listbox
other fields.
On the form is an button to select "Match All" or "Match Any". Then simply by clicking in the listboxes you can build really complex strings like

"(FeeCode = 240 or FeeCode = 123 or FeeCode = 456) OR (PORt = 'ABC' or PORT = 'CDE') OR (DestPort = 'MDW')....

or by click the button you can make change the selection into
"(FeeCode = 240 or FeeCode = 123 or FeeCode = 456) AND (PORt = 'ABC' or PORT = 'CDE') AND (DestPort = 'MDW')....

Does not make sense to have ANDs within a multiselect control because it cannot have two fee codes in a single record.

I have code that demonstrates this. I usually set this up to filter dynamically. So when I click in the first choise in the first listbox it narrows the list. Uncheck and it expands.

 

[bustersports]

MajP, thanks for the help. I knew it was complex and was driving me crazy. I will look into what you suggested and see how that works for them.