Solved

Combo box vs list box or subform performance

Posted on 2004-10-20
8
231 Views
Last Modified: 2012-05-05
We currently have a combo box on our client form displaying client names but the list for some of our customers is quite large (approaching 10,000 records) with performance in control loading seriously degrading. We need to find either a better way to display the names to initiate a search or a faster way to load the control. I've been told, though admittedly don't understand, to use a subform or list box instead of a combo. Is this true?? and why would it be faster? If anyone can offer suggestions on an approach to looking up names with this many records it will be greatly appreciated.
0
Comment
Question by:ACCESSIBLESOLUTIONS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 250 total points
ID: 12357445
yes, 10,000 entries in a single combo box will take a long time to load, and whether it is a combobox or a Listbox makes no difference whatsoever.  Can you break the 10,000 entries into logicval groups, and then load the group names, and only after selecting a group, load just the items for that group?

For example, let's say that the original list was cities in the United States.  These could be grouped by State, so that you would first choose a State, and then a second combo box would load just the cities in THAT state.  This would be MUCH faster, than trying to load all 10,000 cities at the outset.

AW
0
 

Author Comment

by:ACCESSIBLESOLUTIONS
ID: 12357542
We thought about that but in our case all the clients are in the same community. We even grouped by the initial of the last name ie all names that begin with A, as an example, but everyone complained it was to restrictive! The only appeased solution here I do believe is some extraordinary look ahead code that will simple put load the control faster or a completely different search technique. What search techniques do companies like Visa and Master card or Sears use?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12358945
You could make a free text search that pulls back a list of names that match the soundex value of the name typed in the search form and display thise names in a list box for then to choose from if there is more than 1 result, then you the seelction from the listbox for the final query to pull back only the necessary info for your main form.

Steve
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12358958
I believe that they create 'logical groupings'.  When you say the same community, are you speaking about a communiity like a town?  Can you group then by 'neighborhood', or section of town ( for instance, in some towns, the town is sub-divided into NW, NE, SW and SE).  Anything that you can do to cut down on the number of entries that must be loaded into the ComboBox at any one time.  If not, then users are just going to have to be willing to deal with the lag time that is required to load and search through that many entries.

AW
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12358983
It is rarely a good design to load more than perhaps 1,000 entries into a ListBox or ComboBox control.  And there is almost always a way to 're-group' the Large block into smaller sub-blocks.

AW
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12359052
I would like the opportunity to try my typing skills again ...

You could make a free text search that pulls back a list of names that match the soundex value of the name typed in the search form. This list of names would be displayed in a list box for the users to choose from. After a selection has been made on the listbox you can use that value in a query to pull back only the necessary info for your main form.

Steve
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12365069
>  to use a subform or list box instead of a combo.

As AW says, a list or combo makes no difference. Their relative performance is identical and degrades after exceeding around 2000 records. A subform might do what you need, but is almost identical to Steve's suggestion. Basically, you'd have a text box on your form. Your subform displays the appropriate fields from the table where your combo original grabbed its records from. You now filter the subform in the AfterUpdate event of the textbox along the lines of:
Me!MySubformControl.Form.Filter="[LastName] Like '*" & Me!MyTextBox & "*'"
Me!MySubformControl.Form.FilterOn=True

or by changing its record source:
Me!MySubformControl.Form.RecordSource="SELECT * FROM MyTable WHERE [LastName] Like '*" & Me!MyTextBox & "*'"

If you want to do an exact match, you simply change the Like to = and omit the wildcard * characters.
0
 

Author Comment

by:ACCESSIBLESOLUTIONS
ID: 12368089
Thanks everyone for your contribution. I guess I was looking for magic that wasn't forthcoming since all the suggestions were the same (divide and conquer). To that end, I'll award the points to the first individual that responded.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

623 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