Using Combo Boxes to filter a Split Form

I am utilizing a Split Form in Access and I have three (3) dropdown combo boxes: ReportedLOB (used in this example) Account StandCategory

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:

 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 <> -1 Then
  Me.Form.Filter = "[ReportedLOB] = '" & _
                 Replace(Me.Combo_Reported_LOB_Selection.Text, "'", "''") & "'"
  Me.FilterOn = True
  End If
 End Sub

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 ALL dropdown item to be added to the list.

 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;

Now, the million dollar question......When I select ALL from the drop-down it changes all my records to All 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.

Now that you have a working row source query for your combo, I'll suggest you use the combo's After Update event to drive changes to the form's .Filter property.

Dim strFilter As String

With Me.Combo_Reported_LOB_Selection
    If IsNull(.Value) Or .Value = "**ALL**" Then
        ' If the combo box is cleared or ALL selected, clear the form filter.
        Me.Filter = vbNullString
        Me.FilterOn = False
    Else
        ' item other than ALL is selected, filter for an exact match.
        strFilter = "[ReportedLOB] = '" & _
            Replace(.Value, "'", "''") & "'"
        Debug.Print strFilter ' check this in Immediate window in case of trouble
                              ' you can use Ctrl+g to go to the Immediate window
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
End With

Notice that the combo does not have focus at After Update, so its .Text property is not available. So we use .Value instead. The .Text property is really only useful while you're changing the value. And the combo has focus while you're making those changes, so .Text is available then. Pretty much any other time, use .Value.

If you really do prefer to continually change the .Filter with each combo keystroke, you will have to adapt the above code for the combo's Change event.