0

I have a multi-select listbox which I want to be the source of a sql query with results displayed in a listbox. Currently I have the results going to a recordset which is displayed beautifully when only one 1 field is selected. For instance, if the user selects Gender, no recordset datasheet opens and List20 shows the field name, underscored, with the distinct values below it--Perfect!! When I try to select more than one item things go awry. For instance 2 items selected (Gender, Interface) will result in Gender (underlined) but with 2 F's and 2 M's below it and none of the Interface field values. How do I loop through each selection and have them show in the listbox? Here is my code. Also, weirdly, List13 shows no results when I run the query, but when I take away the line: Set Me.List13.Recordset = rs a recordset datasheet opens up and the desired results no longer appear in List20. Please help!

Private Sub Command19_Click()
Dim strSQL As String
Dim strCriteria As String
Dim varItem As Variant
Dim dbs As Database
Set dbs = CurrentDb()
Dim qdf As QueryDef
Dim rs As Recordset

On Error GoTo Err_Command19_Click

For Each varItem In Me!List101.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List101.ItemData(varItem) & "'"
Next varItem

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "SELECT DISTINCT " & strCriteria & " FROM Scrubbed"
strSQL = Replace(strSQL, "'", "")

Set rs = dbs.OpenRecordset(strSQL)
Do Until rs.EOF
Set Me.List20.Recordset = rs
Set Me.List13.Recordset = rs
Loop

With dbs
Set qdf = .CreateQueryDef("TmpDistinctValues", strSQL)
DoCmd.OpenQuery "TmpDistinctValues"
.QueryDefs.Delete "TmpDistinctValues"
End With
dbs.Close
qdf.Close

Exit_Command19_Click:
   Exit Sub
Err_Command19_Click:
    MsgBox "Please select a field"

End Sub
JenPhyllisB
  • 133
  • 1
  • 7
  • Why are there quotes in this line `strCriteria = strCriteria & ",'" & Me!List101.ItemData(varItem) & "'"` when all you do is remove them later? – Fionnuala Aug 01 '12 at 20:37
  • Honestly, I'm not sure why they need to be there but when I remove the line strSQL = Replace(strSQL, "'", "") the code no longer works. I know it doesn't work anyway but I think I'm halfway there. – JenPhyllisB Aug 01 '12 at 20:42
  • If you put in the quotes, you have to remove them, just don't put them in. – Fionnuala Aug 01 '12 at 20:45
  • Also, comment out the error trap until you have completed the procedure. It will mask problems. – Fionnuala Aug 01 '12 at 20:46

1 Answers1

0

Surely you mean:

For Each varItem In Me!List101.ItemsSelected
   strCriteria = strCriteria & "," & Me!List101.ItemData(varItem) 
   colcount = colcount + 1
   colwidths = colwidths & ";" & "1134"
Next varItem

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "SELECT DISTINCT " & strCriteria & " FROM Scrubbed"
''strSQL = Replace(strSQL, "'", "")

''Set rs = dbs.OpenRecordset(strSQL)
''Do Until rs.EOF
Me.List20.RowSource = strSQL
Me.List20.ColumnCount = colcount
Me.List20.ColumnWidths = Mid(colwidths, 2)

''Set Me.List13.Recordset = rs
''Loop
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • When I select Gender and run this code I get error '91' Object variable or With block variable not set on the line Me.List20.Recordset = strSQL – JenPhyllisB Aug 01 '12 at 20:52
  • I changed that shortly after I copied it, it should be rowsource. You will also wish to set the column widths and column counts. I will add a note. – Fionnuala Aug 01 '12 at 21:00
  • Understood, and thank you for your help. I'm getting there. However when I selected the values Gender and Interface, the results in List20 shows 2 columns with the values for Gender and Interface repeated in 4 rows. There are only 2 distinct values for each. I don't see a "limit to unique values" setting in the listbox. Is there code I can add? – JenPhyllisB Aug 01 '12 at 23:44
  • Select distinct gender and interface does not mean distinct gender (2), then distinct interface (2), it means distinct gender+interface (2x2). You need to post a data sample if this is not what you are getting. – Fionnuala Aug 01 '12 at 23:49
  • Got it. So I need to rework my code then, since the SELECT DISTINCT query will not work in this instance? – JenPhyllisB Aug 01 '12 at 23:51
  • It depends on what you want to end up with. It sounds like you may need more than one combo. – Fionnuala Aug 01 '12 at 23:53
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/14773/discussion-between-jenphyllisb-and-remou) – JenPhyllisB Aug 02 '12 at 00:05