Filter Listbox by Checkboxes

[Kovacs]

Hi everybody
I've been looking for a long time on internet (and this forum) in order to solve this problem. I am new at coding and it's very difficult to get a result with only partial informations. Hopefully this will help a lot of people having the same issue.

I wish to filter the records in a listbox by using checkboxes.
The listbox (list1) and the checkboxes are in the same form (form1)
The listbox displays the records from a query (query1) related to a single table (table1).
Each record (assuming a list of videos) has a field called "Typology". The "video typologies" are: Movie, Documentary, Animation. So the table1 has a column called "Typology" which displays one of the three typology in simple text.
When entering a new record (in a specific form) user can choose the typology selecting an option in a combobox. The combobox is a "value list" one and I wrote the typologies in the "row origin" line: "Movie";"Documentary";"Animation"
So we have a single column in simple text for all the typologies, but limited choiches.
So far everything is working well.

Now I wish to filter the records in the listbox by typology. Each typology should be related to a checkbox, so when I check a checkbox then the listbox returns only the records form a specific typology. Also, I wish to check more then one checkbox in order to display records from multiple typologies rather than one.

I've already made a button which resets the form, displaying once again all the records and eventually unchecking all the checkboxes.

About option buttons:
I've managed filtering the listbox results using option buttons in a frame (option group?) and it works fine, but in this way I can choose only one typology at a time (and the typology field in the tabel1 of course is not simple text, but numeric: 1=Movie, 2=Documentary etc).

I thought this could be solved using the click event on each checkbox:
-If the checkbox "Movie" is checked then the listbox shows only the records which have the text "Movie" in the Typology field. Else, show all the records.
-If the checkbox "Documentary" is checked then the listbox shows only the records which have the text "Documentary" in the Typology field.
Etc......

But really don't know how to write this code!! :banghead:
I'm sure that the solution can be easy for a real programmer.. I am at the very beginning!

Thank you in advance for your help.

 

[michaeljryan78]

I know that you said that you would like a series of checkboxes, but this may work. Create 2 listboxes (list2), (list3) and set the values list in list2 to the topologies that you wan to filter. here is a sub to help you out a bit. I didn't test it though. May give you an idea.

    Dim strRowSource As String
    Dim i As Integer
    Dim strValue As String
 
    strRowSource = "Select Typology From myQuery "
      If Me.list2.ItemsSelected.Count > 0 Then
        strRowSource = strRowSource & "Where Typology = "
        For i = 0 To Me.list2.ListCount - 1
            If list2.Selected(i) Then
                  strValue = strValue & list2.ItemData(i)
                  strRowSource = strRowSource & quote(strValue) & " or typology = "
                  Debug.Print strRowSource
            End If
        Next i
    strRowSource = Left(strRowSource, Len(strRowSource) - 15)
    End If
    Debug.Print strRowSource
    Me.list3.RowSource = strRowSource
    Me.list3.Requery

and here is a quote function that I use when I am taking string variables and using them in VBA ( the above routine uses it). Paste this in a new module:

Function quote(str As Variant) As String
    If IsNull(str) Then
        quote = "null"
    Else
        quote = """" & Replace(Trim(str), """", "'") & """"
    End If
End Function

 

[michaeljryan78]

I editted the previous post to correct the code, sorry.

 

[Kovacs]

Thank you michaeljryan78 for your feedback
Unfortunately I have some requests for this database to be fulfilled so I can't change the form structure in that way.
Besides adding new listboxes will definitively destroy the form layout.

I think the solution should be more simple. I'm wandering why it comes so hard to sort it out. Filtering items in a list by checkboxes should be an important part in many databases or inventories.

Basically I'm asking this:
how to filtrate records in a listbox by using a unique keyword (a simple text field) which is in the table itself? Since each checkbox is related to a keyword, clicking the checkbox will display only the records conteining the keyword.

This is driving me crazy. :banghead: :banghead: :banghead: :banghead:

 

[michaeljryan78]

What version of access? How many check boxes? Can you add a command button to trigger the filter event?

I was thinking about using the checkbox caption as a string value for the filter. do you forsee a problem with that?

 

[michaeljryan78]

Add the filter for topology to the Tag property of the checkbox. for example Animation. Do not use quotes in the Tag Property of the checkbox

First we see if there are any filters checked by looking at the sum of the values (-1 is True) if the sum is zero, no checks.

