HELP! using list boxes with multiselect

[tarhim47]

Here is my file.

[weekend00]

There is you file?

[tarhim47]

Hey,

Sorry I posted the file onto a 2nd thread with my question on another. Didnt know how to update the original thread.

In any case, I'm trying to create a form which includes a list box which allows the user to select multiple counties and have the form rertun information for all clients residing in the selected counties.

The idea is to have the list box include all possible counties using a value list option.

It works fine if i make it a text box and enter one county but i want to be able to select multiple counties.

Any ideas?

P.S. the data is all fake!

[weekend00]

you must can find some good example from google for these cases.

[tarhim47]

I have found a bunch of examples which helped me to create the form and then link it to the report but i cant seem to find any examples where multiselect list boxes are used.

[pbaldy]

i cant seem to find any examples where multiselect list boxes are used.

That seems like a funny statement given that I gave you an example here:

https://www.accessforums.net/forms/u...uery-9103.html

[tarhim47]

I tried this...but what this does is provide all the counties in the table in the mutiselect list.

For example:

If my table has the following information
1. client a - oxford
2. client b - norwich
3. client c - oxford
4. client d - elgin
5. client e - peel

Then the multiselect list returns the following options
1. oxford
2. norwich
3. oxford
4. elgin
5. peel

Notice how the multiselect list is just a copy of the counties from the table above. This doesnt really help.

What i want is for the multiselect list to return the following choices:
- oxford
- norwich
- elgin
- peel

This way, if I choose oxford, the form will return two records instead of me having to go through and find all the oxfords and select them.

When I'm done, im going to have about 100 records. If 50 of them are in oxford then it will make it diffficult to the use your example.

Any other ideas?

[pbaldy]

Typically I would expect a "Counties" table that contained a list of available counties. If you want to generate the list from your data, rather than the table base the listbox on a query:

SELECT Sample_data.County
FROM Sample_data
GROUP BY Sample_data.County
ORDER BY Sample_data.County;

That will provide a list of unique counties.

[tarhim47]

Hey Paul,

The multiselect list worked...it now only shows all the different counties in alphabetical order.

However it doesnt return any data.

Can you please try this for me?

Use the following info:
- herd size - low: 1, high: 1000
- age - low: 1, high: 1000
- county: oxford and elgin

It should return a few records in the report.

What am I missing?

Thanks for your patience!

[tarhim47]

Any thoughts?

[pbaldy]

Am I allowed a lunch break?

Did you add code to filter the report using the listbox as demonstrated in my sample? You have to use code, the criteria in the query won't work.

[tarhim47]

Hey Paul, hope you had a good week-end.

And to answer your earlier question, NO (!) you are not allowed a lunch break....hahahaha...lmao! Jus kd, its just that I take a later lunch break which is why I didnt realize the time.

In any case, I tried your code idea and inserted the code into the form and adjusted it to my database. However, it still doesnt return any data.

Here is the code as I've inserted it:



Option Compare Database
Option Explicit

Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.Enter_county_of_farm_operations.ItemsSelected.C ount = 0 Then
MsgBox "Must select at least 1 county"
Exit Sub
End If
'add selected values to string
Set ctl = Me.Enter_county_of_farm_operations
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.OpenReport "Report", acPreview, , "ClientID IN(" & strWhere & ")"
Exit_OK_Click:
Exit Sub
Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click
End Sub





At this point, I am pretty much satisfied with my database. The only thing I need to finish is to be able to allow the user to select multiple counties and have the report return the data for just those counties.

[pbaldy]

Did you take the county criteria out of the query? This doesn't look correct:

DoCmd.OpenReport "Report", acPreview, , "ClientID IN(" & strWhere & ")"

[tarhim47]

I did...there is no criteria in the Query for County.

As for the following line of code, I'm not exactly sure how to fix it. The ClientID is the primary key in the table.

DoCmd.OpenReport "Report", acPreview, , "ClientID IN(" & strWhere & ")"

Can you help me fix it?

Also the error I'm getting when I click OK is as follows:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

[pbaldy]

But you want this restriction on County, so that should be the field specified.