REPOST: LISTBOX filter toggle button

[lmv]

I have a listbox (List34) that I want to be able have a toggle button to turn
off/on the "inactive" filter at present it is set to following criteria.

SELECT [Name List].ID, [Name List].TID, [Name List].LastName, [Name
List].FirstName, [Name List].Status
FROM [Name List]
WHERE ((([Name List].Inactive)=False))
ORDER BY [Name List].LastName, [Name List].FirstName

can someone direct me to an example to download or what code to put in
the clk event of a button. Also, I will need to requery the List34 right?

I did this once upon a time but can't remember how now.
That's the problem with not working on a project all the time.

Thanks

 

[Rastro]

You need to make several strings of select sentences. For example, following
your select sentence.
You should have to add a command button named btnActiveFilter and Captioned
"Active Records" and copy the code below

Privat Sub btnActiveFilter_Click()
dim strFilterOnA, strFilterOnI, strFilterOff as string

' Bring only active records
strFilterOnA= "SELECT [Name List].ID, [Name List].TID, [Name List].LastName,
" & _
"[Name List].FirstName, [Name List].Status FROM [Name List] " & _
" WHERE ((([Name List].Inactive)=False) " & _ 'here is the
filter
"ORDER BY [Name List].LastName, [Name List].FirstName "

'Bring inactive
strFilterOnI= "SELECT [Name List].ID, [Name List].TID, [Name List].LastName,
" & _
"[Name List].FirstName, [Name List].Status FROM [Name List] " & _
" WHERE ((([Name List].Inactive)=True)) " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "

'Bring all
strFilterOff= "SELECT [Name List].ID, [Name List].TID, [Name List].LastName,
" & _
"[Name List].FirstName, [Name List].Status FROM [Name List] " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "



Select Case btnActiveFilter.Caption
Case "Active Records"
List34.RowSource=strFilterOnA
List34.Requery
btnActiveFilter.Caption="Inactive Records"

Case "Inactive Records"
List34.RowSource=strFilterOnI
List34.Requery
btnActiveFilter.Caption="All Records"

Case "All Records"
List34.RowSource=strFilterOff
List34.Requery
btnActiveFilter.Caption="Active Records"

End Select

End Sub

This will do the trick.
Rastro

 

[lmv]

Thank you ... I have tried to get this to work but I can't figure it out

The listbox is based on inactive=false
I just need it to toggle between "false" and "is not null"

If I could see the inactive= true it would be ok but not necessary
But I can't get the code to work it doesn't toggle between the 3 cases. I
have tried as many combinations as I can think of but still isn't working.
Have the button named "Active" but this is how the list loads so I really
just need it to go between ACTIVE and ALL.... this is what I tried using
your code.

Private Sub btnActiveFilter_Click()
Dim strFilterOnA, strFilterOnI, strFilterOff As String

'Bring active
strFilterOnA = "SELECT [Name List].ID, [Name List].TID, [Name
List].LastName, " & _
"[Name List].FirstName, [Name List].Status FROM [Name List] " & _
" WHERE ((([Name List].Inactive)=False)) " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "

' Bring only inactive records
strFilterOnI = "SELECT [Name List].ID, [Name List].TID, [Name
List].LastName, " & _
"[Name List].FirstName, [Name List].Status FROM [Name List] " & _
" WHERE ((([Name List].Inactive)= True) " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "

'Bring all
strFilterOff = "SELECT [Name List].ID, [Name List].TID, [Name
List].LastName, " & _
"[Name List].FirstName, [Name List].Status FROM [Name List] " & _
" WHERE ((([Name List].Inactive)= Is Not Null) " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "

Select Case btnActiveFilter.Caption

Case "Active"
List34.RowSource = strFilterOnA
List34.Requery
btnActiveFilter.Caption = "Active"

Case "Inactive"
List34.RowSource = strFilterOnI
List34.Requery
btnActiveFilter.Caption = "InActive"

Case "All"
List34.RowSource = strFilterOff
List34.Requery
btnActiveFilter.Caption = "All"

End Select

End Sub

 

[Rastro]

I'm sorry that I messed you up with more options than you asked.
If I don't get you wrong you need a switch between to filters, or filter and
absence of it. Right?

What I don't understand is if you already have the control to toggle and
what are you going to filter. I though you want it for the listbox34.

I'm going to suppose that you have a form, a toggle control named
ToggleFilter, and a listbox named Listbox34, and if you leave the toggle
button down (=true) that means that the filter is on and the Listbox34 will
only shows the active records. Caption the ToggleFilter to "Filter off"

Private Sub ToggleFilter_AfterUpdate()
Dim strFilterOn, strFilterOff as String

