Run SQL query on each item in multiselect listbox and show results in another listbox?

Question

I'm running a "select distinct" query on fields selected in a multiselect listbox (the listbox is populated with fields from a table). The code worked fine before I made it multiselect, and now the SQL query is not functioning. For example when I select the fields "Gender" and "INTERFACE" from the field list, the results in the 2nd list box are:

SELECT DISTINCT Gender INTERFACE

Ideally my results would include the field name and then the values, with a line between each field results.

Here is my code:

Dim strSQL As String
Dim strCriteria As String
Dim varItem As Variant

On Error GoTo Err_Command206_Click

For Each varItem In Me.ScrubbedList.ItemsSelected
strCriteria = strCriteria & ",[" & Me!ScrubbedList.ItemData(varItem) & "]"

Next varItem

strSQL = "SELECT DISTINCT " & Mid(strCriteria, 2) & " FROM Scrubbed"

'====== Testing
Debug.Print strSQL
'=====

Me.List316.RowSource = strSQL

Exit_Command206_Click:
    Exit Sub
Err_Command206_Click:
    MsgBox "Please select a field"

score 0 · Answer 1 · answered Jul 31 '12 at 14:35

0

You seem to have some things in the wrong place:

Dim strSQL As String
Dim strCriteria As String
Dim varItem As Variant

On Error GoTo Err_Command206_Click

For Each varItem In Me.ScrubbedList.ItemsSelected
    strCriteria = strCriteria & ",[" & Me!ScrubbedList.ItemData(varItem) & "]"

Next varItem


strSQL = "SELECT DISTINCT " & Mid(strCriteria,2) & " FROM Scrubbed"

'====== Testing
Debug.Print strSQL
'======

Me.List316.RowSource = strSQL


Exit_Command206_Click:
    Exit Sub
Err_Command206_Click:
    MsgBox "Please select a field"

answered Jul 31 '12 at 14:35

Fionnuala

90,370
7
114
152

Thanks---something's not quite right. With the updated code when I select Gender & INTERFACE. The results in the listbox show as: SELECT DISTINCT[Gender] [INTERFACE]FROM Scrubbed This is with List316 using "value list" as its Row Source Type. When I change row source type to Table/Query, the results show as F M – JenPhyllisB Jul 31 '12 at 14:47
I am afraid that is not what I get. Can you post your criteria line? – Fionnuala Jul 31 '12 at 14:54
strCriteria = strCriteria & ",[" & Me!ScrubbedList.ItemData(varItem) & "]" – JenPhyllisB Jul 31 '12 at 14:57
Nope, I still cannot get it to produce line you show above. Mine has the comma. Can you edit your question to include your new code? – Fionnuala Jul 31 '12 at 15:00
Running your code in a test I get `SELECT DISTINCT [AText],[ADate] FROM Scrubbed` – Fionnuala Jul 31 '12 at 15:16
I don't see any difference between your code and mine, yet yours seems to work? – JenPhyllisB Jul 31 '12 at 15:27
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/14698/discussion-between-remou-and-jenphyllisb) – Fionnuala Jul 31 '12 at 15:29

Run SQL query on each item in multiselect listbox and show results in another listbox?

1 Answers1