[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2004-11-29
Medium Priority
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
  • 3
LVL 18

Expert Comment

ID: 12697573
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

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

Expert Comment

ID: 12698202
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

ID: 12698222
LVL 58

Accepted Solution

harfang earned 2000 total points
ID: 12699458
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

834 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