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

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?

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

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

Select allOpen 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.

@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.

It is also giving Run Time Error: '3075' (Syntax Error missing operator in query expression). Any idea about that.

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.

Sure,Thank you for your help , also the error is still there so it's not from this line.

Have 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.

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..

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.

Attach is the DB and image also. 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

Thank you for your help, i will implement this in my operational database. Thanks again.