Multiple Combo Box Filters in a form (1 Viewer)

Tskutnik

Registered User.
Local time
Today, 11:09
Joined
Sep 15, 2012
Messages
229
All - thanks for any help. I can't find this answer.
I the attached DB - There is a form [Holdings_List] with 4 multiple combo boxes used to filter the table.

I cannot figure out how to get them to all work "together". What I want is...
1) The form opens with no records showing
2) When the first selection is made from the Account OR FromDate\ToDate combination OR Security the selected values are shown
3) When the second field value is selected the list filters to only show the First Selection AND the Second Selection
4) When the third field value is selected the list filters to only show the First AND Second AND Third selections
5) If the user blanks out a 3 filter, the remaining filters are applied with the same AND approach.

I already have the [Forms]!.etc... criteria setup in the query and the OnClick even for all 4 fields.

My guess is I have the query criteria incorrect

FYI's
- The related query is embedded in the form Record Source
- A relevant date range for testing is 4/1/20 - 4/30/20
- The [FromDate] and [ToDate] have to work together in a between statement. Selecting only one of the should not filter the data
- I do not want to cascade the data in the combos, using the value selected in one combo to impact the list in the next.

I really appreciate any help. I'm stuck.
 

Attachments

  • ComboBoxTestDB.accdb
    1.1 MB · Views: 212

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:09
Joined
May 21, 2018
Messages
8,463
I do not use form references for this. I use the filter. Makes it a lot easier.
I added a couple helper functions I use.
I function converts dates and string into proper sql form CSql.
The other lets you take separate filters and combine them

Form code
Code:
Private Sub cboHoldListAcct_Key_AfterUpdate()
   FilterList
End Sub

Private Sub txtHoldListToDate_AfterUpdate()
    If IsDate(Me.txtHoldListFromDate) And IsDate(Me.txtHoldListToDate) Then FilterList
End Sub

Private Sub txtHoldListFromDate_AfterUpdate()
    If IsDate(Me.txtHoldListFromDate) And IsDate(Me.txtHoldListToDate) Then FilterList
End Sub

Private Sub cboHoldListSec_Key_AfterUpdate()
    FilterList
End Sub


Private Sub cboHoldListSec_DescShort_AfterUpdate()
    Me.Requery
End Sub

Public Sub FilterList()
  Dim fltrBetween As String
  Dim fltrAcctKey As String
  Dim fltrListSecKey As String
  Dim CombineFltr As String
 
  If IsDate(Me.txtHoldListFromDate) And IsDate(Me.txtHoldListToDate) Then
    fltrBetween = mdlFilters.GetBetweenFilter(Me.txtHoldListFromDate, Me.txtHoldListToDate, "Hold_Date")
  End If
  If Not IsNull(Me.CboHoldListAcct_Key) Then
    fltrAcctKey = "Acct_Key = '" & Me.CboHoldListAcct_Key & "'"
  End If
  If Not IsNull(Me.cboHoldListSec_Key) Then
    fltrListSecKey = "Sec_key = '" & Me.cboHoldListSec_Key & "'"
  End If
 
  combineFilter = CombineFilters(ct_And, fltrBetween, fltrAcctKey, fltrListSecKey)
  If combineFilter <> "" Then
    Me.Filter = combineFilter
    Me.FilterOn = True
  Else
    Me.FilterOn = False
  End If
End Sub

Helper functions

Code:
Public Enum CombineFilterType
  ct_And = 0
  ct_OR = 1
End Enum

Public Function GetBetweenFilter(Ctrl1 As Access.Control, ctrl2 As Access.Control, TheFieldName As String, Optional TheSQL_DataType As SQL_DataType = sdt_date) As String
  Dim val1 As String
  Dim val2 As String
  If Not IsNull(Ctrl1) And Not IsNull(ctrl2) Then
    val1 = CSql(Ctrl1, TheSQL_DataType)
    val2 = CSql(ctrl2, TheSQL_DataType)
    GetBetweenFilter = TheFieldName & " BETWEEN " & val1 & " AND " & val2
  End If
End Function
Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
  Dim FilterCombiner As String
  Dim i As Integer
  Dim strOut As String
 
  If And_Or = ct_And Then
    FilterCombiner = " AND "
  Else
    FilterCombiner = " OR "
  End If
 
  For i = 0 To UBound(Filters)
    If Filters(i) <> "" Then
      If strOut = "" Then
        strOut = Filters(i)
      Else
        strOut = strOut & FilterCombiner & Filters(i)
      End If
    End If
  Next i
  CombineFilters = strOut
