Filter SubForm based on 4 Combo Boxes

[mattc66 via AccessMonster.com]

Hi All,

I have a subform that I have filter based on 4 Combo Boxes. Thats the easy
part, I have the LinkChild and Master Fields based on the cboBoxs.

The hard part that I am struggling with is to turn off the filter on 1 or
more of the cboBoxs.

Can anyone help me with how I'd set this up. I thought about using check boxs
next to the cboBox to toggle on or off the filter by the corrisponding cboBox.


Thanks
Matt

 

[mattc66 via AccessMonster.com]

There has to be a better way to handle this.. See code below.

Private Sub cmdFilter_Click()

Dim stLinkChild As String
Dim stLinkMaster As String

'Brand and Month Filter
If Me.cb1 = -1 Then
If Me.cb4 = -1 Then

stLinkChild = "Brand;MonthShipped"
stLinkMaster = "cbo1;cbo4"

Me.frmShopOrderSqFtShippedSummaryRpt.LinkChildFields = ""
Me.frmShopOrderSqFtShippedSummaryRpt.LinkMasterFields = ""

Me.frmShopOrderSqFtShippedSummaryRpt.LinkChildFields = stLinkChild
Me.frmShopOrderSqFtShippedSummaryRpt.LinkMasterFields = stLinkMaster
If Me.cb2 = 0 Then
Me.cbo2 = "ALL"
If Me.cb3 = 0 Then
Me.cbo3 = "ALL"

End If
End If
End If
End If

'Brand, Wood and Month Filter
If Me.cb1 = -1 Then
If Me.cb3 = -1 Then
If Me.cb4 = -1 Then

stLinkChild = "Brand;WoodType;MonthShipped"
stLinkMaster = "cbo1;cbo3;cbo4"

Me.frmShopOrderSqFtShippedSummaryRpt.LinkChildFields = ""
Me.frmShopOrderSqFtShippedSummaryRpt.LinkMasterFields = ""

Me.frmShopOrderSqFtShippedSummaryRpt.LinkChildFields = stLinkChild
Me.frmShopOrderSqFtShippedSummaryRpt.LinkMasterFields = stLinkMaster
If Me.cb2 = 0 Then
Me.cbo2 = "ALL"

End If
End If
End If
End If

'Wood and Month Filter
If Me.cb3 = -1 Then
If Me.cb4 = -1 Then

stLinkChild = "WoodType;MonthShipped"
stLinkMaster = "cbo3;cbo4"

Me.frmShopOrderSqFtShippedSummaryRpt.LinkChildFields = ""
Me.frmShopOrderSqFtShippedSummaryRpt.LinkMasterFields = ""

Me.frmShopOrderSqFtShippedSummaryRpt.LinkChildFields = stLinkChild
Me.frmShopOrderSqFtShippedSummaryRpt.LinkMasterFields = stLinkMaster

If Me.cb1 = 0 Then
Me.cbo1 = "ALL"
If Me.cb2 = 0 Then
Me.cbo2 = "ALL"

End If
End If
End If
End If

'Brand, Model, Wood and Month Filter
If Me.cb1 = -1 Then
If Me.cb2 = -1 Then
If Me.cb3 = -1 Then
If Me.cb4 = -1 Then

stLinkChild = "Brand;ModelType;WoodType;MonthShipped"
stLinkMaster = "cbo1;cbo2;cbo3;cbo4"

Me.frmShopOrderSqFtShippedSummaryRpt.LinkChildFields = ""
Me.frmShopOrderSqFtShippedSummaryRpt.LinkMasterFields = ""

Me.frmShopOrderSqFtShippedSummaryRpt.LinkChildFields = stLinkChild
Me.frmShopOrderSqFtShippedSummaryRpt.LinkMasterFields = stLinkMaster


End If
End If
End If
End If

End Sub