multi select combo box??? (A2K)

Topic

New Reply

Is there such an object as a multi select combo box? I currently have a list box with the Multi Select property set to Simple. The size of the form on which this list box lives limits users to seeing only about 3 items before having to scroll to see more. Users want a “drop down box” that can display about 20 items at a time (before having to scroll or page down) and also to be able to make multiple selections. I thought I remember reading something about this at one time but I grew up in the sixties and my memory is not what it ought to be.

Any ideas would be helpful.
Thanks,
aap2

Reply | Quote

Replies

No, I’m afraid not. Besides, it would be very difficult for users to select multiple items in a combo box.

If you have no room on the form to enlarge the list box, you might create a popup form with a larger list box to make selecting items easier, and replace the list box on the original form by a command button that opens the popup form.

Reply | Quote

First of all, thanks for your suggestions.
What is the best way to pass the users selections from this multi-select list box as parameters to the query that is the record source for a report? Below is info on my application.

The form is called frmSelectClients
It contains a listbox called lstSelectClients where users select one or more clients.
The form also has a command button called cmdOpenReportSummary used to open a report called rptClientSummaryReport.

The query (qrySelectReportData) below is the record source for the report
I have tried the following with no luck.

Field: ClientNo, some other fields…
Table: qrySelectByRecoveryPctSvcLine
Total: GroupBy
Sort:
Show: (checked)
Criteria: [forms]![Performance Analysis Client Selection Form]![lstSelectClients] <—WHAT GOES HERE?

I have also tried writing a function called SelectedClients() that returns a string of client numbers "Or"d together like this "12345W" Or "23234C" Or "44322S" and tried using this as the query parameter as follows:
Field: ClientNo
…
Criteria: SelectedClient()
It works well if I select one client from the list but returns no data when multiple selections are made.

The code for the function follows:

Public Function SelectedClients() As String
'create filter for selected records
Dim ctlSource As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim intStringLength As Integer
Dim strAster As String 'I tried to use this to pass a wildcard to the query to select all records
Dim strQuote As String 'I used this instead of enclosing quotation marks inside of quotation marks

Set ctlSource = Forms![Performance Analysis Client Selection Form]!lstSelectClients
'evaluate number of items on list to make the filter query
'processes more than one item
strQuote = Chr(34)
For intCurrentRow = 0 To ctlSource.ListCount – 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & strQuote & " Or "& strQuote
End If
Next intCurrentRow

'remove the last "Or" from the search string
intStringLength = Len(strItems)
If intStringLength = 0 Then
strAster = Chr(42)
strItems = strAster
Else
strItems = Left(strItems, (intStringLength – 4))
End If
'pass value to function
SelectedClients = strItems

'Reset destination control's RowSource property.
Set ctlSource = Nothing

End Function

Or, is it better to use an SQL statement as the report data source and where would I put that SQL Statement? Would it be in the "FilterName" part of the DoCmd.OpenReport line or some other place?
As always, your suggestions are appreciated.

Reply | Quote

Your last statement about putting it (SQL statement) in the OpenReport line is another way to go.

The OpenReport line has a filter and a where clause, I would tend to setup a string and put it in the Where clause of the OpenReport line thus:

“CustomerID in (‘xxxx’,’xxxx’,’xxxx’)”

You will have to substitute CustomerID with the real field name and the xxxx with the customer id’s that you were trying to build up before.

HTH
Pat

Reply | Quote

I am revisiting this issue and have a particular problem. I use the function SelectedClients() described above to create a string of CLIENT IDs. that looks like this.

SelectedClients = “12345W Or 23455C Or 34567T”

I use this statement to open the report.

DoCmd.OpenReport stReportName, acViewPreview, ,[CLIENT]=SelectedClients()

And I always get the following error message.

Microsoft Access can’t find the field ‘I’ referred to in your expression.

I can’t figure out what field is being refered to in the error message. Can you tell from looking at this what the problem might be?

Reply | Quote

You have to rework the SelectedClients function to make it return a string like this :
SelectedClients = “[CLIENT]=’12345W’ Or [CLIENT]=’23455C’ Or [CLIENT]=’34567T’ ”
Don’t forget the quotes around the data.
Then, to open the report use
DoCmd.OpenReport stReportName, acViewPreview, ,SelectedClients()

Reply | Quote

I just want to be sure of something. When I follow your suggestion, should the “Record Source” property for the report be blank or have the query name?

Reply | Quote

Should have the query name.
SelectedClients() is only the “where” part that is add to the recordsource

Reply | Quote

Thanks,
If the user makes no selections from the list box, what should the filter look like to get all items in the list instead of no data?

Reply | Quote

The filter should be an empty string, that is , two double quotes.

Reply | Quote

This might help as well –

Another option on the list box would be to program the size of the listbox to change when it is in focus. Thus, when users are focusing on the listbox, the size of the listbox would increase so your users can see more data. When focus is lost, the size would revert back to the original size. Something like this on the On focus event of the listbox should work:

Me!ListboxName.Height = 1000

Then change the height back to the original height on losing focus.

HTH

Reply | Quote

Along with the other suggestions, you might want to take a look at Helen Feddema’s site. I have used information from her Archons 7-8 to set up a paired multi-select listbox. Her example may do more than you need, but it’s worth a look.

Reply | Quote