ACCESSIBLESOLUTIONS
asked on
Combo box vs list box or subform performance
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Steve
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
AW
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
AW
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
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
> 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.F ilter="[La stName] Like '*" & Me!MyTextBox & "*'"
Me!MySubformControl.Form.F ilterOn=Tr ue
or by changing its record source:
Me!MySubformControl.Form.R ecordSourc e="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.
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.F
Me!MySubformControl.Form.F
or by changing its record source:
Me!MySubformControl.Form.R
If you want to do an exact match, you simply change the Like to = and omit the wildcard * characters.
ASKER
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.
ASKER