Multiple Listbox Selection for Query

[rakotonirinas]

Dear specialists and users,

I'm very new to Access and we can say that I'm a self-taught one so not much of an experience right now but I'm loving the wonder it can achieve.

Right now I'm trying to achieve what I would consider as something not too difficult in the Excel world but somehow I cannot manage to make it happen on Access which I believe should not be as difficult as I may think.

On the attached database, I have 2 tables, 1 form and 1 query. The form is used to extract the corresponding for the query.

Objective: The aim of the form is to select one or more items from the listbox and that will be recognised in the criteria within the query.

Issue: I know how to display just ONE item on the criteria but cannot figure out what to do when I have MORE THAN ONE to select as to be part of the criteria.

So let's say, I would like to run the list of all contacts for all center states only, i.e., CN, CE, CS and CW. That should display as the query result of 207 contacts from the main data list.

I would like to use the form to enable that quick selection particularly if there is a long list to choose from.

What would be the best way to achieve this please?

Your help is much appreciated.

Rako.

 

[theDBguy]

Hi Rako. Welcome to AWF! Can't download your file at the moment, but someone else should be along shortly. To do what you want, you'll need to use a multi select listbox and then loop through the selected items collection.

 

[MajP]

This uses a class module that can be used on any multi select listbox.

 

[Pat Hartman]

Here's another method showing three variations. Plus several other useful samples.

 

[rakotonirinas]

Hello Pat Hartman and MajP.

Thank you so very much for sharing these example files. I have't had time to dig dip into it but by looking at what they can achieve, it's very impressive.

I will look through and will report back later on.

Much much appreciated.

 

[rakotonirinas]

Hi Pat Hartman,

Fantastic work you did there with the sample file. Thank you very much.

Quick question please about the Switchboard list. What would be the prime difference between Filter by Multi-Select ListBox, Filter Subform by Multi-SelectListbox and Modify RecordSource by Multi-Select ListBox?

From the layout, they all look pretty much the same to me. However, I believe there would be a noticeable difference in the background.

Much appreciated.
Rako

 

[rakotonirinas]

Hello,
Just a quick question on this one. Can I not just input a call (formula) like the one below, as if it was a textbox, in the Criteria field of the Query (Contacts Query) corresponding to Region column? Or do I really have to write a code to enable the list.

=[Forms]![Region]![List3]

My preference is the former to keep things simple but this may not be possible?

 

[rakotonirinas]

This uses a class module that can be used on any multi select listbox.

Hello MajP,

Thanks for your input and suggestion. I like it.
Can this be expanded to include more than one field to filter, that is to say, let's say I would like to filter both the region and the city (2 fields)?

I'd appreciate if you could show me the way to get to that direction.

Cheers!
Rako

 

[MajP]

This is a little trickier because you have to filter the cities in the second combobox. If you pick NW then the city combo should only show the cities in NW. This is not part of the class so you have to add this function in. Also each listbox returns its filter and now you have to combine into a totals filter. You can add a third for state. The effort is the same just more work.

Option Compare Database
Option Explicit
Private WithEvents MSLF As MultiSelectFilter
Private WithEvents MSLF_City As MultiSelectFilter
Private Sub Form_Load()
  Set MSLF = New MultiSelectFilter
  Set MSLF_City = New MultiSelectFilter
  MSLF.Initialize Me.lstRegions
  MSLF_City.Initialize Me.lstCity
End Sub

Private Sub MSLF_City_FilterChange(TheFilter As String)
  FilterForm

End Sub

Private Sub MSLF_FilterChange(TheFilter As String)
  'Need to filter the multiselect so if you pick a region it only shows cities in that region
  Dim strsql As String
  
  strsql = "SELECT DISTINCT [Contacts Query].City FROM [Contacts Query]"
  If TheFilter <> "" Then
    strsql = strsql & " WHERE " & TheFilter
  End If
  Me.lstCity.RowSource = strsql
  Me.lstCity.Requery
  
  FilterForm
End Sub

Public Sub FilterForm()
  Dim regionFilter As String
  Dim cityFilter As String
  Dim totalFilter As String
  regionFilter = MSLF.Filter
  cityFilter = MSLF_City.Filter
  'combine the filters
  If regionFilter <> "" And cityFilter <> "" Then
    totalFilter = regionFilter & " AND " & cityFilter
  ElseIf regionFilter <> "" Then
    totalFilter = regionFilter
  ElseIf cityFilter <> "" Then
    totalFilter = cityFilter
  End If
  
  Me.Filter = totalFilter
  Me.FilterOn = True
End Sub

 

[Pat Hartman]

@Rako,
Each form gives a short explanation of how it works. Here's a slightly different version:

1. Filter by Multi-Select ListBox --
This is a main form with the selection criteria in the header. Selecting items from the listbox and pressing the Apply button changes the Filter property of the main form.
2. Filter Subform by Multi-Select Listbox --
This is a mainform with the selection criteria in the header with a subform that displays the data. Selecting items from the listbox and pressing the Apply button modifies the RecordSource of the sub form by adding an In() clause. See more comments in #3
3. Modify RecordSource by Multi-Select Listbox--
This is a main form with the selection criteria in the header. Selecting items from the listbox and pressing the Apply button modifies the RecordSource of the main form by adding an In() clause to select only the selected items. This is the method I would normally use since most of my BE databases are RDBMS rather than Jet/ACE and so I never use form filters. I always use criteria in the query which transfers the work to the server rather than doing the filtering locally. Selecting ALL rows and filtering locally, which is how many traditional Access apps are built defeats the entire purpose of linking to server-side tables. To get any advantage of using a real server based database,you actually have to get the server to do the work rather than the local app.

All of these examples can easily be modified to add multiple criteria. You just need to include validation logic in the Apply button to either require all criteria or ignore any that are not selected as you build the WHERE clause or in the first case, the form's Filter property.