Getting values from multiselect listbox

[dkwong]

I have a form that gathers values to be specified in the Filter portion of the OpenReport method. Basically, the form passes values to the underlying query of a report. I have a multiselect listbox where the user can select as many sales code ids as desired. I want to be able to use the list of ids in the filter argument of OpenReport e.g. Sales_Code_ID = 3 AND Sales_Code_ID = 23 AND... and so on for as many as the user selected. How do I do this?

 

[hermanlaksko]

Not sure if this will help you:
Sub AllSelectedData()
Dim frm As Form, ctl As Control
Dim varItm As Variant, intI As Integer

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
For intI = 0 To ctl.ColumnCount - 1
Debug.Print ctl.Column(intI, varItm)
Next intI
Debug.Print
Next varItm
End Sub
Herman

 

[jebry]

Hi!

In the open report button (which I hope you have) use the following code:

Dim varRow As Variant
Dim strCriteria As String

strCriteria = ""
For Each varRow In Me!YourListBox.ItemsSelected
strCriteria = strCriteria & "Sales_Code_ID = " & _
Me!YourListBox.Column(0, varRow) & " Or "
Next varRow

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

DoCmd.OpenReport "YourReport", , , strCriteria

hth
Jeff Bridgham
bridgham@purdue.edu

 

[dkwong]

Thanks, Jeff and for your input as well, Herman. The Left function is nifty although I think it needs to be " Or Sales_Code_ID = ".