Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010

    Unanswered: Filtering with Multiple Combo Boxes

    Currently I have a form (tblTaxonomy) with 6 combo boxes that each will filter the results on a Subform (tblTaxonomysubform). Via this:
    Private Function RunFilter()
       On Error GoTo Error_RunFilter
       Dim FilterClause As String, D As Long
       Dim BoxSource As String
       'Hold whether we Use AND or OR in our Filter Criteria
       D = 1
       If Nz(Me.cboType.Column(0), 0) > 0 Then
          If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
          FilterClause = FilterClause & "[txtType]='" & Me.cboType.Value & "'"
       End If 
       'Fill this Form wide variable so that it can be used for
       'the Report.
       CurrentFilter = FilterClause: FilterClause = ""
       'Place our created Filter Criteria into the Filter property of SubForm.
       tblTaxonomysubform.Form.Filter = CurrentFilter
       'Turn on the Filter
       tblTaxonomysubform.Form.FilterOn = True
    Each combo box is also set to filter the results of the next combo box via this AfterUpdate:

    Private Sub cboType_AfterUpdate()
       cboApp.RowSource = "Select Distinct tblTaxonomy.strApplication " & _
                "FROM tblTaxonomy " & _
                "WHERE tblTaxonomy.txtType = '" & cboType.Value & "' " & _
                "ORDER BY tblTaxonomy.strApplication;"
    This works fine if the user is going to go through the boxes in order, but it has been requested that I find a way to allow them to be used out of order. Where I am having trouble is I want to have all combo boxes filter by all boxes that have been selected. For Example:

    Field One Values: Trees, Birds, Fish
    Field Two Values: Pine, Birch, Crow, Seagull, Trout, Bass
    Field Three Values: Water, Land, Air

    If someone started with the Combo Box for field two and selected Birch then I would only want box one to say Trees and box three to say Land. Thats a really simplified example, but I hope you get the idea.

    Thanks in advance for any help you can give.

  2. #2
    Join Date
    Oct 2009
    It is a common request in the forums, because I guess the textbooks don't emphasize this task clearly.

    Fundamentally: combo/list box controls have a record source. Source them to a defined query. Use the criteria of that query to be calling the value of another combo box.

    Hope this helps you get it designed.

    www CahabaData com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts