By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,658 Members | 1,612 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,658 IT Pros & Developers. It's quick & easy.

Using List box for multi select

P: 29
I created a list box that will let me select more than one, but the problem is that when I run the report (a command button that I created and works) the results are not what I want. Instead of showing just the particular items that I selected it shows everything.
Here is the code that I have for my list box.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCreateReport_Click()
  2. On Error GoTo Err_cmdCreateReport_Click
  3.  
  4.     Dim strCriteria As String
  5.     Dim bolCriteria As Boolean
  6.  
  7.     bolCriteria = False
  8.     strSQL = ""
  9.     strCriteria = ""
  10.  
  11.     BeginSQL
  12.  
  13.  
  14.     strCriteria = "WHERE ("
  15.     If Not IsNull(Me.cboMfg.Value) Then
  16.         strCriteria = strCriteria & "(([new].[MANUFACTURER]) = '" & cboMfg.Value & "') "
  17.         bolCriteria = True
  18.     End If
  19.  
  20.     If Not IsNull(Me.ListApp.Value) Then
  21.             If Not bolCriteria Then
  22.             strCriteria = strCriteria & "(([new].[APPLICATION]) = '" & ListApp.Value & "') "
  23.             Else
  24.             strCriteria = strCriteria & "AND (([new].[APPLICATION]) = '" & ListApp.Value & "') "
  25.         End If
  26.         bolCriteria = True
  27.     End If
  28.  
  29.     If bolCriteria Then
  30.     'if criteria was entered add it to the SQL string
  31.         strSQL = strSQL & strCriteria & ")"
  32.     End If
  33.  
  34.     'close SQL and create query
  35.     EndSQL
  36.  
  37.     ShowReport 'Me.cboSortBy1.Value, Me.cboSortBy2.Value
  38.  
  39. Exit_cmdCreateReport_Click:
  40.     Exit Sub
  41.  
  42. Err_cmdCreateReport_Click:
  43.     MsgBox Err.Description
  44.     Resume Exit_cmdCreateReport_Click
  45.  
  46. End Sub
May 15 '09 #1
Share this Question
Share on Google+
2 Replies


P: 36
Hi

I will just try to point you in the right direction ;)

1) Which rows have been selected
Expand|Select|Wrap|Line Numbers
  1. For i = 0 To Me.lstBox.ListCount
  2.  
  3.     If Me.lstBox.Selected(i) = -1 Then 'selected
2) You need to add to SQL and I recommend using IN
Expand|Select|Wrap|Line Numbers
  1.     field IN (listboxvalue1, listboxvalue2, listboxvalue3,)
  2.  
  3.     End if
  4. Next
Hope it helps

Regards
Emil
May 15 '09 #2

Denburt
Expert 100+
P: 1,356
Yes you do have to collect each item that you have selected by using a loop or something along those lines as the IT couple mentioned although I would only iterate through the items selected themselves..

Example:
Expand|Select|Wrap|Line Numbers
  1. Dim ctl as control
  2. Dim varItm as variant
  3. Set ctl = Me!myListBox
  4.         For Each varItm In ctl.ItemsSelected
  5.                 If ctl.ItemData(varItm) = "(All)" Then
  6.                     BoolAll = True
  7.                 Else
  8.                     debug.print ctl.ItemData(varItm)
  9.                 End If
  10.         Next
  11.  
May 15 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.