Multiple Selection List Box as Criterea for Query (1 Viewer)

Jameo · Apr 19, 2011

Hi all, I have a form that filters a query. From that form there are multipe listboxes.

At the moment, in my query, I filter the results using the following in the query criterea

i.e.

[forms]![search]![cboarea]
etc. etc.

However, I would now like to add amultiple selection list box, instead of one of the combo boxes. After scanning around on the web it has become apparent that you can not simply use a list box using the above form criterea. I have found a couple of examples:

Code:

Option Compare Database
Option Explicit
 
Private Sub Command2_Click()
 
   Dim Criteria As String
   Dim i As Variant
 
   ' Build criteria string from selected items in list box.
   Criteria = ""
   For Each i In Me![List0].ItemsSelected
      If Criteria <> "" Then
         Criteria = Criteria & " OR "
      End If
      Criteria = Criteria & "[CustomerId]='" _
       & Me![List0].ItemData(i) & "'"
   Next i
 
   ' Filter the form using selected items in the list box.
   Me.Filter = Criteria
   Me.FilterOn = True
 
End Sub

But this does not seem to work for me. Bear in mind, that as well as selecting information from the list box, I still need to include the criterea from my other combo boxes.

support.microsoft.com/kb/135546 seems to be the most thourgh explanation out there, but I must admit I am a bit stuck.

Any help you can provide would be hugely appreciated. Many thanks

DCrake · Apr 19, 2011

This is a more efficient way of doing it

Code:

   For Each i In Me![List0].ItemsSelected
         Criteria = Criteria & "," & Me![List0].ItemData(i) 
   Next i

'/Drop the leading comma
Criteria = Mid(Criteria,2)

'/Wrap the In() command around the string

Criteria = "Where CustomerId In(" & Criteria & ")"

Jameo · Apr 19, 2011

Thanks for that DCake, much appreciated.

Just to confirm, CustomerID will be my field that will be filtered by the list box? Also, would the above code be placed in my command button module that runs the query?

Many thanks

DCrake · Apr 19, 2011

Code:

Private Sub Command2_Click()
 
   Dim Criteria As String
   Dim i As Variant
 
   ' Build criteria string from selected items in list box.
   Criteria = ""
[B]   For Each i In Me![List0].ItemsSelected
         Criteria = Criteria & "," & Me![List0].ItemData(i) 
   Next i

'/Drop the leading comma
Criteria = Mid(Criteria,2)

'/Wrap the In() command around the string

Criteria = "Where CustomerId In(" & Criteria & ")" [/B]  

 ' Filter the form using selected items in the list box.
   Me.Filter = Criteria
   Me.FilterOn = True
 
End Sub

Jameo · Apr 20, 2011

Again thanks david. I have just one more question. How would I pass this critera string to a query? I do not want the filtering to take place in the actual form.

Thanks

DCrake · Apr 20, 2011

How are you intending to use this query?

Jameo · Apr 20, 2011

Hi DCrake.

Sorry I will try to be a little more clear now.

The SQL string for the query that I wish to use is as follows:

Code:

SELECT [Power Station Data].[Gas Day], [Power Station Data].[Gas Hour Sort Order], [Power Stations].[Power Station Name], [Power Station Data].[Average Flow ( Vol )]
FROM [Power Station Data] INNER JOIN [Power Stations] ON [Power Station Data].[Data Item Name] = [Power Stations].[Power Station Full Name]
WHERE ((([Power Station Data].[Data Item Name]) In ("BarkingPS.F1","BASFInd.F1","BlackburnMInd.F1","BPSaltendHPInd.F2")));

I would like to put the list box specified criterea in place of the ("BarkingPS.F1","BASFInd.F1","BlackburnMInd.F1","BPSaltendHPInd.F2")

The example of the code I have been trying to modify to define the SQL string is:

Code:

Set DB = CurrentDb()
   Set Q = DB.QueryDefs("multistation")
   Q.SQL = "Select * From [Power Station Data] Where [Data Item Name] In(" & Criteria & _
     ");"
   Q.Close

I think I almost have it, but can't seem to stop creating Syntax errors. This something that i have tried:

Code:

Private Sub Command2_Click()
  Dim Q As QueryDef, DB As Database
   Dim Criteria As String
   Dim ctl As Control
   Dim Itm As Variant
   ' Build a list of the selections.
   Set ctl = Me![List0]
   For Each Itm In ctl.ItemsSelected
      If Len(Criteria) = 0 Then
         Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
      Else
         Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
          & Chr(34)
      End If
   Next Itm
   If Len(Criteria) = 0 Then
      Itm = MsgBox("You must select one or more items in the" & _
        " list box!", 0, "No Selection Made")
      Exit Sub
   End If
   '   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("multistation")
   Q.SQL = "SELECT [Power Station Data].[Gas Day], [Power Station Data].[Gas Hour Sort Order], [Power Stations].[Power Station Name], [Power Station Data].[Average Flow ( Vol )]
