Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2017-08-13
14
Low Priority
?
58 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:WS
[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
ID: 42252816
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
 
LVL 1

Author Comment

by:WS
ID: 42252825
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
ID: 42252841
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
Industry Leaders: 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!

 
LVL 1

Author Comment

by:WS
ID: 42252845
@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
 
LVL 1

Author Comment

by:WS
ID: 42252851
It is also giving Run Time Error: '3075' (Syntax Error missing operator in query expression). Any idea about that.
0
 
LVL 85
ID: 42252888
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
 
LVL 1

Author Comment

by:WS
ID: 42252890
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
ID: 42252910
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
 
LVL 1

Author Comment

by:WS
ID: 42254877
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
ID: 42255110
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
ID: 42255135
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
 
LVL 1

Author Comment

by:WS
ID: 42255152
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
ID: 42255170
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
 
LVL 1

Author Comment

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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

618 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