?
Solved

Ms Access - Filter record on subform based on multiple selection in four listbox

Posted on 2017-08-13
14
Low Priority
?
39 Views
Last Modified: 2017-08-16
Hello,

I have a form that have 4 listbox and subform.I want to filter subform based on multiple section in list box.For one selection it work but not for multiple selection using Master Child Link.Two list box have string and two have numeric value. i want to select multiple values from list box and that filter record below based on selection. I have code for one listbox that work fine but i want to filter based on 4 listbox. Any VBA cod ethat work for 4 listbox multi selection.

Any help would be appreciated.

Thank you.
0
Comment
Question by:W S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 85
Do you mean that users may select multiple items in a single listbox?

Or that users may select a single item in each of 4 listboxes?
0
 

Author Comment

by:W S
User should select multiple items in multiple Listbox. For Example:
Listbox1 -> Select A+ Select B
Listbox2 -> Select C+ Select D
Listbox3 -> Select E+ Select F
Listbox4 -> Select G+ Select H

....
Also attach with this is example DB in which frmListBox is the one that have 3 ListBox and i want that it should select multiple item from multiple listbox.

Thank you for help in advance.
Example_DB--1-.accdb
0
 
LVL 85
I often find it easiest to create a routine that will filter records when the user clicks a button, or updates a control. Something like this:

Dim var As Variant
Dim filter As String

If Me.Country.ItemsSelected.Count > 0 Then
    For Each var In Me.Country.ItemsSelected
        If Len(filter) > 0 Then
            filter = filter & " OR "
        End If
        filter = "Country='" & Me.Country.ItemData(var) & "'" & filter
    Next var
End If

If Me.lstYear.ItemsSelected.Count > 0 Then
    For Each var In Me.lstYear.ItemsSelected
        If Len(filter) > 0 Then
            filter = filter & " OR "
        End If
        filter = "Year=" & Me.lstYear.ItemData(var) & "'" & filter
    Next var
End If

Me.qryRecent_subform.Form.filter = filter
Me.qryRecent_subform.Form.FilterOn = True

Open in new window

You would obviously have to change Control, Object, Table and Field names to match your own, but essentially that's how you'd do this. You should also remove the Master/Child links to the subform.

From there, you'd call the routine when you needed to filter the records. For example, you could add a Filter button and call code like this in the Click event.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:W S
@Scott McDaniel (Microsoft Access MVP - EE MVE ), Can you add this in example DB like how this will work because i can't understand few things in this. Also i have 4 list box and i think this will work for two. yes i can go on with thing add a button and then that filter out that more easy and efficient, but how that will work for four, can you explain bit more. Thank you.
0
 

Author Comment

by:W S
It is also giving Run Time Error: '3075' (Syntax Error missing operator in query expression). Any idea about that.
0
 
LVL 85
We're here to help you, not do your work for you. If you want someone to do the work for you, open a Gig or a Live Request.

The error is most likely coming from this line:

        filter = "Year=" & Me.lstYear.ItemData(var) & "'" & filter

It should be:

        filter = "Year=" & Me.lstYear.ItemData(var) & filter

Assuming Year is a numeric value.
0
 

Author Comment

by:W S
Sure,Thank you for your help , also the error is still there so it's not from this line.
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
You'll have to change the names of the Controls - i.e. the "lstYear" - to match your project. This is just an example of the method you can use to do what you want.

I've attached your sample with the modifications. This looks at two of the listboxes. You can use those as examples to add filtering for others.
Example_DB--1-.accdb
1
 

Author Comment

by:W S
lisbox-error2.pngHave a look at image, it's not filtering correctly

Also yeah i understand this is an example because my original database is too complex , it's having 40 table with 40 forms and relationship stuff. If these two listbox work fine i will deal with the rest, thanks for your help again.
0
 
LVL 85
The function I provided uses the OR operator, so the above selections would give you any record where Country=New1 OR Year =2000 OR Year=2003. If you want to filter for only those records where Country=NEW1 AND Year=2000 AND Year=2003, then change the functions to use the AND keyword instead of the OR keyword. From what I can see, using the AND keyword would result in  no records being returned (because of the Product selection, if you've included that) If you haven't included the PRoduct selection yet, then you'd return 1 record..
1
 
LVL 85
Anytime you receive an error, you should post details of that error (screenshot is best).

When I change the two instances of OR in the code (in the database I sent) to "AND", the result filters correctly. I did not include the Product in my filtering, and when I select NEW1, 2000, and 2003 I see 1 result as expected.

If you've changed the code, you'd have to post that changed code in order for us to review.
0
 

Author Comment

by:W S
Attach is the DB and image also. LISBOX-ERROR4.png As you can see in image selecting Country and Year is not displaying any record although there are record for specific country and year in qryRecent. Attach in DB you can have a look at it too.

I remove Product also for the time being , if these two work i will make other work too.

It should filter these record based on listbox selection:
ID      Country      Product        Years      Values                Source                     Timestamp            
13      NEW1      DEF                  2003        0                  SOU3                  8/16/2017                        
14      NEW1      DEF                       2003        23                  SOU4                  8/15/2017                        
3      NEW3      ABC                  2001        0                  SOU3                  8/2/2017                        
1      NEW3      ABC                2003        0                  SOU4                  8/1/2017      
                  
Thank you for your time and effort.
Example_DB--1---1-.accdb
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
My point is - the function is doing what it's intended. If you need to make changes to the way it filters you'd have to make code changes. I don't get an error when I use the example database, but if I select NEW1 AND 2000 AND 2003, I don't return any records (nor would I expect to, since there are no records with both 2000 AND 2003 in the Years column).

If you want to change to logic where you show any record in NEW1 and 2000 OR NEW1 and 2003 you can do that, but it requires a different function. You'd have to (a) loop through the first listbox to grab the first selection and then (b) loop through the second listbox to AND the Years to the each of those selections and then (c) OR those back to a "master" filter string. As you add listboxes, you'll have to introduce more "sub loops" for each "primary" loop, and that can get very, very tricky.

So something like this:

    
    Dim var    As Variant
    Dim filter As String

    If Me.Country.ItemsSelected.Count > 0 Then
        For Each var In Me.Country.ItemsSelected
            Dim ctry As String
            '/ get the initial Country filter:
            ctry = "Country='" & Me.Country.ItemData(var) & "'"
            '/ now loop through lstYear, and add that to the ctry string:
            If Me.lstYear.ItemsSelected.Count > 0 Then
                Dim varyear As Variant
                For Each varyear In Me.lstYear.ItemsSelected
                    '/ since this is the "last" filter listbox, we can go ahead and
                    '/ add the string to the "master" filter string:
                    If Len(filter) > 0 Then
                        filter = filter & " OR "
                    End If
                    filter = filter & "(" & ctry & " AND Years=" & Me.lstYear.ItemData(varyear) & ")"
                Next varyear
            End If
        Next var
    End If

    Me.qryRecent_subform.Form.filter = filter
    Me.qryRecent_subform.Form.FilterOn = True

Open in new window

If you want to add other listboxes, like your Product listbox, and you want to further "sub filter", you'd have to add another loop inside the lstYear loop.
1
 

Author Comment

by:W S
Thank you for your help, i will implement this in my operational database. Thanks again.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question