Applying one 'global filter' on a form that remains while I apply other filters (1 Viewer)

[eschrob]

My database is a large dataset that contains RFQ data for my part numbers from multiple customers for multiple years. I have a form (with subforms) that displays one record at a time and subtotals calculations in the header (like total value of the RFQ).

I would like to apply a drop down combo box to select the customer and date of the RFQ (usually just a year like 2019). Once those filters are set (they can reside in the header), I'd like to be able to filter and sort on the rest of the form to display specific records or a subset of records (like all part #'s beginning with 'ABC').

Everytime I try this, once I apply the second filter, the 1st filter is erased. I'm not a VBA expert but have been able to apply what I've learned on this and other sites to get my database working pretty well. This improvement would be a BIG help!

 

[June7]

If you are using VBA to set form filter, yes, this replaces any criteria set in the form's Filter property.

You have to include all criteria in VBA constructed filter. Your code should already accommodate this. Review http://allenbrowne.com/ser-62.html

Access ribbon and right click filter tools do allow progressive filter.

 

[arnelgp]

either you change to Recordsource of the form or
use Master/Child link fields on the subforms.

 

[theDBguy]

Hi. I also vote for changing the Record Source. That makes the filter more permanent.

 

[eschrob]

Thanks Gents.
I've looked at the Allen Brown example but I can't determine if it really addresses my need. It's using multiple boxes to filter.
What I'm trying to so is set an "Account Name" filter so the form shows all records for Account "A".

Once Account "A's" records are showing, I want to filter and sort on many of the combo boxes in the form (examples below)
- select records where part number is like ABC12* (I have a combo search box set up for this using ApplyFilter macro)
- sort records by descending qty or $
- select record where Index is between "x" and "x" (i have a combo search box that uses VBA event to do this).
When these sorts or filters are applied, I don't want the filter on Account to disappear.

I'm still a beginner at this so I appreciate your patience.

 

[eschrob]

Can you offer an example of "changing the record source"

 

[June7]

Do you want to be able to view Account "B" or any other account records on this same form? If you don't want the Account filter to disappear, then include it in every constructed filter. Don't set the account filter textbox to null or empty string and value remains available for code to use.

Why mix macro and VBA coding?

 

[eschrob]

June7-
I want to be able to select a dropdown to pick the account I want to work on. Once set, I want to do a bunch of filtering and sorting on the records associated with that account.
(Ideally I'd like to also select the 'RFQ year" too, but I'm happy to start slowly. Example, pick Account A and then pick RFQ Year = 2019. Then select the part numbers or index numbers I want to view.

If the account filter textbox is empty, then I want to look at all records or just filter on part numbers (so I can see a specific part # for all accounts).

Your comment about including the account filter in every constructed filter implies that for every text box on the form that I want to filter, I will need to have code selecting Account = Account Select Box.

So I have in my Part Number search box the ApplyFilter Macro where [RFQ P/N] like "*" & [Forms]!frmRFQ]]!PNSearch] & "*"

Do I add another Macro Line for ApplyFilter where [AccountSelecct] like [Forms]![frmRFQ]![Account] & "*"

And I'm using a mix of VBA and macros because I'm using what works because I'm a novice.

 

[arnelgp]

for changing recordsource, you must replace your macro with vba.
on the AfterUpdate of your PNSearch combo:

Private Sub PNSearch_AfterUpdate()
If PNSearch.ListIndex = -1 Then
    Me.Recordsource = "Select * from yourTable;"
Else
    Me.Recordsource = "Select * from yourTable Where [RFQ P/N] Like '*" & _
                Me![PNSearch] & "*';"
End If
End Sub

 

[eschrob]

arnelgp-
Thanks

So, first I select the account name in the AccountSelect combo box drop down.

Then I type a part number in the PNSearch box and hit enter.

The code below filters the records on the Account name in the AccountSelect combo box and then filters on the PN in the PNSearch box.

Do I have it right?

 

[June7]

I think you should abandon macro. I NEVER use macros. My suggestion would mean running code to build filter. And yes, it would include account number criteria every time if there is a value in account number filter textbox.

But suggestion to set RecordSource to include account number criteria is certainly a valid alternative. Then once RecordSource property is set, additional code to set Filter property would not remove account filter.

And since this also involves VBA, more reason to eliminate macro component.

 

[arnelgp]

start the filtering first on Year and Account (main form, I supposed).
on design view of your form, add this to the Property->Event->After Update of
Year combo and Account combo:

=fncFilter()

**
now add code to the main form:

Public Function fncFilter()
Dim sWhere As String 
If Me.[RFQYearcomboNameHere].ListIndex <> -1 Then
   sWhere = "[RFQ Year] = " & Me.[RFQYearcomboNameHere] & " And "
End If
If Trim(Me![Account] & "") <> "" Then
    sWhere = sWhere & "[AccountSelecct] Like "'*" & Me![Account] & "*'"
End 
If Right(sWhere,4) = "And " Then sWhere = Left(sWhere, Len(sWhere)-4)
If Len(sWhere)>0 then
    Me.Recordsource = "Select * from yourTable Where " & sWhere
Else
    Me.Recorsource = "Select * from yourTable"
End If
End Function

 

[Pat Hartman]

I don't know how much data you're working with or if your BE is SQL Server or you have any possibility of converting the BE to an ODBC data source. But if you do, you should not be using filters. You should be using criteria in the RecordSource query. If there are only a few fields involved, you can just make the query work with having come of the criteria be optional. If you have more than say 4 fields that can be used independently or together, you should consider building the Where clause on the fly.

When the criteria is selected, the user presses a "button" and your code simply requeries the form:

Me.Requery

To send the query to the server for processing.

 

[isladogs]

@eschrob
In case it helps, have a look at my two example databases which build filter strings based on multiple combos and option group values https://www.access-programmers.co.uk/forums/showthread.php?t=302189.

The examples show how complex sql strings can be built 'on the fly'

 

[eschrob]

Isladogs,
Thanks for the examples. Since I'm a novice on VBA it will take me some time to interpret your example and apply it to my database. It seems promising. I will be playing with it over the next few weeks as I have time. And will report my results. Thanks again to you and all for the suggestions.

 

[eschrob]

Arnelgp-
I'm trying to adopt your code suggestion. Again, I'm a novice.

Seems like you're defining a subroutine called "fncFilter" which is invoked when either (AccounSelect or RFQYear) combo box is updated.
When I apply =fncFilter() to the Event I get
Private Sub AcctSelect_AfterUpdate()
=fncFilter()
End Sub
This generating an error right away. I'm obviously missing something.

For the actual subroutine, I assume I just past this in the VBA window along with the rest of the code?

Would you be wiling to explain each line in the code?

 

[arnelgp]

delete your your code, and instead directly type:

=fncFilter()

to the event on the Property Sheet.