sql langage with multiselect listbox

[julienmy5757]

Hello,

I have a table and a multiselect listbox.
The table is on the beginning of the event on click of a cmd button.
The table is like :

strMain = "SELECT DISTINCT [A], [b], [C], [D], [E], [F] FROM TblR"
Set rstMain = db.OpenRecordset(strMain, dbOpenDynaset)

After I have a code.
I would like to put a criteria like C=CLIST when you select multiple items in the list, to use it in my code
I know that I have to use a loop and .Itemsselected but I don't know how to write it
I am working on Acess 2010

Thank you very much

[ADezii]

The Logic is to iterate the List of Items Selected, if any, while at the same time building a Criteria String. I created some Code for you that uses the Sample Northwind Database. The List Box contains all unique Regions for Employees, while the generated SQL will represent only those Region(s) selected from the List Box by the User. This is only one of many approaches - any questions, feel free to ask.

Dim lst As ListBox
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
 
Set lst = Me![lstRegions]
 
'If no Items were selected, then get out
If lst.ItemsSelected.Count = 0 Then Exit Sub
 
'Iterate each Item Selected & build Criteria String
For Each varItem In lst.ItemsSelected
  strCriteria = strCriteria & "'" & lst.ItemData(varItem) & "',"
Next
 
'Strip Last "'"
strCriteria = "[Region] IN (" & Left$(strCriteria, Len(strCriteria) - 1) & ")"
strSQL = "SELECT * FROM Employees WHERE " & strCriteria
 
'Test Logic
Debug.Print strSQL

OUTPUT:

SELECT * FROM Employees WHERE [Region] IN ('AR','NJ','WA')