Combo box with large table as source

[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?

 

[Banana]

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?

See if this is helpful.

http://allenbrowne.com/AppFindAsUType.html

 

[John W. Vinson]

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?

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

 

[David W. Fenton]

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.

100? I'd say it's 5,000 too many!

 

[John W. Vinson]

100? I'd say it's 5,000 too many!

Erm? So you wouldn't use a 14-row combo box because 13 is too many?

 

[David H]

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

 

[John Spencer]

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]

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?

He Banana and John W.Vinson,
thanks for understanding my problem en give me a good solution. I´ll test it
out.

 

[David H]

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.

 

[John Spencer]

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

 

[David H]

Soundex? As in phonetic searches?

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

 

[John Spencer]

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

 

[David W. Fenton]

Erm? So you wouldn't use a 14-row combo box because 13 is too
many?

Ack, decimal point error! I should have said 500.