title: Using Combo Boxes to filter a Split Form
body: <p>I am utilizing a Split Form in Access and I have three (3) dropdown combo boxes:
  ReportedLOB (used in this example)
  Account
  StandCategory</p>
  
  <p>Each of these combo boxes are tied to the specific Business Unit selected, therefore it limits the amount of items in the Combo box.  I am using the following code for the ReportedLOB:</p>
  
  <pre><code> Private Sub Combo_Reported_LOB_Selection_Change()
  
  ' If the combo box is cleared, clear the form filter.
    If Nz(Me.Combo_Reported_LOB_Selection.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
  
  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
    ElseIf Me.Combo_Reported_LOB_Selection.ListIndex &lt;&gt; -1 Then
    Me.Form.Filter = "[ReportedLOB] = '" &amp; _
                   Replace(Me.Combo_Reported_LOB_Selection.Text, "'", "''") &amp; "'"
    Me.FilterOn = True
    End If
   End Sub
  </code></pre>
  
  <p>Let's say there are 4 items in the drop down list: MCD, Comp, GRD, DRR
  When I select MCD, it filters correctly for MCD results. However, upon review with the team, they want to remove that filter for MCD to have all results again, so I created an extra table an joined it for a <strong>ALL</strong> dropdown item to be added to the list.</p>
  
  <pre><code> SELECT DISTINCT dbo_ztblGAAP_Splits_TableA.ReportedLOB FROM
  dbo_ztblGAAP_Splits_TableA WHERE (((dbo_ztblGAAP_Splits_TableA.BU)=[Forms]![Frm_Main]!
  [Frm_Main_TextBox_Display_BU_Number_HIDDEN])) 
  ORDER BY dbo_ztblGAAP_Splits_TableA.ReportedLOB
  UNION ALL 
  SELECT Top 10, "**ALL**" FROM  
  dbo_tTbl_ADMIN_ForFiltering
  ORDER BY ReportedLOB;
  </code></pre>
  
  <p>Now, the million dollar question......When I select <strong>ALL</strong> from the drop-down it changes all my records to <strong>All</strong> versus giving me all the original results for Reported LOB.  Has anyone every dealt with this?  I have searched everywhere to try to piece this code together.</p>