Multiple combos event doing same action - Any practical way?

[Celal]

Hi, I have several combos in a form which individually filter the records in that form (continuous). The filtering process is done in a private sub filterList() and all the combos AfterUpdate event calls that procedure. Example:

Private Sub cbo_filterDate_AfterUpdate()
   filterList
End Sub

Private Sub cbo_filterSupp_AfterUpdate()
   filterList
End Sub

Private Sub cbo_filterGroup_AfterUpdate()
   filterList
End Sub

And so on for all the combos.

I also have command buttons doing same thing for each combo to remove the filter:

Private Sub cmd_removeFilterDate_Click()
Me.cbo_filterDate.Value = Null
filterList
End Sub

Private Sub cmd_removeFilterSupp_Click()
Me.cbo_filterSupp.Value = Null
filterList
End Sub

Private Sub cmd_removeFilterGroup_Click()
Me.cbo_filterGroup.Value = Null
filterList
End Sub

I was wondering if there is a better, more practical way of doing this. Thought about naming the combos and buttons as cbo_filter1, cbo_filter2 etc and putting in a for - next loop but couldn't figure out where to put that loop in code.

Thanks for any advice

[Seth Schrock]

I can't really comment on your FilterList sub since I don't know what you have, but since you are wanting it to run when each combo box is changed, you have to call your function from their AfterUpdate event just as you are doing. I don't think that a loop through the controls would benefit you any. Perhaps it would help if you would show us your FilterList sub.

[Celal]

The FilterList sub just changes the record source of the form:
The declarations:

Option Compare Database
Private Const strRowSrc As String = "SELECT * FROM qry_purchases WHERE qry_purchases.TtlQty > 0"
Private Const strOrderBy As String = " ORDER BY qry_purchases.purchaseDate, qry_purchases.purchGroup, qry_purchases.purchaseSubID;"

The FilterList sub:

Private Sub filterList()
Dim myRowSrc As String
Dim rsFilterDate, rsFilterSupp, rsFilterGroup, rsfilterType, rsfilterMaterial, rsfilterCode As String

'check filterdate
'format string to add the # delimiters and get the right format.
Const conJetDate = "\#mm\/dd\/yyyy\#"
    If Not IsNull(Me.cbo_filterDate) Or Me.cbo_filterDate <> "" Then
        rsFilterDate = " AND qry_purchases.purchaseDate = " & Format(Me.cbo_filterDate, conJetDate)
    End If
'check filterSupp
    If Not IsNull(Me.cbo_filterSupp) Or Me.cbo_filterSupp <> "" Then
        rsFilterSupp = " AND qry_purchases.supplierName='" & Me.cbo_filterSupp & "'"
    End If
'check filterGroup
    If Not IsNull(Me.cbo_filterGroup) Or Me.cbo_filterGroup <> "" Then
        rsFilterGroup = " AND qry_purchases.purchGroup='" & Me.cbo_filterGroup & "'"
    End If

'check filterType
    If Not IsNull(Me.cbo_filterType) Or Me.cbo_filterType <> "" Then
        rsfilterType = " AND qry_purchases.purchType='" & Me.cbo_filterType & "'"
    End If
'check filterType
    If Not IsNull(Me.cbo_filterType) Or Me.cbo_filterType <> "" Then
        rsfilterType = " AND qry_purchases.purchType='" & Me.cbo_filterType & "'"
    End If
'check filterMaterial
    If Not IsNull(Me.cbo_filterMaterial) Or Me.cbo_filterMaterial <> "" Then
        rsfilterMaterial = " AND qry_purchases.purchMaterial='" & Me.cbo_filterMaterial & "'"
    End If
'check filterCode
    If Not IsNull(Me.cbo_filterCode) Or Me.cbo_filterCode <> "" Then
        rsfilterCode = " AND qry_purchases.purchCode='" & Me.cbo_filterCode & "'"
    End If

' Filter The List
    myRowSrc = strRowSrc & rsFilterDate & rsFilterSupp & rsFilterGroup & rsfilterType & rsfilterMaterial & rsfilterCode
    myRowSrc = myRowSrc & strOrderBy
    
    Me.RecordSource = myRowSrc
    Me.Requery
    
End Sub

[Seth Schrock]

Considering that you would have to use a select case statement to test for the control name inside the loop, I don't think that you will gain anything by looping through the controls.

[nico5038]

Guess you're indeed "stuck" with this solution for this way of filtering.
Personally I don't code this, as I instruct my users to use the right-click pop-up menu. This filtering is more versatile as it will allow wild characters in the filter and automatically adds the AND relation.
With the QBF you could even use an OR relationship, but that's for most users "a bridge too far".

For some flexible solutions I use the Me.Subform.Filt er value to show on the top of the filtered subform and I allow the user to modify and/or store the filter under a name and let them select such a filter from a combobox. Makes it easy when they have many regular filterings.

Idea ?

Nic;o)

[Celal]

I'd think the select case and loop would not shorten the code either..
Nico's solution does not seem to simplify the code either. I was just curious if I was taking the long and wrong way doing the same thing for the same event for many controls but seems it's the only way.

Thanks a lot for your interest and assistance

[nico5038]

Hmm, "Nico's solution does not seem to simplify the code either."

That's true, it eliminates the code completely :-)

Nic;o)

[Hennepin]

In the property sheet for button change On Click to =clear_filterLi st() or what ever name you choose.
and for the combo boxes change AfterUpdate to =filterList().
In the button tag property put the name of the combo control the button clears in.
You have to change the subs to functions.

Private Function filterList()
   'your filter   
End Function

Private Function clear_filterList()
   Me.Controls(Me.ActiveControl.Tag) = Null
   filterList
End Function

[Celal]

Hennepin: This seems much tidier and simpler for the clear filter buttons. I'll give it a try.

For the combos, I suppose you mean change the "Private Sub filterList()" to "Private Function filterList()" and change the "[Event Procedure]" to "=FilterLis t()" on the property window.

Sounds logical, will try when I get to my computer

[Celal]

Works absolutely fine... leaned up lots of code too.. Thanks a lot Hennepin.