Use a Toggle Button to Filter a Field

[kd2017]

See my post #14

[mike60smart]

Hi

That only displays whichever Toggle I click

[kd2017]

That's strange, it works just as you've requested on my machine. Can you post your latest full code or db?

[mike60smart]

Hi

I tried this:

Private Sub cmdFilter_Click()


10        On Error GoTo cmdFilter_Click_Error


      Dim filter As String
          
20        filter = ""


30    If Me.Toggle0 Or Me.Toggle1 = -1 Then


40    Me.filter = ("Left([Lane],3) Like '*" & Me.Toggle0.Caption & "'") And ("Left([Lane],3) Like '*" & Me.Toggle1.Caption & "'")
       
50        Debug.Print filter
60    End If
70        On Error GoTo 0
80        Exit Sub


cmdFilter_Click_Error:


90        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdFilter_Click, line " & Erl & "."


End Sub

It only filter to whichever Toggle I select.

[kd2017]

It looks you're trying to be clever around line 30 and 40 but it's not working out.

Try this from post #14:

Private Sub cmdFilter_Click()
    Dim fltr As String
    
    fltr = ""

    If IsNull(Me.Toggle0) Then Me.Toggle0 = False
    If IsNull(Me.Toggle1) Then Me.Toggle1 = False
    
    If Me.Toggle0 Then
        fltr = "[Lane] Like 'BNE*'"
    End If
    
    If Me.Toggle1 Then
        If fltr <> "" Then fltr = fltr & " OR "
        fltr = fltr & "[Lane] Like 'ADL*'"
    End If
    
    Debug.Print fltr
    
    If fltr <> "" Then
        Me.Filter = fltr
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
End Sub

[mike60smart]

Hi

Cancel my last I closed the Form and Reopened and it now does exactly what I need.

Many thanks for this

[mike60smart]

Hi KD2017

So building on those 2 Toggle buttons working how would I modify the code when I have 4 Toggle Buttons like this:-

30        If IsNull(Me.Toggle0) Then Me.Toggle0 = False
40        If IsNull(Me.Toggle1) Then Me.Toggle1 = False
41        If IsNull(Me.Toggle2) Then Me.Toggle2 = False
42        If IsNull(Me.Toggle3) Then Me.Toggle3 = False

Toggle2 Caption is SYD
Toggle3 Caption is MLB

[kd2017]

This would be the pattern

Private Sub cmdFilter_Click()
    Dim fltr As String
    
    fltr = ""
    
    If IsNull(Me.Toggle0) Then Me.Toggle0 = False
    If IsNull(Me.Toggle1) Then Me.Toggle1 = False
    If IsNull(Me.Toggle2) Then Me.Toggle2 = False
    If IsNull(Me.Toggle3) Then Me.Toggle3 = False
    
    If Me.Toggle0 Then
        fltr = "[Lane] Like 'BNE*'"
    End If
    
    If Me.Toggle1 Then
        If fltr <> "" Then fltr = fltr & " OR "
        fltr = fltr & "[Lane] Like 'ADL*'"
    End If
    
    If Me.Toggle2 Then
        If fltr <> "" Then fltr = fltr & " OR "
        fltr = fltr & "[Lane] Like 'SYD*'"
    End If
    
    If Me.Toggle3 Then
        If fltr <> "" Then fltr = fltr & " OR "
        fltr = fltr & "[Lane] Like 'MLB*'"
    End If
    
    Debug.Print fltr
    
    If fltr <> "" Then
        Me.Filter = fltr
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
End Sub

There might be a better way to handle the null conditions. Is the null state a result of the Triple State property? Maybe if you set the default value to all the toggle buttons as false it won't ever load as null?

[Welshgasman]

It looks you're trying to be clever around line 30 and 40 but it's not working out.

Try this from post #14:

Private Sub cmdFilter_Click()
    Dim fltr As String
    
    fltr = ""

    If IsNull(Me.Toggle0) Then Me.Toggle0 = False
    If IsNull(Me.Toggle1) Then Me.Toggle1 = False
    
    If Me.Toggle0 Then
        fltr = "[Lane] Like 'BNE*'"
    End If
    
    If Me.Toggle1 Then
        If fltr <> "" Then fltr = fltr & " OR "
        fltr = fltr & "[Lane] Like 'ADL*'"
    End If
    
    Debug.Print fltr
    
    If fltr <> "" Then
        Me.Filter = fltr
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
End Sub

Yes, it does Mike, you are just fortunate that as they are boolean, it will work.

You could just as easy have

30    If Me.Toggle0 Or Me.Toggle1 = Then

and it will work.
However it would not for most other non boolean tests, so do not get into this habit of OR'ing tests like that.

However if your filter choices are growing in number like weeds, you might want to consider another method, like a multi select listbox and one button?

[mike60smart]

Hi

That works just great.

I can see the Login in the pattern what I get lost on is the basic syntax

Many thanks