FROM [Power Station Data] INNER JOIN [Power Stations] ON [Power Station Data].[Data Item Name] = [Power Stations].[Power Station Full Name]
WHERE ((([Power Station Data].[Data Item Name]) In(" & Criteria & _
     ")));"
   Q.Close
 
   ' Run the query.
   DoCmd.OpenQuery "multistation"
 
 
End Sub

I am really close I think, but just can't make that final step. Sorry for the constant questions mate.

Jameo · Apr 20, 2011

Hi mate.

I have managed to get ti right, finally lol.

Code:

Private Sub Command2_Click()
  Dim Q As QueryDef, DB As Database
   Dim Criteria As String
   Dim ctl As Control
   Dim Itm As Variant
   ' Build a list of the selections.
   Set ctl = Me![List0]
   For Each Itm In ctl.ItemsSelected
      If Len(Criteria) = 0 Then
         Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
      Else
         Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
          & Chr(34)
      End If
   Next Itm
   If Len(Criteria) = 0 Then
      Itm = MsgBox("You must select one or more items in the" & _
        " list box!", 0, "No Selection Made")
      Exit Sub
   End If
   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("multistation")
   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("multistation")
   Q.SQL = "Select [Power Station Data].[Gas Day], [Power Station Data].[Gas Hour Sort Order], [Power Stations].[Power Station Name], [Power Station Data].[Average Flow ( Vol )] From [Power Station Data] INNER JOIN [Power Stations] ON [Power Station Data].[Data Item Name] = [Power Stations].[Power Station Full Name] Where [Power Station data].[data item name] In (" & Criteria & _
     ");"
   Q.Close

   ' Run the query.
   DoCmd.OpenQuery "multistation"

Thanks for all your help, you've been invaluable.

Cheers

Manindra · Feb 9, 2013

Jameo said:

Hi mate.

I have managed to get ti right, finally lol.

Code:

Private Sub Command2_Click()
  Dim Q As QueryDef, DB As Database
   Dim Criteria As String
   Dim ctl As Control
   Dim Itm As Variant
   ' Build a list of the selections.
   Set ctl = Me![List0]
   For Each Itm In ctl.ItemsSelected
      If Len(Criteria) = 0 Then
         Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
      Else
         Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
          & Chr(34)
      End If
   Next Itm
   If Len(Criteria) = 0 Then
      Itm = MsgBox("You must select one or more items in the" & _
        " list box!", 0, "No Selection Made")
      Exit Sub
   End If
   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("multistation")
   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("multistation")
   Q.SQL = "Select [Power Station Data].[Gas Day], [Power Station Data].[Gas Hour Sort Order], [Power Stations].[Power Station Name], [Power Station Data].[Average Flow ( Vol )] From [Power Station Data] INNER JOIN [Power Stations] ON [Power Station Data].[Data Item Name] = [Power Stations].[Power Station Full Name] Where [Power Station data].[data item name] In (" & Criteria & _
     ");"
   Q.Close

   ' Run the query.
   DoCmd.OpenQuery "multistation"

Thanks for all your help, you've been invaluable.

Cheers

Hi!

Kindly suggest modification if I have three such combo boxes on the form. Currently I use 5 combo boxes for each (5*3=15 cbos) connected to a query with OR criteria without any coding. I want to replace the 15 combo boxes with only the 3 multiple selection list boxes. I am in learning phase of access 2007. Any help would be greatly appreciated.

armesjr · Sep 17, 2015

Hopefully someone can help me out. Relatively new to the world of VBA. If the answer has already been posted, please point me to the thread. I was able to successfully modify the OP's code to fit my initial needs. Now i am trying to modify the code so that my final query now pulls information in from two separate multi-select list boxes. Any advice on how to go about this would be great.

Code:

Private Sub List_CG_AfterUpdate()

Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant
' Build a list of the selections.
Set ctl = Me![List_CG]
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(32) & ctl.ItemData(Itm) & Chr(32)
Else
Criteria = Criteria & "," & Chr(32) & ctl.ItemData(Itm) _
& Chr(32)
End If
Next Itm
If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If
' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("Test")
' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("Test")
Q.SQL = "SELECT Measure_Categories.Measure_Category_ID, Measure_Categories.Measure_Category FROM Measure_Categories INNER JOIN Percentiles ON Measure_Categories.Measure_Category_ID = Percentiles.Measure_Category_ID WHERE Percentiles.Dept_ID = [forms]![Selection]![Combo_Dept] and Percentiles.CG_ID In (" & Criteria & _
") group by Measure_Categories.Measure_Category_ID, Measure_Categories.Measure_Category;"
Q.Close
' Run the query.
DoCmd.OpenQuery "test"
End Sub

Multiple Selection List Box as Criterea for Query (1 Viewer)

Jameo

New member

DCrake

Remembered

Jameo

New member

DCrake

Remembered

Jameo

New member

DCrake

Remembered

Jameo

New member

Jameo

New member

Manindra

New member

armesjr

New member

Similar threads

Users who are viewing this thread