Use a Toggle Button to Filter a Field

[mike60smart]

Hi Everyone

Is it possible to use a Toggle button to set a filter on a Field in a Split Form?


The Toggle button has the following string as it's Caption "BNE"

Then use a Command button's Click Event to filter for the specific String "BNE"

If this works then would it be possible to use Multiple Toggle Buttons?

Any help appreciated.

[Welshgasman]

Yes.
You have already been shown how to filter.
Use the buttons caption for criteria.

[kd2017]

Private Sub toggleButton_Click()
    If Me.toggleButton Then
        'me.toggleButton is True
    
        Me.Filter = "[some_field] = 'BNE'"
        Me.FilterOn = True
    Else
        'me.toggleButton is False
        Me.FilterOn = False
    End If
End Sub

[mike60smart]

Hi Welshgasman

I have 2 Toggle buttons togBNE and togADL

I have never used Toggle Buttons before but I have the following on the Click Event of a Command Button:-

Private Sub cmdFilter_Click()


10        On Error GoTo cmdFilter_Click_Error
20    If Me.togBNE = -1 Then
30    Me.Filter = "[Lane]='" & Me.togBNE.Caption
40            Me.FilterOn = True
50        Else
60            Me.FilterOn = False
70    End If


80    If Me.togADL = -1 Then
90    Me.Filter = "[Lane]='" & Me.togADL.Caption
100           Me.FilterOn = True
110       Else
120          Me.FilterOn = False
130   End If
          
140       On Error GoTo 0
150       Exit Sub


cmdFilter_Click_Error:


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


End Sub

When I try to run this Code I get the following error:-

[kd2017]

You forgot the closing quotation marks

Me.Filter = "[Lane]='" & Me.togBNE.Caption & "'"

What would you expect to happen if both toggles are true at the same time? As it is written togADL will always override whatever togBNE is doing.

You may want to build your filter string before applying it an a manner similar to this: http://allenbrowne.com/ser-62.html

[mike60smart]

Hi kd2017

I am not understanding why the following is happening.

I was under the impression that if I Click a Toggle Button this sets the property to -1

However, I set a breakpoint on line 20 :-

20    If Me.togBNE = -1 Then

When I click the Filter Command Button it takes me to Line 20 and when I hover on Me.togBNE it shows = 0 ?

Can anyone help with this?

[Welshgasman]

@mike60smart
What exactly is a toggle button to you.? I only have 2007 and was envisaging a normal button, click it once and you set the filter, click it again and the filter is cleared.?

You filter syntax is incorrect, that is why you get the error message. Look back at the thread where theDBguy showed you the syntax.

As mentioned, what is meant to happen if you click button A, then button B?

Edit: Are we talking Option Button, or Option Button group ?

[Welshgasman]

Ok, found toggle buttons, never have used them.

However a quick test (you should try this sometimes) does set it to -1, but the next click to 0, so how are you tracking which click is which. There is also a triple state for it as well?

Try this code (amending to suit of course) to see what yours is doing.

Private Sub Toggle0_Click()
If Me.Toggle0 Then
    Debug.Print "Toggle 0 " & Me.Toggle0 & " " & Me.Toggle0.Caption
Else
    Debug.Print "Toggle 0 NE " & Me.Toggle0 & " " & Me.Toggle0.Caption

End If
End Sub

Private Sub Toggle1_Click()
If Me.Toggle1 Then
    Debug.Print "Toggle 1 " & Me.Toggle1 & " " & Me.Toggle1.Caption
    Else
    Debug.Print "Toggle 1 NE " & Me.Toggle1 & " " & Me.Toggle1.Caption

End If
End Sub

[kd2017]

Hi kd2017

I am not understanding why the following is happening.

I was under the impression that if I Click a Toggle Button this sets the property to -1

However, I set a breakpoint on line 20 :-

20    If Me.togBNE = -1 Then

When I click the Filter Command Button it takes me to Line 20 and when I hover on Me.togBNE it shows = 0 ?

Can anyone help with this?

What happens when you flip the toggle button and try again?
Just for reference in ms access: True = -1 and False = 0
Also fyi for future reference, in my testing just now I noticed that when the form loads and before the toggle control has been clicked it's value is null instead of False (0).

[mike60smart]

Well the Immediate Window displays this when Toggle0 is clicked:-

Toggle 0 -1 BNE

[Welshgasman]

Yes, then click it again.

[mike60smart]

Hi






OK the current Split Form displays all records available like this:-


When I click Toggle0 (BNE) it displays 8 records as expected.

When I click Toggle1 (ADL) it displays 2 records as expected.

The Code now is as follows:-

Private Sub Toggle0_Click()


10        On Error GoTo Toggle0_Click_Error
20    If Me.Toggle0 Then
      Dim strFilter As String
30    strFilter = "Left([Lane],3) Like '*" & Me.Toggle0.Caption & "'"
40    Me.Filter = strFilter
50    Me.FilterOn = True
60    End If
          
70        On Error GoTo 0
80        Exit Sub


Toggle0_Click_Error:


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


End Sub

What would I need to do so that I am able to click Toggle0 to display all of the BNE Records and then Click Toggle1 to ALSO display the ADL Records?

[Welshgasman]

You would build the filter accordingly.

You have to decide how it is meant to work?

[kd2017]

Code for your filter button's click event (not the toggle button's click event)

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

You could just as easily put this code in a separate function and then just have the toggle buttons call said function on their click events.

[mike60smart]

Hi

This is where I am lost.

I want to be able to Filter for BNE values and then Also show ADL Values at the same time

Would the filter be on these lines:-

Me.Filter = ("Left([Lane],3) Like '*" & Me.Toggle0.Caption & "'") And ("Left([Lane],3) Like '*" & Me.Toggle1.Caption & "'")