Filter Datasheet with multiple combo boxes (1 Viewer)

tmyers

Well-known member
Local time
Today, 03:54
Joined
Sep 8, 2020
Messages
1,090
I have a split-form that I am trying to filter with two different combos, but am struggling a little bit.

The first combo filters the sheet by a project managers name. I then want the second combo to filter those results by type.

My first filter works fine to get it to show specific PM's.
Code:
Dim strfilter   As String
    
    Me.Refresh
    
    strfilter = "[Project Manager Name] like '*" & Me.PMFilterCmb & "*'"
    
    Me.Filter = strfilter
    Me.FilterOn = True

How do I then add a second combo to then filter those results one step further? The field in question is [Job Type] and it has 4 static values (Combination, Switchgear, Lighting, Miscellaneous). So I would select which PM to filter it by, then filter those results by one of the job arch-types. So an example would be I filter it to the PM John. I then filter it to see all Switchgear job types John has.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:54
Joined
Oct 29, 2018
Messages
21,357
Hi. The way I would do something like that is to create a common sub to build the filter criteria by examining each combo for any selections.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:54
Joined
Sep 21, 2011
Messages
14,037
Test if each combo has a value and build the filter accordingly.
You might want to filter for switchgear type regardless of manager. Make it flexible.
 

tmyers

Well-known member
Local time
Today, 03:54
Joined
Sep 8, 2020
Messages
1,090
So would it be something along the lines of if the other is null, filter by me else filter by me AND other?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:54
Joined
Oct 29, 2018
Messages
21,357
So would it be something along the lines of if the other is null, filter by me else filter by me AND other?
Not really. If it was me, I would just check if the first one is not null, then criteria is this. Then, if the next combobox is not null, then criteria is first criteria plus this one too. Here's the basic structure of what I would use:

Code:
Dim strCriteria As String

strCriteria = "1=1"

If Me.FirstCombo > "" Then
    strCriteria = strCriteria & " AND FieldName=" & Me.FirstCombo
End If

If Me.SecondCombo > "" Then
    strCriteria = strCriteria & " AND FieldName=" & Me.SecondCombo
End If

Debug.Print strCriteria

Me.Filter = strCriteria
Me.FilterOn = True
Hope that helps...
 

tmyers

Well-known member
Local time
Today, 03:54
Joined
Sep 8, 2020
Messages
1,090
Got it! Thanks as always for your help!
Code:
Dim strfilter   As String

    If Forms.ProjectsFrm.PMFilterCmb > "" Then
   
        strfilter = "[Project Manager Name] like '*" & Forms.ProjectsFrm.PMFilterCmb & "*'"
       
    End If
   
    If Forms.ProjectsFrm.JobTypeCmb > "" Then
   
        strfilter = strfilter & " AND [Job Type] like '*" & Forms.ProjectsFrm.JobTypeCmb & "*'"
       
    End If
       
    Forms.ProjectsFrm.Filter = strfilter
    Forms.ProjectsFrm.FilterOn = True

EDIT:
That throws an error if I try to filter by type before name, but that is an easy fix.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:54
Joined
Feb 19, 2002
Messages
42,970
Do NOT use like when your criteria comes from a combo. LIKE is ONLY used when you have a partial string. LIKE usually prevents the use of indexes and forces a full table scan. Doesn't matter much if you have only a hundred records in a table but once you get to thousands, the search will become sluggish.

I don't use filters at all but that's because my BE's are usually SQL Server and you want the server to do the searching. You don't want to download entire tables to search locally.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:54
Joined
Oct 29, 2018
Messages
21,357
That throws an error if I try to filter by type before name, but that is an easy fix.
Remember what I said earlier, I would prefer to create a common sub for all of that and simply call it from whatever event you want to use to apply the filter. So, in that case, no matter which order you want to select the comboboxes, the criteria would still work.
 

tmyers

Well-known member
Local time
Today, 03:54
Joined
Sep 8, 2020
Messages
1,090
I get what you are saying DBguy. I will make that change.

Pat, in my particular case, the records are extremely unlikely to ever go over 500. Most of the "cleaning" is done when an excel sheet that does have the thousands of lines is imported in. I have several queries that then clean it up before the user even sees it. This is also just me rehashing code I used previously and could remember (that particular code did indeed require the "like"). I will drop the "like" though as you are correct with it not being needed in this instance.
 

Users who are viewing this thread

Top Bottom