Multiple selections for output 1

[Longtail2000]

I am trying to use a search form that checks a database for a number of fields that must match to display on the output report. One of the fields has a list box which I want the user to be able to choose 1 or more selections - all of which are tested against the database for a match. I have selected "Simple" under 'Multi Select' and can selectively choose the ones desired but when I comit the search it shows no output although I know there are matches.

Any ideas?

 

[RickSpr]

I presume that somewhere you have a criteria string that includes something like "[fieldname] = Forms![formname]![listboxname]". That won't work. A MultiSelect list box control has Null as its Value property, because it actually has several values, one for each item selected.

You need to construct your criteria string in code, using the ItemsSelected property of the list box control. The ItemsSelected property is a Collection containing one Variant item for each selected row in the list box.

A typical use would be to build a String variable by concatenating the values from the ItemsSelected collection, separated by commas:

    Dim varItem As Variant, strList As String
    For Each varItem In [listbox].ItemsSelected
        strList = strList & ",'" & varItem & "'"
    Next varItem
    strList = Mid$(strList, 2) ' remove initial comma

You would then use the string to build a criteria string, something like the following:

    strCriteria = "[fieldname] IN (" & strList & ")"

Note: It's always possible that the user selects NO items in the list. The code above won't fail in this case, but the criteria string won't return any records, either. You should probably test separately for this situation, using "If [listbox].ItemsSelected.Count = 0 Then...".

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Mecha]

Hi Rick

I am also trying to use listboxes to run a query and generate a report, i can get it to work when selections are made, but when no selections are made i want the query to use all the items in the listbox. I tried just removing the whole If statement, but then the report wont open
As you said above i guess it would go something like

"If [listbox].ItemsSelected.Count = 0 Then...".

but what would go after the "then". How do you say "select all" in VB speak?

Thanks


Carly

 

[RickSpr]

If no items are selected, you should remove the corresponding criteria. Perhaps that's what you meant by "removing the whole If statement", but I'm not sure because you haven't provided any details. There are several ways to modify a report's recordset, and I dont' know which technique you're using.

In any case, acting as if the user had selected all the items isn't the best solution. Since this is really a distinct question, I recommended starting a new thread and including the details of what you're doing. And please read FAQ181-2886.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein