Querying multiple List box results

[melaniews]

I have table1 with 2 fields. Interest_code and CustomerID.
I want to use Interest_code as criteria in a parameter query. I want to be able to make several selections without separate parameter boxes.

I created table2 with 2 fields. Interest_Code and Interest_description. It is a list defining the possible Interests.

Interest_code in Table2 joins with Interest_code in Table1

I created a form called "criteria" with a list box that lists the contents of table2 and a command button to run the query. I want to be able to make multiple selections from the list and use them in the query. Like a parameter query without having separate boxes pop up.

I created a query with criteria that references forms!criteria!list16.

I've seen in other posts that I cannot set the multi-select property on a list box to anything other than none and be able to use it in query criteria as it will always be null. And this is exactly my problem.
However, I cannot seem to get what it is that I need to do next to make this work.

Thanks in advance,
Melanie

 

[melaniews]

I've gotten this far and I'm getting a 'type mismatch' error:


Private Sub cmdCriteria2_Click()
On Error GoTo Err_cmdCriteria2_Click

'place this code in the clicked event of your command
'button which start running the report, e.g., "Run Report"
' - presume the listbox is called lstcompany
' - the company is called fldCompany
' the report recordsource is the query
Dim strFilter As String
Dim varItem As Variant
For Each varItem In Me!List16.ItemsSelected
strFilter = strFilter & "[INTEREST_ID] = '" & _
Me![List16].ItemData(varItem) & "' OR "
Next

'
' the next bit of code will subtract out the last "OR"
If strFilter <> &quot;&quot; Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
'
' now, run the report using strFilter to pass a string
' containing the needed companies
DoCmd.OpenQuery &quot;Criteria&quot;, acNormal, strFilter

Exit_cmdCriteria2_Click:
Exit Sub

Err_cmdCriteria2_Click:
MsgBox Err.Description
Resume Exit_cmdCriteria2_Click

End Sub


I tried taking out the single quotes here---

'&quot; & _
Me![List16].ItemData(varItem) & &quot;'

and that did not work either. Same error.

TIA
Melanie

 

[melaniews]

To clarify a possible question, I reported the field name incorrectly in the first post. It is Interest_ID not Interest_code

 

[bhoran]

I think the issue is the statement opening the query.

DoCmd.OpenQuery &quot;Criteria&quot;, acNormal, strFilter

This might work if it used the OpenReport function instead.

try
DoCmd.OpenReport &quot;rptCriteria&quot;, acNormal, , strFilter