End Function
'**************************************************************************************************************
'----------------------------------------Convert To Delimited SQL When Datatype can be Determined -------------
'**************************************************************************************************************


Public Function CSql(ByVal Value As Variant, Optional Sql_Type As SQL_DataType = sdt_UseSubType) As String
    'Can be used when the Value is subtyped. For example you pass a declared variable
    Const SqlNull       As String = "Null"
    Dim Sql             As String
    
    'If the Sql_type is not passed then use the data type of the value
    If Trim(Value & " ") = "" Then
      CSql = SqlNull
    Else
         If Sql_Type = sdt_UseSubType Then
           Select Case VarType(Value)
             Case vbEmpty, vbNull
               Sql_Type = sdt_Null
             Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte
               Sql_Type = sdt_Numeric
             Case vbDate
               Sql_Type = sdt_date
             Case vbString
               Sql_Type = sdt_text
             Case vbBoolean
               Sql_Type = sdt_Boolean
             Case Else
               Sql_Type = sdt_Null
           End Select
         End If
        
        Select Case Sql_Type
           Case sdt_text
                 Sql = Replace(Trim(Value), "'", "''")
                 If Sql = "" Then
                     Sql = SqlNull
                 Else
                     Sql = " '" & Sql & "'"
                 End If
           Case sdt_Numeric
                 If IsNumeric(Value) Then
                  Sql = CStr(Value)
                 Else
                  MsgBox "Invalid data: " & Value & ". You specified a numeric data type", vbInformation
                  Exit Function
                 End If
           Case sdt_date
                 If IsDate(Value) Then
                     If Int(CDate(Value)) = Value Then
                        Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
                     Else
                        Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
                     End If
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a date data type", vbInformation
                   Exit Function
                 End If
           Case sdt_Boolean
                 If Value = "True" Or Value = "False" Or Value = -1 Or Value = 0 Or Value = "Yes" Or Value = "No" Then
                   If Value = "True" Or Value = "Yes" Then Value = -1
                   If Value = "False" Or Value = "No" Then Value = 0
                   Sql = str(Value)
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a boolean data type", vbInformation
                   Exit Function
                 End If
            Case sdt_Null
              Sql = SqlNull
        End Select
          CSql = Trim(Sql)
   End If
    
End Function
 

Attachments

  • ComboBoxTestDB_MajP.accdb
    1.9 MB · Views: 214

Tskutnik

Registered User.
Local time
Today, 11:09
Joined
Sep 15, 2012
Messages
229
I do not use form references for this. I use the filter. Makes it a lot easier.
I added a couple helper functions I use.
I function converts dates and string into proper sql form CSql.
The other lets you take separate filters and combine them

Form code
Code:
Private Sub cboHoldListAcct_Key_AfterUpdate()
   FilterList
End Sub

Private Sub txtHoldListToDate_AfterUpdate()
    If IsDate(Me.txtHoldListFromDate) And IsDate(Me.txtHoldListToDate) Then FilterList
End Sub

Private Sub txtHoldListFromDate_AfterUpdate()
    If IsDate(Me.txtHoldListFromDate) And IsDate(Me.txtHoldListToDate) Then FilterList
End Sub

Private Sub cboHoldListSec_Key_AfterUpdate()
    FilterList
End Sub


Private Sub cboHoldListSec_DescShort_AfterUpdate()
    Me.Requery
End Sub

Public Sub FilterList()
  Dim fltrBetween As String
  Dim fltrAcctKey As String
  Dim fltrListSecKey As String
  Dim CombineFltr As String

  If IsDate(Me.txtHoldListFromDate) And IsDate(Me.txtHoldListToDate) Then
    fltrBetween = mdlFilters.GetBetweenFilter(Me.txtHoldListFromDate, Me.txtHoldListToDate, "Hold_Date")
  End If
  If Not IsNull(Me.CboHoldListAcct_Key) Then
    fltrAcctKey = "Acct_Key = '" & Me.CboHoldListAcct_Key & "'"
  End If
  If Not IsNull(Me.cboHoldListSec_Key) Then
    fltrListSecKey = "Sec_key = '" & Me.cboHoldListSec_Key & "'"
  End If

  combineFilter = CombineFilters(ct_And, fltrBetween, fltrAcctKey, fltrListSecKey)
  If combineFilter <> "" Then
    Me.Filter = combineFilter
    Me.FilterOn = True
  Else
    Me.FilterOn = False
  End If
End Sub

Helper functions

Code:
Public Enum CombineFilterType
  ct_And = 0
  ct_OR = 1
End Enum

