multiple selection list box to open query not working (1 Viewer)

[ebs17]

Your pictures are meaningless to me.

There are several ways to incorporate multiple selection into a filter. It is always useful to first consider what a finished, functioning filter should look like before attempting to put it together and integrate it using VBA.
Your version should look like this (alphanumeric content):

=> for installation in VBA string with single quotes instead of the possible double quotes
WHERE FieldX IN ('a23', 'gg5', 'fd67')

This means:

    For i = 0 To Me.CodigoMasa.ListCount - 1
        If Me.CodigoMasa.Selected(i) Then
            strCodigoMasa = strCodigoMasa & Chr(39) & Me.CodigoMasa.Column(0, i) & Chr(39) & ","
        End If
    Next i

Include in query

sSQL = "SELECT * FROM TableY WHERE FieldX IN (" & strCodigoMasa & ")"

Compared to other methods (use of Instr or LIKE), the use of an existing index on FieldX is supported here, so this method is a better choice.

 

[arnelgp]

same thing here:

Dim lst As Access.ListBox
Dim varItem As Variant
Dim strBuild As String
Set lst = Forms![yourFormName]![yourListboxName]
If lst.ItemsSelected.Count > 0 Then
  For Each varItem In lst.ItemsSelected
    strBuild = strBuild & """" & lst.ItemData(varItem) & ""","
  Next varItem
    Me![txtSelected] = Left$(strBuild, Len(strBuild) - 1)
End If

 

[abelmumo]

Your pictures are meaningless to me.

There are several ways to incorporate multiple selection into a filter. It is always useful to first consider what a finished, functioning filter should look like before attempting to put it together and integrate it using VBA.
Your version should look like this (alphanumeric content):

=> for installation in VBA string with single quotes instead of the possible double quotes
WHERE FieldX IN ('a23', 'gg5', 'fd67')

This means:

    For i = 0 To Me.CodigoMasa.ListCount - 1
        If Me.CodigoMasa.Selected(i) Then
            strCodigoMasa = strCodigoMasa & Chr(39) & Me.CodigoMasa.Column(0, i) & Chr(39) & ","
        End If
    Next i

Include in query

sSQL = "SELECT * FROM TableY WHERE FieldX IN (" & strCodigoMasa & ")"

Compared to other methods (use of Instr or LIKE), the use of an existing index on FieldX is supported here, so this method is a better choice.

Thank you for replying.

