Using Combo Boxes to filter a Split Form

Question

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.

That UNION ALL query is the row source for Combo_Reported_LOB, correct? If so, does it return the correct results when you test it alone as a new query in the Access query designer? One issue ... SELECT Top 10, "**ALL**" ... that doesn't look right to me. — HansUp
@HansUp, Correct, and to your 2nd Question - now my results are not working. I know you have commented on issues similar to this and I appreciate your tutelage. I created a table that has two columns: ID and ReportedLOB, ID=1 and ReportedLOB = ALL, I must have my joins all screwed up, because it fails. Working on it — T-Rex
I tried following Microsoft's example [LINK]( support.microsoft.com/kb/210290) on how to add an "All" option to a ComboBox in Microsoft Access, but their article does not do an adequate job of providing guidance, aside from specifying the code. — T-Rex
OK, and you've set your sights on the UNION query approach instead of the callback function, right? That's the better choice because callbacks are more challenging. Does the query in the following comment give you what you need for the combo's row source. Use any table or query which returns a single row in place of Dual, or make your own Dual table. — HansUp
@HansUp, yes the callback function is not working at all. Therefore, I placed the code above and the Query works. When I go to the Form and Select ALL from the list, my Form Blanks out. Like everything dissappears, white screen. I am guessing my Private Sub Combo_Reported_LOB_Selection_Change() needs to be modified now? — T-Rex

HansUp HansUp · Accepted Answer · 2013-12-30T23:12:50

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.

Using Combo Boxes to filter a Split Form

1 Answers