none
toglle to filter form RRS feed

  • Question

  • I have a form where i want a toggle button to filter the form

    if I press the button the form filters correctly , but if a press the button again, de form does not show all the records again , although I do a requery .

    it seems that the underlying query does not refresh.

    some listboes on the form to sort the data do not work either if a want to go back to the unfiltered form

    ...

    If Me.Toggle870 Then
         
                
               
               
              
                    Me.Filter = "[FSklant] = " & 1380
                    Me.FilterOn = True

    else

     
             Me.FilterOn = False

    end if

    Wednesday, December 7, 2016 5:18 PM

Answers

  • The examples in my ComboDemo file do not use filtering.  In each case the form's query is restricted by referencing unbound controls in the form as parameters.  If you wish to extend the restriction on the basis of a fixed criterion, rather than selecting a value in an unbound combo box. I'd suggest that, rather than a toggle button, you add an unbound check box control to the form, with a default value of False, and reference this as a further parameter in the query along the following lines:

     

    AND ((Forms!YourFormName!YourCheckBox = TRUE

    AND FSKlant = 1380) OR Forms!YourFormName!YourCheckBox = FALSE)

     

    In the check box's AfterUpdate event procedure requery the form with:

     

    Me.Requery


    Ken Sheridan, Stafford, England

    Wednesday, December 7, 2016 10:36 PM
  • Try this:

     

        If Me.FilterOn Then

             Me.FilterOn = False

        Else

            Me.Filter = "[FSklant] = " & 1380

            Me.FilterOn = True

        End If

     

    You don't really need a toggle button for this; a straightforward command button will do.  Whether the form is filtered or not will be indicated in the navigation bar.

     

    Requerying the form has nothing to do with this.  Calling the Requery method reloads the form's recordset and consequently returns rows on the basis of any restriction of the recordset on one or more columns, which is not the same as filtering the form.


    Ken Sheridan, Stafford, England

    Wednesday, December 7, 2016 6:08 PM

All replies

  • Hi,

    Just a thought... it may not be necessary but try adding:

    Me.Filter=""

    before Me.FilterOn=False

    Hope it helps...

    Wednesday, December 7, 2016 5:19 PM
  • Try this:

     

        If Me.FilterOn Then

             Me.FilterOn = False

        Else

            Me.Filter = "[FSklant] = " & 1380

            Me.FilterOn = True

        End If

     

    You don't really need a toggle button for this; a straightforward command button will do.  Whether the form is filtered or not will be indicated in the navigation bar.

     

    Requerying the form has nothing to do with this.  Calling the Requery method reloads the form's recordset and consequently returns rows on the basis of any restriction of the recordset on one or more columns, which is not the same as filtering the form.


    Ken Sheridan, Stafford, England

    Wednesday, December 7, 2016 6:08 PM
  • Hi Ken,

    this seems to work, the file is filtered correctly, but when I go back and have the form not filtered, the form is not refreshed anymore. It is a form on a navigation form.

    the form is based on one of your example databases , combodemo

    Wednesday, December 7, 2016 9:41 PM
  • The examples in my ComboDemo file do not use filtering.  In each case the form's query is restricted by referencing unbound controls in the form as parameters.  If you wish to extend the restriction on the basis of a fixed criterion, rather than selecting a value in an unbound combo box. I'd suggest that, rather than a toggle button, you add an unbound check box control to the form, with a default value of False, and reference this as a further parameter in the query along the following lines:

     

    AND ((Forms!YourFormName!YourCheckBox = TRUE

    AND FSKlant = 1380) OR Forms!YourFormName!YourCheckBox = FALSE)

     

    In the check box's AfterUpdate event procedure requery the form with:

     

    Me.Requery


    Ken Sheridan, Stafford, England

    Wednesday, December 7, 2016 10:36 PM
  • Hi Ken, I did not look at it this way, Seems to be the best solution I'' m off for 2 days now, i try it on saturday and let you know the result, Many thanks
    Thursday, December 8, 2016 6:15 AM
  • I have a form where i want a toggle button to filter the form

    if I press the button the form filters correctly , but if a press the button again, de form does not show all the records again , although I do a requery .

    Hi tekoko,

    The way I usually work is a little different: I hardly use filters.

    Depending on the context I create a sql_string dynamically. So in your case that would be:

    "unfiltered": "SELECT * FROM Klanten_tbl"

    "filtered":     "SELECT * FROM Klanten_tbl WHERE FSklant = " &  klant_id

    where klant_id = 1380.

    At each click of the button the appropriate sql_string is assigned to the form's RecordSource. Especially in more complex situations it saves a lot of bookkeeping in restoring the previous situation, and it is very versatile.

    Imb.

    Thursday, December 8, 2016 8:33 AM
  • hi  Ken , it works ! many thanks !
    Saturday, December 10, 2016 2:34 PM
  • Hi tekoko10,

    I am glad your issue has been resolved, and I would suggest you mark the reply from Ken as answer.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 12, 2016 7:44 AM
  • Done ! :)
    Monday, December 12, 2016 11:18 AM