I understand that the filter content was not correctly built, it was lacking the """ to indicate that the elementes selected are texts. I added Char(34) instead of Char(39) since in the Spanish version, text content should be indicated between " and not between '. So now, the code used for contatenating the selected elements of the listbox looks like:

Private Sub btnCodigoMasa_Click()
    Dim i As Integer
    Dim strCodigoMasa As String
    
    For i = 0 To Me.CodigoMasa.ListCount - 1
        If Me.CodigoMasa.Selected(i) Then
            strCodigoMasa = strCodigoMasa & Chr(34) & Me.CodigoMasa.Column(0, i) & Chr(34) & ";"
        End If
    Next i

    ' Verificar si strCodigoMasa no está vacío antes de eliminar el último carácter
    If Len(strCodigoMasa) > 0 Then
        Me.txtCodigoMasa = Left(strCodigoMasa, Len(strCodigoMasa) - 1)
    Else
        Me.txtCodigoMasa = ""
    End If
End Sub

However I know I am failing to understand where in my query I should include the code sSQL = "SELECT * FROM TableY WHERE FieldX IN (" & strCodigoMasa & ")"

I am currently using "txtCodigoMasa" as criteria for filtering the fierld COD_MASA_AGUA on my query, but now it does not work even for a single result, since Access tries to find in that Field something that looks like ""alphanumericcode"" instead of "alphanumericcode". I think Access is understanding that string as a unique text and not a combination of different texts delimited by ";", as used for IN() function in Spanish Access.

Could you please help me understand how to include that in my query? My query (which has also other criteria for other fields) has this code so far:

SELECT FIC_SUBPROGRAMAS.ID_SUBPROGRAMA AS ID, FIC_SUBPROGRAMAS.DESCRIPCION AS DESCRIPCIÓN
FROM FIC_SUBPROGRAMAS LEFT JOIN (((FIC_MUNICIPIOS RIGHT JOIN FIC_PUNTOS_MUESTREO ON FIC_MUNICIPIOS.COD_INE = FIC_PUNTOS_MUESTREO.CHT_COD_INE) LEFT JOIN ((FIC_ESTACIONES RIGHT JOIN FIC_ESTACIONES_PUNTOS_MUESTREO ON FIC_ESTACIONES.COD_ESTACION = FIC_ESTACIONES_PUNTOS_MUESTREO.COD_ESTACION) LEFT JOIN (FIC_MASAS_AGUA RIGHT JOIN FIC_MASAS_AGUA_ESTACIONES ON FIC_MASAS_AGUA.COD_MASA_AGUA = FIC_MASAS_AGUA_ESTACIONES.COD_MASA_AGUA) ON FIC_ESTACIONES.COD_ESTACION = FIC_MASAS_AGUA_ESTACIONES.COD_ESTACION) ON FIC_PUNTOS_MUESTREO.COD_PUNTO_MUESTREO = FIC_ESTACIONES_PUNTOS_MUESTREO.COD_PUNTO_MUESTREO) RIGHT JOIN FIC_SUBPROGR_PUNTO_MUESTREO ON FIC_PUNTOS_MUESTREO.COD_PUNTO_MUESTREO = FIC_SUBPROGR_PUNTO_MUESTREO.COD_PUNTO_MUESTREO) ON FIC_SUBPROGRAMAS.ID_SUBPROGRAMA = FIC_SUBPROGR_PUNTO_MUESTREO.ID_SUBPROGRAMA
WHERE (((FIC_MASAS_AGUA.COD_MASA_AGUA)=[Forms]![1_SelectorConsulta]![txtCodigoMasa]) AND ((FIC_MUNICIPIOS.NOM_MUNI)=[Forms]![1_SelectorConsulta]![Municipio] Or [Forms]![1_SelectorConsulta]![Municipio] Is Null) AND ((FIC_MUNICIPIOS.NOM_PROV)=[Forms]![1_SelectorConsulta]![Provincia] Or [Forms]![1_SelectorConsulta]![Provincia] Is Null) AND ((FIC_MUNICIPIOS.NOM_CCAA)=[Forms]![1_SelectorConsulta]![CCAA] Or [Forms]![1_SelectorConsulta]![CCAA] Is Null) AND ((FIC_MASAS_AGUA.ARTICULO)=[Forms]![1_SelectorConsulta]![Articulo] Or [Forms]![1_SelectorConsulta]![Articulo] Is Null))
GROUP BY FIC_SUBPROGRAMAS.ID_SUBPROGRAMA, FIC_SUBPROGRAMAS.DESCRIPCION;

Sorry for the inconveniene, I know I must be struggling to understand something really basic, but I am quite new to this challenging work

 

[ebs17]

In my example, I incorporated the listing directly into the query.

You place the listing in a form text field, which is itself built into the query. A text field provides exactly ONE content (entire string), no longer a list.
If you stick to this strategy, you will need to redesign the filtering in the query.
Basic view:

WHERE Instr(1, "|a23|gg5|d67|", "|" & FIC_MASAS_AGUA.COD_MASA_AGUA & "|") > 0
' or
WHERE "|a23|gg5|d67|" LIKE "*|" & FIC_MASAS_AGUA.COD_MASA_AGUA & "|*"

This means that a entire string can be passed as a parameter and evaluated. The delimiter pipe used as an example is used to filter only the exactly desired content, since both methods also use partial content as hits and therefore the result without this delimiter can be incorrect.

Can you now assemble the changed string on your own and also change the query definition?

 

[abelmumo]

In my example, I incorporated the listing directly into the query.

You place the listing in a form text field, which is itself built into the query. A text field provides exactly ONE content (entire string), no longer a list.
If you stick to this strategy, you will need to redesign the filtering in the query.
Basic view:

WHERE Instr(1, "|a23|gg5|d67|", "|" & FIC_MASAS_AGUA.COD_MASA_AGUA & "|") > 0
' or
WHERE "|a23|gg5|d67|" LIKE "*|" & FIC_MASAS_AGUA.COD_MASA_AGUA & "|*"

This means that a passed overall string can be passed as a parameter and evaluated. The delimiter pipe used as an example is used to filter only the exactly desired content, since both methods also use partial content as hits and therefore the result without this delimiter can be incorrect.

Can you now assemble the changed string on your own and also change the query definition?

I do not understand

Following your strategy, how can I also incorporate the listing directly into my query? I was only using the textbox to see if the code was working properly and all elements were being selectec but I do not need it at all, I only want that multiple selection to filter the results of my query.

My problem with your previous solution is that I am not understanding where to use your code "sSQL = "SELECT * FROM TableY WHERE FieldX IN (" & strCodigoMasa & ")"" in my query. If it is a part of the code I am executing to construct the string with the selected elements, how can that affect the other query? How can I relate the code and the query? I was trying to generate the string in the textbox and use the textbox content to later run the query...

Thank you again for your help and efforts, I know it might be difficult to explain this to be understood by someone with short experienc such as me.

 

[ebs17]

how can I also incorporate the listing directly into my query?

Since the selection can be different (none to many), the integration must be dynamic, which means that the SQL string, which is just some text before its actual use, is assembled using simple string operations.

Example code (may still contain errors)

Sub DoIt()
    Dim sSQL As String
    Dim i As Long
    Dim strCodigoMasa As String
    
    ' SQL statement with placeholder [%1]
    sSQL = "SELECT FIC_SUBPROGRAMAS.ID_SUBPROGRAMA AS ID, FIC_SUBPROGRAMAS.DESCRIPCION AS DESCRIPCIÓN" & _
       " FROM FIC_SUBPROGRAMAS LEFT JOIN ( ( (FIC_MUNICIPIOS RIGHT JOIN" & _
       " FIC_PUNTOS_MUESTREO ON FIC_MUNICIPIOS.COD_INE = FIC_PUNTOS_MUESTREO.CHT_COD_INE" & _
       " ) LEFT JOIN ( (FIC_ESTACIONES RIGHT JOIN FIC_ESTACIONES_PUNTOS_MUESTREO ON" & _
       " FIC_ESTACIONES.COD_ESTACION = FIC_ESTACIONES_PUNTOS_MUESTREO.COD_ESTACION )" & _
       " LEFT JOIN (FIC_MASAS_AGUA RIGHT JOIN FIC_MASAS_AGUA_ESTACIONES ON" & _
       " FIC_MASAS_AGUA.COD_MASA_AGUA = FIC_MASAS_AGUA_ESTACIONES.COD_MASA_AGUA ) ON" & _
       " FIC_ESTACIONES.COD_ESTACION = FIC_MASAS_AGUA_ESTACIONES.COD_ESTACION ) ON" & _
       " FIC_PUNTOS_MUESTREO.COD_PUNTO_MUESTREO =" & _
       " FIC_ESTACIONES_PUNTOS_MUESTREO.COD_PUNTO_MUESTREO ) RIGHT JOIN" & _
       " FIC_SUBPROGR_PUNTO_MUESTREO ON FIC_PUNTOS_MUESTREO.COD_PUNTO_MUESTREO =" & _
       " FIC_SUBPROGR_PUNTO_MUESTREO.COD_PUNTO_MUESTREO ) ON" & _
       " FIC_SUBPROGRAMAS.ID_SUBPROGRAMA = FIC_SUBPROGR_PUNTO_MUESTREO.ID_SUBPROGRAMA" & _
       " WHERE ( [%1]" & _
       " AND ((FIC_MUNICIPIOS.NOM_MUNI) =" & _
       " [Forms]![1_SelectorConsulta]![Municipio] OR" & _
       " [Forms]![1_SelectorConsulta]![Municipio] Is Null ) AND (" & _
       " (FIC_MUNICIPIOS.NOM_PROV) = [Forms]![1_SelectorConsulta]![Provincia] OR" & _
       " [Forms]![1_SelectorConsulta]![Provincia] Is Null ) AND (" & _
       " (FIC_MUNICIPIOS.NOM_CCAA) = [Forms]![1_SelectorConsulta]![CCAA] OR" & _
       " [Forms]![1_SelectorConsulta]![CCAA] Is Null ) AND ( (FIC_MASAS_AGUA.ARTICULO) =" & _
       " [Forms]![1_SelectorConsulta]![Articulo] OR" & _
       " [Forms]![1_SelectorConsulta]![Articulo] Is Null ) )" & _
       " GROUP BYFIC_SUBPROGRAMAS.ID_SUBPROGRAMA, FIC_SUBPROGRAMAS.DESCRIPCION ;"
      
    ' generation filter expression
    For i = 0 To Me.CodigoMasa.ListCount - 1
        If Me.CodigoMasa.Selected(i) Then
            strCodigoMasa = "," & strCodigoMasa & Chr(39) & Me.CodigoMasa.Column(0, i) & Chr(39)
        End If
    Next i
    If Len(strCodigoMasa) > 0 Then strCodigoMasa = "FIC_MASAS_AGUA.COD_MASA_AGUA IN (" & Mid(strCodigoMasa, 2) & ")"
    Debug.Print strCodigoMasa           ' for control
      
    If Len(strCodigoMasa) > 0 Then
        sSQL = Replace(sSQL, "[%1]", strCodigoMasa)
    Else
        sSQL = Replace(sSQL, "[%1]", "True")
    End If
      
    ' assignment as data origin
    Forms("frmAnyOne").RecordSource = sSQL
      
End Sub

 

[Josef P.]

Note: (OT or not OT )
For filtering with multiple selection, I like the multi-value controls (bound to a multi value data field).

The filter values can be accessed as an array directly via the control or the n:m table generated by Access can be used for a query.
The parameter of the parameter query is then not the selected values but the ID of the recordset used in the background with the MVF.

SELECT
      ...
FROM
      HelpTabWithMVF TabMVF
      inner join
      TabToFilter T On T.fkXyz = MVF.XyzIdFilters.Value
where
      TabMVF.ID = [IdOfFilterRecord]

Example: https://github.com/josef-poetzl/access-examples/blob/main/data/MultiValueFilterControl.zip
... A form reference is used instead of the parameter. (However, this could easily be changed to parameter.)

 

[Pat Hartman]

Here's an example that shows three variations on how the In() clause is used (2 - filter, 3 - change the Recordsource of the main form, and 4 - change the recordsource of the subform) In the example, the PK is numeric but as you have discovered, the technique is the same for a string, you just have to concatenate the quotes into the string.

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

 

[abelmumo]

Note: (OT or not OT )
For filtering with multiple selection, I like the multi-value controls (bound to a multi value data field).
View attachment 112242
The filter values can be accessed as an array directly via the control or the n:m table generated by Access can be used for a query.
The parameter of the parameter query is then not the selected values but the ID of the recordset used in the background with the MVF.

SELECT
      ...
FROM
      HelpTabWithMVF TabMVF
      inner join
      TabToFilter T On T.fkXyz = MVF.XyzIdFilters.Value
where
      TabMVF.ID = [IdOfFilterRecord]

Thank you Josef P.,

My problem there is that the items to be selected by the user depend themselves on the result of a previous query, therefore I can not make a unique design of a form with the multi-value controls (as far as I know).

I will keep this on mind for selections of fixed items
Best regards

 

[abelmumo]

Hi again everyone!

I think I made sense of it all and make it work, so I am going to post the procedure that I made with general names, so maybe it can help anyone facing this same problems in the future.

Please be aware that my knowledge on VBA is VERY limited, and there might be a more sofisticated way to reach this same goal. If you notice any possible improvement, please do not hesitate to post it, it will be very well appreciated.

In the form where you want the user to pick different elements on a listbox (and after allowing the multi-selection in listbox properties) you must create a button with this code on it's clic event:

Private Sub NAMEOFBUTTON_Click()
'This first part of the code will create the SQL instruction of a query filtering only the selected results'  
    Dim i As Integer
    Dim strS As String
    Dim X As Variant
   
        strS = ""
    For Each X In LISTBOX.ItemsSelected
        If strS <> "" Then strS = strS & ", "
        strS = strS & """" & LISTBOX.ItemData(X) & """"
    Next
   
    If strS = "" Then
        strS = "SELECT * FROM TABLEX" 'TABLEX should be the source table where the items selected are stored, for example, the table T_CUSTOMERS if the user is selecting different customers on a listbox'
    Else
        strS = "SELECT * FROM TABLEX WHERE FIELX IN(" & strS & ")" 'FIELDX would be the specific field the user is selecting, for example CUSTOMER_NAME'
    End If
   
    MsgBox strS 'Optional, just to show the SQL code for the query where you will filter the selected values and being able to see if it is correct'

'The following code will update the SQL instruction of an existing query called MULTISELECT_QUERY that you must create before executing this code, however it will be updated by the selecion so just pick any table and field on your database

    Dim qdf As QueryDef
    Set db = CurrentDb
    Set qdf = db.QueryDefs("MULTISELECT_QUERY")
    qdf.SQL = strCodigoMasa
    qdf.Close
    Set qdf = Nothing
    Set db = Nothing

Then, I modified my desired query to be filtered by the previously selected items by connecting this MULTISELECT_QUERY to the table to be filtered on my desired query (connection made FIELDX to FIELDX, and added an additional quote on the WHERE instruction being "TABLEX.FIELDX = MULTISELECT_QUERY.FIELDX".

So far, this works for me, however as abovementioned, my knowledge on VBA is very limited and there may be a easier way to reach this same objective... so please do not hesitate to add any feedback!!

Thank you all for your help.

 

[Josef P.]

My problem there is that the items to be selected by the user depend themselves on the result of a previous query, therefore I can not make a unique design of a form with the multi-value controls (as far as I know).

You can define the data source of the combo box as you wish. The multiple selection functionality is only available via the bound multi-value data field.
If the form is already bound to another source, you could integrate the dropdown via a subform.

 

[Pat Hartman]

Create one query using the In() and then create a second query to join to that query to select the rows from the other table. You can also use a sub query for the In() part. The sub query will be simpler if you are building the query in VBA but possibly less efficient since ACE does not optimize sub queries well.