Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multi select combo box??? (A2K)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: multi select combo box??? (A2K)

    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.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: multi select combo box??? (A2K)

    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
    Regards,

    Gary
    (It's been a while!)

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi select combo box??? (A2K)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi select combo box??? (A2K)

    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.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: multi select combo box??? (A2K)

    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

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi select combo box??? (A2K)

    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?

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi select combo box??? (A2K)

    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()
    Francois

  9. #9
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi select combo box??? (A2K)

    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?

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi select combo box??? (A2K)

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

  11. #11
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi select combo box??? (A2K)

    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?

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: multi select combo box??? (A2K)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •