Combo box with large table as source

  • Thread starter Thread starter geocalp
  • Start date Start date
G

geocalp

I want to select a name in a combo box. The source is a large table > 500000
records. I can see only a part (62500) of the names. How can I see them all
in the list? Or is there another system to see them all?
 
I want to select a name in a combo box. The source is a large table > 500000
records. I can see only a part (62500) of the names. How can I see them all
in the list? Or is there another system to see them all?
Click to expand...

A combo is limited to 65536 entries - which is at least a hundred times too
many for a user to scroll or even autocomplete through.

See Allen Browne's excellent example of how to deal with this problem at
http://allenbrowne.com/ser-32.html
 
A combo is limited to 65536 entries - which is at least a hundred
times too many for a user to scroll or even autocomplete through.
Click to expand...

100? I'd say it's 5,000 too many!
 
I wouldn't go with a combo box at all and would use a Repeater nested in a
scrolling DIV with custom paging. Oh wait this isn't ASP.NET
 
Actually, I have people that use a worklist combobox with thousands of rows
(10 to 12 thousand names plus case number- unique values).

The combobox is populated when they open the database or add records to their
worklist. The combobox is on the main form, which is always open. The
combobox allows them to find a particular person/case and the after update
event of the combobox loads the case and associated records.

It is extremely fast and in constant use. It is not used for data entry, but
it is used for data selection. If they ever get to the point of needing more
than 65,000 cases listed, I will have to change the design, but it is highly
unlikely that there will ever be that many active cases.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
geocalp said:
I want to select a name in a combo box. The source is a large table > 500000
records. I can see only a part (62500) of the names. How can I see them all
in the list? Or is there another system to see them all?
Click to expand...

He Banana and John W.Vinson,
thanks for understanding my problem en give me a good solution. I´ll test it
out.
 
Do the users know case numbers off the top of their heads? I came up with a
way where you could select a value in the list by entering a related value
that doesn't exist in the list.
 
They usually know the name and that in all but rare instances gets them to the
proper record. They also have an advanced search dialog that allows them to
search by Case, Name, Phone, SSN, MA Number, and associated contact
information. As well as SOUNDEX coding on LastName

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Soundex? As in phonetic searches?

John Spencer said:
They usually know the name and that in all but rare instances gets them to the
proper record. They also have an advanced search dialog that allows them to
search by Case, Name, Phone, SSN, MA Number, and associated contact
information. As well as SOUNDEX coding on LastName

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
 
Yes, it kind of phonetic. This search is slow unless you pre-populate a field
with the soundex equivalent of the name (a bit un-normalized since the soundex
value is dependent on the last name value). In SQL Server 2005 you can
actually have a "pre-calculated" field that does that.

In Access, you have to handle it through the date-entry form(s). The table is
small enough (30K records) that I just use a vba function to generate the
soundex coding against the fields in the table and the search value.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top