Filtering on Multiple Fields

[dancole42]

So I'm self-taught, which means that most of my code is crappy, brute force, and redundant. Usually I just let it go, but this time it's driving me crazy that there's not a more elegant way to do what I'm doing.

I have two combo boxes on a form called "Main": "ProductSelect" and "QueueSelect." Users can open a form called BugSelect that gets filtered by criteria in one of both of those combo boxes. Users can open BugSelect by clicking "ButtonQueueEdit" or "ButtonQueueView" (the latter opens the BugSelect form in read only mode). Call EnableDisableControls("Main", 0, False) disables the main menu.

Below is how I've implemented this. What's a better way?

Private Sub ButtonQueueEdit_Click()
If IsNull(Me.QueueSelect) Then
    If IsNull(Me.ProductSelect) Then
        MsgBox ("You must select a Product and/or Queue.")
            Exit Sub
    Else
        DoCmd.OpenForm "BugSelect", , , "[Product] = " & Me.ProductSelect
            Call EnableDisableControls("Main", 0, False)
            Exit Sub
    End If
Else
    If IsNull(Me.ProductSelect) Then
        DoCmd.OpenForm "BugSelect", , , "[BugActionQueue] = " & Me.QueueSelect
            Call EnableDisableControls("Main", 0, False)
            Exit Sub
    Else
        DoCmd.OpenForm "BugSelect", , , "[BugActionQueue] = " & Me.QueueSelect & " AND [Product] = " & Me.ProductSelect
            Call EnableDisableControls("Main", 0, False)
    End If
End If
End Sub

Private Sub ButtonQueueView_Click()
If IsNull(Me.QueueSelect) Then
    If IsNull(Me.ProductSelect) Then
        MsgBox ("You must select a Product and/or Queue.")
            Exit Sub
    Else
        DoCmd.OpenForm "BugSelect", , , "[Product] = " & Me.ProductSelect, acFormReadOnly
            Forms![BugSelect]![ViewMode] = "ReadOnly"
            Call EnableDisableControls("Main", 0, False)
            Exit Sub
    End If
Else
    If IsNull(Me.ProductSelect) Then
        DoCmd.OpenForm "BugSelect", , , "[BugActionQueue] = " & Me.QueueSelect, acFormReadOnly
            Forms![BugSelect]![ViewMode] = "ReadOnly"
            Call EnableDisableControls("Main", 0, False)
            Exit Sub
    Else
        DoCmd.OpenForm "BugSelect", , , "[BugActionQueue] = " & Me.QueueSelect & " AND [Product] = " & Me.ProductSelect, acFormReadOnly
            Forms![BugSelect]![ViewMode] = "ReadOnly"
            Call EnableDisableControls("Main", 0, False)
    End If
End If
End Sub

 

[MarkK]

Subroutines

[SIZE="1"]Private Sub ButtonQueueEdit_Click()
    HandleQueueClick
End Sub

Private Sub ButtonQueueView_Click()
    HandleQueueClick
End Sub

Private Sub HandleQueueClick()
    If IsNull(Me.QueueSelect) And IsNull(Me.ProductSelect) Then
        MsgBox ("You must select a Product and/or Queue.")
    ElseIf IsNull(Me.QueueSelect) Then
        OpenBugSelect "[Product] = " & Me.ProductSelect, acFormReadOnly
    ElseIf IsNull(Me.ProductSelect) Then
        OpenBugSelect "[BugActionQueue] = " & Me.QueueSelect, acFormReadOnly
    Else
        OpenBugSelect "[BugActionQueue] = " & Me.QueueSelect & " AND [Product] = " & Me.ProductSelect, acFormReadOnly
    End If
End Sub

Private Sub OpenBugSelect(Filter As String, datamode As AcFormOpenDataMode)
    Const FN = "BugSelect"
    DoCmd.OpenForm FN, , , Filter, datamode
    EnableDisableControls "Main", 0, False
    If datamode = acFormReadOnly Then Forms(FN).ViewMode = "ReadOnly"
End Sub[/SIZE]