Using a Toggle Button to turn a filter on and off (1 Viewer)

[Bird_FAT]

He all,

I've been searching and playing with this for about 4 days now and I'm still feeling like a noob!

I have a form with four subforms that holds various details for my students - all working fine, now I have a tick box on my main form that shows the archived state of students - i.e. if they are still active or not. Now I need to keep all my student records, but I don't want to trawl through all the archived ones to find the live ones, and vise versa!

Is there a way to add a toggle button that will toggle between showing live ONLY and archived ONLY based on the Yes/No tick box?

I've tried all sorts of things (but, obviously NOT the right things! lol), but I'm really new to Access (1 week of using it) and so have had no luck.

Have tried:

creating a query with a filter - but can only get it to show archived ONLY or ALL.

VBA

Private Sub Check66_Click()
 DoCmd.ApplyFilter , "filter criteria here"
End Sub

- This would be the favourite - but whatever I put as my filter - I can only seem to do the same as above! I guess that I will need an IF/THEN/ELSE statement, but it doesn't seem to work either!

Anybody able to point me in the right direction with the VBA code, or any other method?

Thanks,

 

[JANR]

To use FormFilter property you can use this:

Private Sub Archive_Click()
 Me.Filter = "[Yes/No_Field] = True"
 Me.FilterOn = True
End Sub
 
Private Sub Live_Click()
 Me.Filter = "[Yes/No_Field] = False"
 Me.FilterOn = True
End Sub

Hope this helps.

JR

 

[Bird_FAT]

To use FormFilter property you can use this:

Private Sub Archive_Click()
 Me.Filter = "[Yes/No_Field] = True"
 Me.FilterOn = True
End Sub
 
Private Sub Live_Click()
 Me.Filter = "[Yes/No_Field] = False"
 Me.FilterOn = True
End Sub

Hope this helps.

JR

Nice one JR - I have a question though - I've been trying to put the code here into a toggle button so that I don't have to have two buttons, but I can't seem to get it to go together! I've tried using IF/ELSE and IF/IF, but I'm missing something

    If Me.Filter = "[Archived] = True" Then
        Me.FilterOn = True
        End If
    If Me.Filter = "[Archived] = False"
        Me.FilterOn = True
    End If

    If Me.Filter = "[Archived] = True" Then
        Me.FilterOn = True
    Else: Me.Filter = "[Archived] = False"
        Me.FilterOn = True
    End If

Any ideas if I'm even on the right track?

 

[JANR]

If you make an optiongroup with the toggle buttons on it you can shift between various filters in a select statement.

Private Sub sFilter_AfterUpdate()
 Select Case sFilter
 Case 1
 Me.Filter = "[Archived] = True" 
        Me.FilterOn = True
 Case 2
 Me.Filter = "[Archived] = False"
        Me.FilterOn = True
 Case else
 ' do nothing
 End Select
End Sub

OptionGroupe here is called sFilter.

JR

 

[Bird_FAT]

My bad - I think I've failed to explain - (or I'm missing something!)

In my table, I have a Yes/No field - what I want to have happen is for there to be a toggle button that will allow me to see either:

Case 1 - ONLY records that have this option ticked
or
Case 2 - ONLY records that have this option unticked

By clicking on the toggle button, I then get to change between the two cases.

Your original code

Private Sub Archive_Click()
 Me.Filter = "[Yes/No_Field] = True"
 Me.FilterOn = True
End Sub

Worked to turn the filter on, and another button would then reverse the filter criteria:

Private Sub Live_Click()
 Me.Filter = "[Yes/No_Field] = False"
 Me.FilterOn = True
End Sub

But, I can't seem to merge the criteria so that 1 click of a toggle button causes it to run Case 1 and the second click runs Case 2.

Is that clearer, or have I just muddied the waters even more?

 

[JANR]

If you want to use just one togglebutton to change filters between 2 states either "Archive" aka "yes" and "live" aka "No" then just interrogate the togglebutton's state, it can only be true or false. Something like this:

Private Sub Toggle_AfterUpdate()
    If Me.Toggle = True Then
        Me.Filter = "[Yes/No_Field] = True"
        Me.FilterOn = True
    Else
        Me.Filter = "[Yes/No_Field] = False"
        Me.FilterOn = True
    End If
End Sub

JR

 

[Bird_FAT]

Excellent - Thanks JANR!

Still getting to grips with all the different states of buttons in Access - BeforeUpdate / AfterUpdate / OnClick / etc. - No wonder I couldn't get the ELSE/IF statement to work!

Rep added!

 

[JANR]

Glad to help.

JR

 

[98jbsz]

If you make an optiongroup with the toggle buttons on it you can shift between various filters in a select statement.

Private Sub sFilter_AfterUpdate()
 Select Case sFilter
 Case 1
 Me.Filter = "[Archived] = True" 
        Me.FilterOn = True
 Case 2
 Me.Filter = "[Archived] = False"
        Me.FilterOn = True
 Case else
 ' do nothing
 End Select
End Sub

OptionGroupe here is called sFilter.

JR

I made a toggle button as this post and it works well, the only issue i have is that i have a combo search box, and i want it should only show the records that are filtered and not the whole list. how can i do it?