' Bring only Active records
strFilterOn = "SELECT [Name List].ID, [Name List].TID, " & _
"[Name List].LastName, [Name List].FirstName, " & _
"[Name List].Status FROM [Name List] " & _
" WHERE ((([Name List].Inactive)= False) " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "


'Bring all
strFilterOff = "SELECT [Name List].ID, [Name List].TID, " & _
"[Name List].LastName, [Name List].FirstName, " & _
"[Name List].Status FROM [Name List] " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "

If ToggleFilter then
List34.RowSource=strFilterOn
ToggleFilter.Caption="Filter on"
Else
List34.RowSource=strFilterOff
ToggleFilter.Caption="Filter off"
End If

End Sub

Thank you ... I have tried to get this to work but I can't figure it out

The listbox is based on inactive=false
I just need it to toggle between "false" and "is not null"

If I could see the inactive= true it would be ok but not necessary
But I can't get the code to work it doesn't toggle between the 3 cases. I
have tried as many combinations as I can think of but still isn't working.
Have the button named "Active" but this is how the list loads so I really
just need it to go between ACTIVE and ALL.... this is what I tried using
your code.

Private Sub btnActiveFilter_Click()
Dim strFilterOnA, strFilterOnI, strFilterOff As String

'Bring active
strFilterOnA = "SELECT [Name List].ID, [Name List].TID, [Name
List].LastName, " & _
"[Name List].FirstName, [Name List].Status FROM [Name List] " & _
" WHERE ((([Name List].Inactive)=False)) " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "

' Bring only inactive records
strFilterOnI = "SELECT [Name List].ID, [Name List].TID, [Name
List].LastName, " & _
"[Name List].FirstName, [Name List].Status FROM [Name List] " & _
" WHERE ((([Name List].Inactive)= True) " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "

'Bring all
strFilterOff = "SELECT [Name List].ID, [Name List].TID, [Name
List].LastName, " & _
"[Name List].FirstName, [Name List].Status FROM [Name List] " & _
" WHERE ((([Name List].Inactive)= Is Not Null) " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "

Select Case btnActiveFilter.Caption

Case "Active"
List34.RowSource = strFilterOnA
List34.Requery
btnActiveFilter.Caption = "Active"

Case "Inactive"
List34.RowSource = strFilterOnI
List34.Requery
btnActiveFilter.Caption = "InActive"

Case "All"
List34.RowSource = strFilterOff
List34.Requery
btnActiveFilter.Caption = "All"

End Select

End Sub

 

[lmv]

Rastro,
Once again thank you... I am not sure what the problem could be. Your code
makes perfect sense I have tried it... but when I toggle I get NO records...

On open the listbox is on a subform (list34) based on the sql where inactive
is not in the code. (I know we have discussed this but I keep mentioning it
because I don't know if that is part of the problem that it is loading
without the parameter in the sql)

I have tried changing that numerous ways but it made no difference. The list
will load right depending on what filter I put on the inactive field but the
toggle returns no records.

So now when the list loads it has ALL RECORDS (Inactive and ACTIVE) The
toggle is to get out the inactive records which would be filtered by
Inactive=false... now when I clk the toggle button the list is blank.

ugggg! Logically I can't make sense of it.

by the way the list34 has an afterupdate event but I don't know what
difference that would make.

Private Sub List34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List34], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks again!

 

[Rastro]

I forgot to requery the list34

Add this at the right after de "End Sub" of the "Private Sub
ToggleFilter_AfterUpdate()" event:

List34.Requery

Hope now it works.

 

[lmv]

Thank you... I finally figured out the problem
the WHERE LINE was missing a parenthesis

" WHERE ((([Name List].Inactive)= FALSE) " & _

needed to be

" WHERE ((([Name List].Inactive)= FALSE)) " & _

But your suggestions helped! Not sure if my coding is exactly right but it
works!

final solution:
Private Sub ToggleFilter_AfterUpdate()
Dim strFilterOn, strFilterOff As String

'Bring all
strFilterOff = "SELECT [Name List].ID, [Name List].TID, " & _
"[Name List].LastName, [Name List].FirstName, " & _
"[Name List].Status, [Name List].Inactive FROM [Name List]" & _
"ORDER BY [Name List].LastName, [Name List].FirstName "

' Bring only Active records
strFilterOn = "SELECT [Name List].ID, [Name List].TID, " & _
"[Name List].LastName, [Name List].FirstName, " & _
"[Name List].Status, [Name List].Inactive FROM [Name List] " & _
" WHERE ((([Name List].Inactive)= FALSE)) " & _
"ORDER BY [Name List].LastName, [Name List].FirstName "

If ToggleFilter Then
List34.RowSource = strFilterOff
ToggleFilter.Caption = "Filter Off"
List34.Requery
Else
List34.RowSource = strFilterOn
ToggleFilter.Caption = "Filter On"
List34.Requery
End If

End Sub