Single Toggle Button for applying a filter showing records in a Form.

[MikeFL]

I need some help. I would like to have a “Command Button” placed on an open form that when clicked will apply a filter that will only show underlying records that have a “Null” value in the [CompDate] field. Then when it is clicked, again it will remove the filter showing all the records in the database. I would also like the name on the button to change back and forth to "filter on, "filter off" if possible. The form being filtered is bound by a “Query.”

Is this possible to do with a command button and if so which Category and Action type do I use?

Is there another way?

I have tried everything I can think of with command buttons with many variations of the code below and nothing works!

Private Sub command542FormFilter_Click()
If Me. command542FormFilter .Caption = "Filter On" Then
    Me. command542FormFilter .Caption = "Filter Off"
    Me.Filter = ""
    Me.FilterOn = False
    Else
    Me. command542FormFilter .Caption = "Filter On"
    Me.Filter = "QUSel_FilterForm_FO-QUsq-TA-000"
    Me.FilterOn = True

End If
End Sub

 

[boblarson]

You had it close, just reversed. If the command button says "Filter On" then you are going to press it to turn the filter on. So you need to set the filter then and then change the caption.

If Me.command542FormFilter.Caption = "Filter On" Then
    Me.command542FormFilter.Caption = "Filter Off"
    Me.Filter = "QUSel_FilterForm_FO-QUsq-TA-000"
    Me.FilterOn = True
Else
    Me. command542FormFilter.Caption = "Filter On"
    Me.Filter = ""
    Me.FilterOn = True
End If

Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[boblarson]

Oops - Ignore the last one, I forgot to set the FilterOn to FALSE:

If Me.command542FormFilter.Caption = "Filter On" Then
    Me.command542FormFilter.Caption = "Filter Off"
    Me.Filter = "QUSel_FilterForm_FO-QUsq-TA-000"
    Me.FilterOn = True
Else
    Me.command542FormFilter.Caption = "Filter On"
    Me.Filter = ""
    Me.FilterOn = False
End If

Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[TheAceMan1]

How are ya MikeFL . . .

If your [blue]more explicit[/blue] with your captions, the code you've provided is just fine:

[blue]Private Sub command542FormFilter_Click()
   Dim cmd As Control
   
   Set cmd = Me.command542FormFilter

   If cmd.Caption = "Filter [purple][b]Is[/b][/purple] On" Then
      cmd.Caption = "Filter [purple][b]Is[/b][/purple] Off"
      Me.Filter = ""
      Me.FilterOn = False
   Else
      cmd.Caption = "Filter [purple][b]Is[/b][/purple] On"
      Me.Filter = "QUSel_FilterForm_FO-QUsq-TA-000"
      Me.FilterOn = True
   End If
   
   Set cmd = nothing

End Sub[/blue]

Also I query your filter [blue]"QUSel_FilterForm_FO-QUsq-TA-000"[/blue]. Your not pinging against a field on the form. Is this working? . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886