Then we loop the controls of the form and find the tag property and start to build a where clause based on the value in Tag. We ignore controls that dont have a tag property assigned.

I have attached a quick and dirty sample in Access 2010

Private Sub Command8_Click()
 
Dim ctrl As Control
Dim strRowSource As String
strRowSource = "Select Typology From myQuery "
If Me.Check2 + Me.Check4 + Me.Check6 <> 0 Then
 
    strRowSource = strRowSource & "WHere typology = "
    For Each ctrl In Me.Controls
        Debug.Print ctrl.Name, ctrl.Tag
        Select Case ctrl.Tag
            Case Is <> ""
                If ctrl.Value = -1 Then
                    strRowSource = strRowSource & quote(ctrl.Tag) & " or typology = "
                    Debug.Print strRowSource
                End If
        End Select
    Next ctrl
    strRowSource = Left(strRowSource, Len(strRowSource) - 15)
 
End If
Me.List0.RowSource = strRowSource
Me.List0.Requery
End Sub
Function quote(str As Variant) As String
    If IsNull(str) Then
        quote = "null"
    Else
        quote = """" & Replace(Trim(str), """", "'") & """"
    End If
End Function

 

[Kovacs]

Thank you again michaeljryan78 for your help!
Actually I tried your solution which seemed to be brilliant.. but something is wrong.
In fact if you run your database and try to enter new informations in the table and then apply the filter, all the data disappear, except for the typology field.
Seems like the filter just replaces the data from tblData with the data from tblTopology you made. Only the amount of records is correct! And in the listbox, all the data from the other columns disappear.
Sorry for my inexperience, I really don't know how to fix this.

 

[Kovacs]

See if this helps.

lol AccessBlaster thank you for your concern
The database you sent is one of the models I used to build mine. That's awesome and really helped me to figure out how to code a database (thank you to John Big Booty by the way...).
told you, I've been searching for a long time on internet to find a solution......

Unfortunately that document works with option buttons instead of checkboxes. This allows the user to choose only one filter at a time (see the original post "About option buttons").

I tried in many ways to adapt the "JBBooty database" to my purpose, but I don't know how to turn the option buttons into checkboxes. Using single checkboxes instead of a frame with option buttons should give to the user a multiple choice.

Seems there's something wrong with it

 

[michaeljryan78]

There is only the typology field because that is the only filed I chose in the select statement in strResource. If you need other fields, just add them to the select statement.

 

[michaeljryan78]

StrResource = "select typology, fld, fld2, fld3 from myquery "

 

[Mihail]

I'm wandering why it comes so hard to sort it out. Filtering items in a list by checkboxes should be an important part in many databases or inventories.

Do not be so sure.
Because you have NOW only 3 typology (Movie, Documentary, Animation) not mean that this is a limitation.
If you change your mind you can define more, isn't it ? (biography, SF, Action etc)
That mean that you need to add 3 more check boxes (and code to handle this).
If you change again your mind and don't like SF then you must remove one check box (and the code that handle this). No finish term here .

I'll quote you again:

Unfortunately I have some requests for this database to be fulfilled so I can't change the form structure in that way.
Besides adding new listboxes will definitively destroy the form layout

Who have designed that form layout ? If he/she is a better programmer than you are why do not HE (or SHE) accomplish the task ?

Ok. Enough with quotes.

To use check boxes to filter your list is (at least in your case and in my opinion) far away from the best solution.
I advice you to design a new table
tblTypology
ID_Typology (PK - AutoNumber)
Typology (Text)

Your field Typology should be a lookup on this table.
Next, a listbox in the form should have also this table as row source and must allow multiple selection.
Using the item selected in this list, should be a simple task to create a filter.

Upload your database and I'll try to tweak it.

Good luck.

 

[Kovacs]

OK I'm posting the database, called Inventory
Please remember: it's just an example, not the entire database. Don't pay attention to the content, it's just for show. What I need is to filter records by Category.

MichealJRyan78:
I've tried your solution, it's still in the code. You were right of course about displaying more columns, and your database was correct, but something is not working with mine, I don't know why. Maybe the problem is the relationship among tabels, query and listbox... ??? also reset button doesn't work

AccessBlaster:
Maybe you're right about PK and relationship, but I really don't know what do to and how use the code you sent... sorry

Thanks again and forgive my poor english, perhaps I made some mistakes