Combo box populate all values

[RobTsintas]

I frequently use a combo box to select a person on a form.

This allows auto-complete of the person ID, but also allows the person to be selected by name (the people are presented by ascending surname in the dropdown).

The problem is that the values only fill in the dropdown while it is being scrolled, so to get to a "Smith", for example, you can't just grab the scroll bar and drag it down until you get to the "S" section, as the list is only initially filled up to "E" (say). Scrolling down that far then fills the next 20-or-so.

So I frequently have users sitting there holding the mouse button down on the scroll bar down arrow for a long, long time until they get to the patient they want!

Once the list is open, a simple Ctrl-Down, to jump to the last option, will instantly fill the list. But I can't expect my users to know about this.

So (finally to the question!), is there any way to force the whole list to be populated in the dropdown right from the start?

Cheers

 

[dhookom]

You might try add a text box with the combo listcount as a control source like:

=[cboPatient].[ListCount]

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

The way you describe this does not sound like normal Access behavior, it sounds as if there is additional code to make it perform as it currently does. A combo box should load all records into the row source. I do not really understand this

The problem is that the values only fill in the dropdown while it is being scrolled, so to get to a "Smith", for example, you can't just grab the scroll bar and drag it down until you get to the "S" section, as the list is only initially filled up to "E" (say). Scrolling down that far then fills the next 20-or-so.

Maybe if this is a calculated field it takes time to render. Actually I doubt you have something like this coded, because the code to return a different set of records based on the scroll bar location would be very complicated and require the use of windows APIs.

If the list is that big you may want to add some features to it.
1) Make the "List Rows" large so you see a bunch of records at once. Try 255 and see if that gives you what you want.
2) you could set it up as a "find as you type". So if you type S it filters the list to s*, if you type "sm" it filters to just sm*
3) Do cascading combos. First combo or other controls allows you to select a letter or letter range to narrow the second combo

 

[MajP]

Here is a pretty nice and simple feature. Use a tab control to filter your list. Shrink it down and put it behind the combo except for the tabs.

Here is some code, but you could avoid writing the sql string by simply making a few queries. If you click on the first tab then just load the appropriate query as the row source.

Private Sub cmboProduct_Enter()
  FilterByTab
End Sub

Public Sub FilterByTab()
  Dim strSql As String
  Dim strWhere As String
  Dim lastLetter As String
  Dim firstLetter As String
  Dim strCaption As String
  
  strCaption = Me.tabFilter.Pages(tabFilter.Value).Caption
  strSql = "SELECT Products.ProductID, Products.ProductName FROM Products"
  firstLetter = Left(strCaption, 1)
  lastLetter = Right(strCaption, 1)
  strWhere = " WHERE left([ProductName],1) between '" & firstLetter & "' AND '" & lastLetter & "'"
  strSql = strSql & strWhere & " ORDER BY Products.ProductName"
  Me.cmboProduct.RowSource = strSql
End Sub

 

[RobTsintas]

Duane, your solution is simple, elegant and works perfectly - thanks!

MajP: Thanks for your responses too!