Query a form on a Multi Select List box (1 Viewer)

[Blancorn]

Hi All,

I would like to ask you for help. I went through all the threads on this forum but none of the solutions found works for me (or I am not skilled enough to apply them in my database).

I want to filter the field O_Cliente with values from multiselection in temp_O_Cliente list box. Can you guide me how to do it?

Please find my database attached for refference.

Regards,
Tomek

 

[strive4peace]

hi @Blancorn

one way to do it is on the AfterUpdate event of the temp_O_Cliente listbox

Private Sub temp_O_Cliente_AfterUpdate()
'Me.Refresh
'220508 strive4peace
   'build string from selections in Multi-Select Listbox and filter RowSource of combo

   Dim vIndex As Variant _
      ,vAllSelections As Variant _
      ,sSQL As String _
      ,bClearValue As Boolean
    
   vAllSelections = Null
   bClearValue = False
 
   'loop listbox ItemsSelected and construct list of choices for criteria
   With Me.temp_O_Cliente
      For Each vIndex In .ItemsSelected
         vAllSelections = (vAllSelections +  ", ") _
            &  """" & .ItemData(vIndex) &  """"
      Next vIndex
   End With
 
   'SQL for the combo box in the subform without criteria
   sSQL =  "SELECT [tb_cliente].[id_cliente]" _
      &  ", [tb_cliente].[Cliente] " _
      &  " FROM tb_cliente " _
      &  " ORDER BY [Cliente];"
 
   'use the subform combo box
   With Me.Control_Panel.Form.O_Cliente
      'if it should be filtered, change the SQL statement
      If Not IsNull(vAllSelections) Then
         'add WHERE clause before ORDER BY
         sSQL = Replace(sSQL _
            , "ORDER BY" _
            , " WHERE [Cliente] IN (" _
            & vAllSelections &  ") ORDER BY")
         bClearValue = True
      End If
      'if the RowSource has changed, set it to the new SQL statement
      If .RowSource <> sSQL Then
         .RowSource = sSQL
         .Requery
         'if the value should be cleared, then clear it
         If bClearValue = True Then
            .Value = Null
         End If
      End If
   End With
End Sub

HOWEVER, what if there is already a value in the O_Cliente control on the subform and what if now that value doesn't show because it's not in the list? Therefore, if the combo RowSource is getting criteria, any value is cleared. This is not ideal, but will work.

I couldn't test this because your database has errors ... I assume those need to be fixed before you can test it too!

 

[moke123]

In each module you need to have

Option Compare Database
Option Explicit

Although I dont understand what your data represents, I think you need to fix it. It is unusual to have 99 fields in one table. You should probably break some of it out into its own tables. Tables should be tall and thin.

You also have lookup fields in your tables. Google "The Evils of LookUp Fields" for details. You should avoid using them as they mask what your true data is. O_Cliente is one of the lookup fields.

Attachment fields are also a bad idea as they bloat the database. Store files in a folder and save the path in your database.

You should also have primary keys in your tables.

When I use a multi-select listbox to filter data I usually use the PKey in an In Clause.
For instance, "Select * from tblClients where ClientID in (" & strIDs & ")" strID would be like "1,3,5,33,47" which would be the pkeys taken from the listbox.