Public Function GetBetweenFilter(Ctrl1 As Access.Control, ctrl2 As Access.Control, TheFieldName As String, Optional TheSQL_DataType As SQL_DataType = sdt_date) As String
  Dim val1 As String
  Dim val2 As String
  If Not IsNull(Ctrl1) And Not IsNull(ctrl2) Then
    val1 = CSql(Ctrl1, TheSQL_DataType)
    val2 = CSql(ctrl2, TheSQL_DataType)
    GetBetweenFilter = TheFieldName & " BETWEEN " & val1 & " AND " & val2
  End If
End Function
Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
  Dim FilterCombiner As String
  Dim i As Integer
  Dim strOut As String

  If And_Or = ct_And Then
    FilterCombiner = " AND "
  Else
    FilterCombiner = " OR "
  End If

  For i = 0 To UBound(Filters)
    If Filters(i) <> "" Then
      If strOut = "" Then
        strOut = Filters(i)
      Else
        strOut = strOut & FilterCombiner & Filters(i)
      End If
    End If
  Next i
  CombineFilters = strOut
End Function
'**************************************************************************************************************
'----------------------------------------Convert To Delimited SQL When Datatype can be Determined -------------
'**************************************************************************************************************


Public Function CSql(ByVal Value As Variant, Optional Sql_Type As SQL_DataType = sdt_UseSubType) As String
    'Can be used when the Value is subtyped. For example you pass a declared variable
    Const SqlNull       As String = "Null"
    Dim Sql             As String
   
    'If the Sql_type is not passed then use the data type of the value
    If Trim(Value & " ") = "" Then
      CSql = SqlNull
    Else
         If Sql_Type = sdt_UseSubType Then
           Select Case VarType(Value)
             Case vbEmpty, vbNull
               Sql_Type = sdt_Null
             Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte
               Sql_Type = sdt_Numeric
             Case vbDate
               Sql_Type = sdt_date
             Case vbString
               Sql_Type = sdt_text
             Case vbBoolean
               Sql_Type = sdt_Boolean
             Case Else
               Sql_Type = sdt_Null
           End Select
         End If
       
        Select Case Sql_Type
           Case sdt_text
                 Sql = Replace(Trim(Value), "'", "''")
                 If Sql = "" Then
                     Sql = SqlNull
                 Else
                     Sql = " '" & Sql & "'"
                 End If
           Case sdt_Numeric
                 If IsNumeric(Value) Then
                  Sql = CStr(Value)
                 Else
                  MsgBox "Invalid data: " & Value & ". You specified a numeric data type", vbInformation
                  Exit Function
                 End If
           Case sdt_date
                 If IsDate(Value) Then
                     If Int(CDate(Value)) = Value Then
                        Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
                     Else
                        Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
                     End If
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a date data type", vbInformation
                   Exit Function
                 End If
           Case sdt_Boolean
                 If Value = "True" Or Value = "False" Or Value = -1 Or Value = 0 Or Value = "Yes" Or Value = "No" Then
                   If Value = "True" Or Value = "Yes" Then Value = -1
                   If Value = "False" Or Value = "No" Then Value = 0
                   Sql = str(Value)
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a boolean data type", vbInformation
                   Exit Function
                 End If
            Case sdt_Null
              Sql = SqlNull
        End Select
          CSql = Trim(Sql)
   End If
   
End Function
This is really great. Thanks so much for your time.
 

Tskutnik

Registered User.
Local time
Today, 11:09
Joined
Sep 15, 2012
Messages
229
Again - really great. I copied everything in to my version and it works perfect.

I may need to use this approach for a few other forms. It looks like I would just need to change the events in the filter references in each new form. Is that right? The mdlFilters&QueryUtilities seem non form-specific.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:09
Joined
May 21, 2018
Messages
8,463
I may need to use this approach for a few other forms. It looks like I would just need to change the events in the filter references in each new form. Is that right

Yes. If you follow the code it is very repetitive. You need a variable for each control filter. Then just combine them using the the combine method. Make sure you use the CSql function to properly delimit dates and strings.
 

Tskutnik

Registered User.
Local time
Today, 11:09
Joined
Sep 15, 2012
Messages
229
is there a way to make it open with no records showing? The real DB has millions of records and I dont want any results to show unless they are selected.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:09
Joined
Oct 29, 2018
Messages
21,359
is there a way to make it open with no records showing? The real DB has millions of records and I dont want any results to show unless they are selected.
Hi. Pardon me for jumping in, I added an initial filter to the form when it loads.
 

Attachments

  • ComboBoxTestDB_MajP.zip
    140.9 KB · Views: 104

