Cascading combo box to show all fields when box is blank

[mjseim]

This post is meant to help others. I have already solved the problem but was unable to find a solution anywhere. Therefore, I decided to post the solution in case anyone has the same problem in the future.

This is what you're about to see in code:
1) I have three cascading combo boxes
2) I want those combo boxes to filter a field "CostCode" ONLY IF they have a value entered.
3) So... with a simple series of If/Then statements and predeteremined RowSource strings, I was able to construct a method for filtering only by those cascading combo boxes that had values entered.

If anyone knows a better way, please post! Take care.

Private Sub CostCodeFilter()
    
    If Me.filterCategory <> "" Then
        If Me.filterSubcategory <> "" Then
            If Me.filterItem <> "" Then
                'Filter: Category, Subcategory, Item
                Me.txtCostCodeFull.RowSource = "SELECT DISTINCTROW [HOA-Budgets-CostCodes].CostCodeFull, [HOA-Budgets-CostCodes].CategoryName, [HOA-Budgets-CostCodes].SubcategoryName, [HOA-Budgets-CostCodes].ItemName, [HOA-Budgets-CostCodes].ItemMemo FROM [HOA-Budgets-CostCodes] WHERE ((([HOA-Budgets-CostCodes].CategoryID)=[Forms]![HOA-Budgets].[filterCategory]) AND (([HOA-Budgets-CostCodes].SubcategoryID)=[Forms]![HOA-Budgets].[filterSubcategory]) AND (([HOA-Budgets-CostCodes].ItemID)=[Forms]![HOA-Budgets].[filterItem])) ORDER BY [HOA-Budgets-CostCodes].CostCodeFull;"
            Else
                'Filter: Category, Subcategory
                Me.txtCostCodeFull.RowSource = "SELECT DISTINCTROW [HOA-Budgets-CostCodes].CostCodeFull, [HOA-Budgets-CostCodes].CategoryName, [HOA-Budgets-CostCodes].SubcategoryName, [HOA-Budgets-CostCodes].ItemName, [HOA-Budgets-CostCodes].ItemMemo FROM [HOA-Budgets-CostCodes] WHERE ((([HOA-Budgets-CostCodes].CategoryID)=[Forms]![HOA-Budgets].[filterCategory]) AND (([HOA-Budgets-CostCodes].SubcategoryID)=[Forms]![HOA-Budgets].[filterSubcategory])) ORDER BY [HOA-Budgets-CostCodes].CostCodeFull;"
            End If
        Else
            'Filter: Category
            Me.txtCostCodeFull.RowSource = "SELECT DISTINCTROW [HOA-Budgets-CostCodes].CostCodeFull, [HOA-Budgets-CostCodes].CategoryName, [HOA-Budgets-CostCodes].SubcategoryName, [HOA-Budgets-CostCodes].ItemName, [HOA-Budgets-CostCodes].ItemMemo FROM [HOA-Budgets-CostCodes] WHERE ((([HOA-Budgets-CostCodes].CategoryID)=[Forms]![HOA-Budgets].[filterCategory])) ORDER BY [HOA-Budgets-CostCodes].CostCodeFull;"
        End If
    Else
        'Filter: None
        Me.txtCostCodeFull.RowSource = "SELECT DISTINCTROW [HOA-Budgets-CostCodes].CostCodeFull, [HOA-Budgets-CostCodes].CategoryName, [HOA-Budgets-CostCodes].SubcategoryName, [HOA-Budgets-CostCodes].ItemName, [HOA-Budgets-CostCodes].ItemMemo FROM [HOA-Budgets-CostCodes] ORDER BY [HOA-Budgets-CostCodes].CostCodeFull;"
    End If
    
    Me.filterCategory.Requery

End Sub