Find or scroll to and Select a Listbox item from a value in a combo box. HELP!

Posted on 2004-11-29
Last Modified: 2012-05-05
I have a listbox with many items. To enable faster searching, I have a combo box based on the same query as the listbox. When a user chooses an item from the combo box, I want the listbox to scroll to and select the item. This is a multi-select list box that acts as the basis for building a recordset for a report - so please don't suggest switching to some other control. The multi-select box is necessary. What shouldn't be necessary is having the users scrolling all day to find the entries they want. I know about the pressing of the first keystroke going to the first matching item as well, but its not enough.

I have tried using listindex to no avail, and have searched all the properties and methods also, but I can find no way to do this. Any help much appreciated - because of urgency of project I'm offering lots of points! Thanks in advance...

Question by:SWisowaty
    LVL 18

    Expert Comment

    suppose you have your listtbox entries numbered by an ID running from 1,2,3 ....
    following will do what you want:

    Private Sub myCbo_KeyDown(KeyCode As Integer, Shift As Integer)
      If KeyCode = 13 Then
        If Me.myList.Selected(Me.myCbo) Then
          Exit Sub
          Me.myList.Selected(Me.myCbo - 1) = True
        End If
      End If
    End Sub

    Regards, franz


    Expert Comment

    the ListIndex properties only work when the ListBox is multi-select = none.
    LVL 18

    Expert Comment

    numeriss points to the weak point.
    you can acchieve, that your users can comfortably select via teh dropdown, but you will need to introduce a field to show them, what they have already collected, this will make sense anyway, if the list is reall long, so eaven if you could scoll to the last selected entry, you would not see all selected.

    have a look at his example: http://www.itsgemeiner/service/EE/listboxmultiselect.mdb

    it shows, how to ensure a continous numbering by a query:

    SELECT myTab.myID, myTab.myText, (SELECT Count(T2.myText)  FROM myTab T2 WHERE myTab.myText > T2.myText) AS mySeq
    FROM myTab
    ORDER BY myTab.myText;

    this query is the source of your list and combo

    i introduced a third controll to show the actual selected names - so the code is following:

    Private Sub myCbo_KeyDown(KeyCode As Integer, Shift As Integer)
      Dim myString
      Dim myCtl As Control
      Dim myVar
      If KeyCode = 13 Then ' take Entry on the Enter Key - so you can make use of the autofillsupport
        If Me.myList.Selected(Me.myCbo.Column(0)) Then  ' if name is already selected leave
          Exit Sub
          Me.myList.Selected(Me.myCbo.Column(2)) = True ' add to the selection
        End If
      End If
      ' this part colects all selected names to a textbox for display
      myString = ""
      Set myCtl = Me.Controls("myList")
      For Each myVar In myCtl.ItemsSelected
        myString = myString & myCtl.Column(1, myVar) & ","
      Me.isSelected = Left(myString, Len(myString) - 1) ' get rid of last camma
    End Sub

    Regards, Franz

    LVL 18

    Expert Comment

    LVL 58

    Accepted Solution

    You are making your life too complicated, Franz :)

    I could not do anything with the "extended" multiple selection setting, but if the list is "simple", then it is, indeed, simple:

    Private Sub cboClients_AfterUpdate()

        With lstClients
            .SetFocus   ' needed to access .ListIndex
            .ListIndex = cboClients   ' show row
            .Selected(cboClients) = True   ' select row
        End With
        cboClients.Value = Null   ' reset for next usage

    End Sub

    (I had a combo cboClients, with Bound Column: 0, and a list box lstClients, with exactly the same RowSource)

    It is important to understand that this works by item number, not by key. If one list is sorted differently, it will still select the 5th item in the list if the 5th item is selected from the combo...
    Also, the "bound column: 0" is just a short-cut to avoid having to refer to cboClients.ListIndex. The effect of "bound column: 0" is in effect to set cboClients.Value = cboClients.ListIndex...

    Good Luck

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now