Combo Box- Large Table Lookup

[TURNERMICH]

I have a table with a large number of records 100,000.
I need a combo box, to lookup and display a record, using say "Description" as the field.
I have the field (description) indexed>
I have created a combo box, and altered the query to sort on description.
When looking up the combo box it seems to work ok, if the character entered is say "D", BUT when say "S" is entered,
it stalls the machine and takes ages to light up, again., and the lookup does not work correctly.
*****There seems to be a limit (practical), for a record source in a combo box.??!!!***

---------------------------
??What is the best way to handle looking up in the above case. Is there a way to overcome the problem.
I do also have lookup fields that are numeric., which makes a preliminary combo a bit difficult.

---------------------------


Would appreciate any advice.

 

[ide]

I dont't know, is there a limit or not, but if its true this case i should do the following:

-insert before the combo an option group, with 2-5 option:
- A-H
- I-P
- Q-Z
-into the option group change event a case

...
On Error Resume Next
select case Option1
Case A-H
me.comBo1.rowsource = "SELECT * FROM table1 WHERE ...;"
Case A-H
me.comBo1.rowsource = "SELECT * FROM table1 WHERE ...;"
Case A-H
me.comBo1.rowsource = "SELECT * FROM table1 WHERE ...;"
end select
...

sorry, if it doesn't help you
ide

 

[TURNERMICH]

ide..Thanks for your comments. This approach will work ok
on character fields, but will be a problem on numeric fields.

Would appreciate any comments from anyone else who has had the problem, with very large combo box's.

 

[JohnHarkins]

I have a similar situation: I'm looking for a "clever" design to support recording membership in a political auxililary and contribution to the organization.

The "base" table consists of voter records (more than 35,000), auxiliaries (2 or 3) with approximately 150 members in each, and the need to record contributions of time and dollars.

I've thought of using a technique that populates a combo box with all the names of members of a given auxiliary (I record contributions for only one auxiliary at a time) so that I can choose one member and then record on a form the date and amount (hours or dollars) of his/her contribution. If, however, the name does not appear in the combo box, I'd like a technique to let the user choose from among the 35,000 voters (auto-complete?). The implication is that that voter would then have not only his/her hours or dollars recorded but also be designated automatically as a member of the particular auxiliary to which the contribution is being made. If the entire voter list doesn't contain the name of the donor of time or dollars, I need to add a new "voter."

There's probably a cleaner way to handle data input than what I've proposed, so please help me with your imagination and/or experience! John Harkins