Listbox will work when multiselect=simple

[ro88y09]

Listbox will work when multiselect=none, but it wont work when multi-select = simple or extended. After I have a listbox setup, I can select multiple values without any issue. As soon as I open up my query, it returns without any records. I can also clear the criteria, [Forms]![aaDashboard]![Location2], in the Bay column of my query and it will return all records as it should.



I believe I have everything setup correctly:

Form: aaDashboard
Row Source: Values
Listbox Name: Location2

Query: aaDailyProductionComparison
Bay: [Forms]![aaDashboard]![Location2]

This is what I think could be the issue:
-There is a weird value setting, in my query or form, that is messing me up.
-I need to open up my form in such a way that it explains "Location2" on the form is linked to "Bay" in the query.

Has anyone else come across this before? Any ideas?

Thanks in advance

[pbaldy]

You can't simply refer to the listbox when it's multiselect, you need code to read the selected values. One option if you're using the query for a form or report:

http://www.baldyweb.com/multiselect.htm

[ro88y09]

Thanks for the response pbaldy. Much appreciated. I have put the following in my code. The only issue I face now is how to open the query with a "where" statement. See my code below:

Private Sub cmdOpenQuery_Click()
Dim strWhere As String
 Dim ctl As Control
 Dim varItem As Variant
'make sure a selection has been made
 If Me.Location2.ItemsSelected.Count = 0 Then
   MsgBox "Must select at least 1 machine location"
   Exit Sub
 End If
'add selected values to string
 Set ctl = Me.Location2
 For Each varItem In ctl.ItemsSelected
   strWhere = strWhere & ctl.ItemData(varItem) & ","
   'Use this line if your value is text
   'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
 Next varItem
'trim trailing comma
 strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
 DoCmd.OpenQuery "aaDailyProductionComparison", acViewNormal, "Bay IN(" & strWhere & ")"
 
Exit_cmdOpenQuery_Click:
  Exit Sub
Err_cmdOpenQuery_Click:
  MsgBox Err.Description
  Resume Exit_cmdOpenQuery_Click
  
End Sub

I believe my command to open the query is wrong. Everytime I try to run this code, I get the error, "Run-time error '13':" If I simply command the query to open, it obviously opens the query without any results.

[pbaldy]

That technique is designed to work with OpenForm or OpenReport (OpenQuery does not have the same argument). You would take the criteria out of the query, and use OpenForm or OpenReport with strWhere.

Also, most of us never expose queries or tables to users, just forms and reports.