Filtering on a form with multiple combo boxes (1 Viewer)

[jco23]

i have a form with four combo boxes and two radio button boxes.
combo_PR
combo_PN
combo_CC
combo_FT

each of the combo boxes above reference a field (respectively):
pr
pn
cc
ft

for each combo box, I use (obviously changing the combo_pr and pr for each event):
If IsNull(Me.Combo_PR) Then
DoCmd.ApplyFilter "[pr]", "[pr] like '*'"
Else
DoCmd.ApplyFilter "[pr]", "[pr] = '" & Me.Combo_PR & "'"
End If

and this works fine individually. however, if I filter on combo_PR and the attempt to add another filter like combo_PN, the query appears to reset/forget the combo_pr filter and just filter for combo_pn. of course, I'd like to keep some nulls in there for when folks want to filter on just one or two criteria rather than three or four.

what is the bet method to apply the filtering to focus on multiple combo boxes?

normally, I set the criteria within the query to be:

Like IIf(IsNull([Forms]![frm_fr]![Combo_pr]),"*",[Forms]![frm_fr]![Combo_pr])

and this has worked best, but for whatever reason, within this query, using this slows it down tremendously to where it is not useable.

any insight would be greatly appreciated!

thx!

 

[theDBguy]

Hi. Try looking at some demos on "search form" to see some examples on how to do something like that.

Sent from phone...

 

[arnelgp]

create a function in your form and call it on the AfterUpdate event:

=fncApplyFilter()

Public Function fncApplyFilter()
    Dim strFilter As String
    If [comb_PR].ListIndex > -1 Then
        strFilter = strFilter & "[pr] Like '*" & [combo_PR] & "*' And "
    End If
    If [combo_PN].ListIndex > -1 Then
        strFilter = strFilter & "[pn] Like '*" & [combo_PN] & "*' And "
    End If
    If [combo_cc].ListIndex > -1 Then
        strFilter = strFilter & "[cc] Like '*" & [combo_cc] & "*' And "
    End If
    If [combo_FT].ListIndex > -1 Then
        strFilter = strFilter & "[ft] Like '*" & [combo_FT] & "*'"
    End If
    
    If Len(strFilter) > 0 Then
        If Right(strFilter, 4) = "And " Then
            strFilter = Trim$(Left$(strFilter, Len(strFilter) - 4))
        End If
        Me.Filter = strFilter
        Me.FilterOn = True
        
    Else
        Me.FilterOn = False
    End If
End Function