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

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...

SWisowatyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bonjour-autCommented:
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
    Else
      Me.myList.Selected(Me.myCbo - 1) = True
    End If
  End If
End Sub

Regards, franz

0
numerissCommented:
the ListIndex properties only work when the ListBox is multi-select = none.
0
bonjour-autCommented:
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
    Else
      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) & ","
  Next
  Me.isSelected = Left(myString, Len(myString) - 1) ' get rid of last camma
End Sub

Regards, Franz


0
bonjour-autCommented:
0
harfangCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.