theDBguy

I’m here to help
Staff member
Local time
Today, 08:09
Joined
Oct 29, 2018
Messages
21,359
Hi. It's me again. I noticed the "clear filter" button is not working, so I decided to work on it. :)
 

Attachments

  • ComboBoxTestDB_MajP (2).zip
    143.3 KB · Views: 188

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:09
Joined
May 21, 2018
Messages
8,463
Code:
Public Sub FilterList()
  Dim fltrBetween As String
  Dim fltrAcctKey As String
  Dim fltrListSecKey As String
  Dim CombineFltr As String
  Dim strSql As String

  strSql = "SELECT Holdings_Agg.Hold_Agg_ID, Holdings_Agg.Acct_Key, Holdings_Agg.Sec_Key, Holdings_Agg.Hold_Date, Holdings_Agg.Hold_Quantity, "
  strSql = strSql & "Holdings_Agg.Hold_Price, Holdings_Agg.Hold_Principal, Holdings_Agg.Hold_Income, Holdings_Agg.Hold_Market, SecMaster_Agg.Sec_DescriptionShort "
  strSql = strSql & "FROM Holdings_Agg INNER JOIN SecMaster_Agg ON Holdings_Agg.Sec_Key = SecMaster_Agg.Sec_Key"

  If IsDate(Me.txtHoldListFromDate) And IsDate(Me.txtHoldListToDate) Then
    fltrBetween = mdlFilters.GetBetweenFilter(Me.txtHoldListFromDate, Me.txtHoldListToDate, "Hold_Date")
  End If
  If Not IsNull(Me.CboHoldListAcct_Key) Then
    fltrAcctKey = "Holdings_Agg.Acct_Key = '" & Me.CboHoldListAcct_Key & "'"
  End If
  If Not IsNull(Me.cboHoldListSec_Key) Then
    fltrListSecKey = "Holdings_Agg.Sec_key = '" & Me.cboHoldListSec_Key & "'"
  End If

  CombineFltr = CombineFilters(ct_And, fltrBetween, fltrAcctKey, fltrListSecKey)
  If CombineFltr <> "" Then
    strSql = strSql & " WHERE " & CombineFltr
    Debug.Print strSql
    Me.RecordSource = strSql
  Else
    Me.RecordSource = ""
  End If
End Sub

If you have that many records using the filter may not be a good idea, so you can modify the recordsource.
I am not sure when you apply the filter after the fact if it returns the records first and then filters or if it actually applies the filter when it pulls the records.
 

Attachments

  • ComboBoxTestDB_MajP2.accdb
    1.9 MB · Views: 237

Tskutnik

Registered User.
Local time
Today, 11:09
Joined
Sep 15, 2012
Messages
229
Majp - thanks for the note. Im not sure of the best method either. The filters are working well now so I'm going to leave it until response time becomes a problem.
 

cvmircea

New member
Local time
Today, 15:09
Joined
Mar 23, 2013
Messages
1
Very useful example for filtering. Thanks, i need this. Is possible to filter also the comboboxes data source to show only existing option?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 19, 2002
Messages
42,986
And I never use filters:) Most of my apps have ODBC BE's so I build all my databases using client/server techniques rather than old Access techniques. Using filters implies that the the form is bound to a table or a query with no criteria and that is not the way we build efficient client/server apps. I bind all my forms to queries WITH criteria. So do do this filtering, I don't use code, I use queries.

The form will have a couple of unbound text boxes or combos to use for the "filtering". The Where clause of the query will be

Where (fld1 = Forms!myform!cbofld1 OR Forms!myform!cbofld1 Is Null)
AND (fld2 = Forms!myform!cbofld2 OR Forms!myform!cbofld2 Is Null)
AND (fld3 = Forms!myform!cbofld3 OR Forms!myform!cbofld3 Is Null)
AND (fld4 = Forms!myform!cbofld4 OR Forms!myform!cbofld4 Is Null)

In the after update event of each combo (or in the click event of a button, if you prefer)
Me.Requery
to rerun the form's RecordSource query.

Notice the way the Where is constructed. Each field is a compound condition. Either the table field is = the form field OR the form field is null. This is what makes the conditions optional so that you can use 0, 1, 2, 3, 0r all 4. Conditions that are specified are "and'd". The parentheses are CRITICAL to get Access to evaluate the multiple, compound conditions as you intend.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 19, 2002
Messages
42,986
Is possible to filter also the comboboxes data source to show only existing option?
I'm not sure what you mean by this but your combo's RowSource query can also contain criteria.
 

Users who are viewing this thread

Top Bottom