Multiple Selection from a list box

You need to build a criteria string.

Dim strWhere$,  i%, SQL_STR$

'Then query the list box.

For i = 0 To lbX.ListCount - 1
If lbX.Selected(i) Then
 if strWhere & "" <> "" then
  strwhere = strwhere & lbX.Column(1, i) & "or "
 else
  strwhere = lbX.Column(1, i) & "or "
 end if
end if
next i
strWhere = left(strWhere,len(strwhere)-3))
SQL_STR = columnnameonreport & "like & & strwhere

reportX.filter = SQL_STR

I have done this dynamically. This is written from top of my head. Please verify the syntax.

a similar possibility is:

Dim varItem As variant
Dim strCriteria as string

For each varItem in lstNames.ItemsSelected
   strCriteria = strCriteria & lstNames.itemdata(varitem) & ","
Next i

strCriteria = "YourField in (" & left$(strCriteria, len(strCriteria)-1) & ")"

docmd.openreport "YourReport", acViewDesign
reports("YourReport").filter = strCriteria
reports("YourReport").filteron = true

Hope that helps . . .

brewdog

I use the following code to build a filter based upon inputs by the user. There are text boxes and combo boxes used as inputs. I then apply the filter to a subform but a report could be used as well. The code evaluates the lenth of the input boxes to determine if  AND is needed and concatinates all the boxes together into one long filter.


If Len(Me.txtNum) > 0 Then
  lsFilter1 = "Investigation_Number Like '" & Me.txtNum & "*'"
End If
 
If Len(Me.txtCauseCode) > 0 Then
    Call Add_To_Filter1
    lsFilter1 = lsFilter1 & "Cause_Code Like '" & Me.txtCauseCode & "*'"
End If
 
If Len(Me.cboDepartment) > 0 Then
    Call Add_To_Filter1
    lsFilter1 = lsFilter1 & "Department Like '" & Me.cboDepartment & "*'"
End If
 
If Len(Me.cboDestruction) > 0 Then
    Call Add_To_Filter1
    lsFilter1 = lsFilter1 & "Destruction Like '" & Me.cboDestruction & "*'"
End If

If Len(lsFilter1) > 0 Then
    Me.SEARCH_INV_SF.Form.Filter = lsFilter1
    Me.SEARCH_INV_SF.Form.FilterOn = True
  Else
    Me.SEARCH_INV_SF.Form.Filter = ""
    Me.SEARCH_INV_SF.Form.FilterOn = True
End If

''' this code could be used for a report in place of the above if statement.

DoCmd.OpenReport "RPT_SEARCH", acPreview, , lsfilter1

'''this code called above.

Private Sub Add_To_Filter1()
   
  If Len(lsFilter1) > 0 Then
    lsFilter1 = lsFilter1 & " AND "
  End If
 
End Sub

I use a button to apply the filter and another button to reset the text and combo boxes to null.

chipcary: evidently you didn't read my comment in our other thread. :o) Please post comments, not answers -- answering locks the questions and takes them out of the normal flow of experts exchanging ideas.

Thanks for your cooperation. :o)

brewdog: I did read your comment however this was posted prior to that. sorry

I wish to thank everyone who has attempted to come to my aid. I do not know enough about access to incorporate these suggestions.  If only you can help me with this code: =IIf([Forms]![Test]![Select Level] Is Null,"
                ","[Level]=Forms![Test]![Select Level]")
It works for retrieving one record if the multiselect property is set to none.  But if I set multiselect to simple or extended it shows syntax error.

What I have on my form are two radio buttons, one for printing whole report and the other linked to the listbox in case a user wants to select more than one report.  I then linked the radio buttons to 2 command buttons, one to preview report and the other to print.  I used macros for all these.   The above expression is placed in the WHERE condition of the preview macro.  If you can help me ammend the above code to do what I want, I'll appreciate it.  Otherwise, you can walk me through another process that can get this done.  Thanks for your efforts.  I am increasing the points by 50 for your effort.

I don't think the macro is going to be a nice way to do this one. I'd suggest putting the code I posted before:

Dim varItem As variant
Dim strCriteria as string

For each varItem in lstNames.ItemsSelected
   strCriteria = strCriteria & lstNames.itemdata(varitem) & ","
Next varItem

strCriteria = "YourField in (" & left$(strCriteria, len(strCriteria)-1) & ")"

docmd.openreport "YourReport", acViewDesign
reports("YourReport").filter = strCriteria
reports("YourReport").filteron = true
docmd.openreport "YourReport", acViewPreview

behind the Preview button and a similar piece, with acViewNormal on the last line instead of acViewPreview, for the print button.

I don't think the macro is going to be a nice way to do this one. I'd suggest putting the code I posted before:

Dim varItem As variant
Dim strCriteria as string

For each varItem in lstNames.ItemsSelected
   strCriteria = strCriteria & lstNames.itemdata(varitem) & ","
Next varItem

strCriteria = "YourField in (" & left$(strCriteria, len(strCriteria)-1) & ")"

docmd.openreport "YourReport", acViewDesign
reports("YourReport").filter = strCriteria
reports("YourReport").filteron = true
docmd.openreport "YourReport", acViewPreview

behind the Preview button and a similar piece, with acViewNormal on the last line instead of acViewPreview, for the print button.

Wait a minute . . . I just reread your last post. Does your list box contain a list of reports or a list of values that users can choose to limit what shows up on one report?

In this case you must reopen this question. The solution by chip is not what you want. Cause he/she talks about the text boxes. You have a multi select listbox.

I have the mdb ready with a form and report and a table to demonstrate what I have stated in my first evr post for this question. I need your emai address.

Ogunbo, I have a slightly different method: what I do is put a field on the form to contain the selections.  When the user makes a selection it adds the selection to the field.  Then I create a query that does an "INLIST(value in the field, which I have set up to load with the proper formatting) against the selection field.  

It's simpler and lets the users see what they have selected.  Of course, you also have to put in a button which allows deselecting of the last value.  Hope this is of use; let us know if you want to continue pursuing this approach or go with one of the others.  Thanks.

devtha: I use both text boxes and combo boxes it works either way in conjunction. it works either way.

I am really grateful for all of you who have continued to offer me help.  I was unable to give an instant reply as I was away at work. To: Brewdog:  I tried the code but there seems to be a syntax error with the line:
strCriteria = "YourField in (" & left$(strCriteria, len(strCriteria)-1) & ")" .

The list box contains fields from a query and what I wanted was for a user to be able to select more than one record to preview or print.  The list boxs multiselect property is set to simple to allow multi selection.

To Devta:
My email address is olutoyin@yahoo.com.  I am interested in seeing the example that you have for me.

Once again, I thank you all.

You could use the selection in the list box to create a table with one entry for each selection in the box.  If the box contained valid IDs from the same recordsource as the table that you are trying to restrict the content of, then you could use that id field in the records of the new table you create.

Then you could make the recordsource of the display item, (form, report):

Select * from <MainTable> Inner Join <NewlyCreatedTbl> ON <MainTable>.[ID] = <NewlyCreatedTbl>.[ID];

(You supply the names for <MainTable> and <NewlyCreatedTbl>.

Then if you ran the code to create the new table prior to opening the form or report, the join in the query would restrict the records as you indicate.

I can be more explicit if you need, and if you provide the names of the objects involved.

Brian

Look out for the email.

The email I received from Devta helped to sole the problem, hence I wish to reopen the question to Devta so that he can get his points.  Many thanks to all of you who have offered help on this problem.

glad it's solved for you. Nice work, devtha.