limit to combo box rows?

  • Thread starter Thread starter curt
  • Start date Start date
C

curt

My form is a simple presentation of "leads" (e.g.,
prospects, contacts, etc.) from a table. The table has
about 120k rows. Each rows primary index is an
autonumber field. In many cases, we don't know the "id"
number to find a specific record. We can alway's use
the "find" tool to find a record. But since we almost
always know the lead's name, the unbound combo box (which
is just a query on the table containing the lead id and
lead name, displaying the lead name, but the field value
is the id) is used as a "Find: " prompt that lets us
start typing in the name, select the row from the drop
down list, and then the "after update" event for the
combo box causes the form to fill with the info for the
selected record. Works great, except the combo box only
shows up through the middle of the m's. If I want
to "find" a lead with the name "samurai sam's", I can
type in the full name ok and go to the record, but if I
type in an "s" and then use the combo box to select from
the list, it only shows the last records its populated
with which stop in the m's (the last record included in
the list is the 65kth record or so). Any help?
 
(the last record included in
the list is the 65kth record or so).
Click to expand...

Your subject line correctly diagnoses the problem: a combo box is
limited to 65536 rows.

There are some tricks to get around this - for instance you could have
a second control to limit the records retrieved in the combo box (e.g.
another combo box with just the letters A to Z); in its AfterUpdate
event you could set the Rowsource query of the customer combo to only
names beginning with that letter.
 
Hi Curt,
Not sure, but it sounds like a limit on the number of
items permitted in the box (65K is a "magic" sounding
number). You might try limiting the number of names
selected in the query (say A-J), and then have separate
queries and boxes for the other names (say K-R, and S-Z).
If this is workable, think ahead a bit - maybe dividing
the list into fourths would be better if the basic table
is growing. Also, are there ANY names that start with
other than a letter (weird, but possible); you'd need to
think about how to specify those.
A slightly more elegant solution would be to prompt the
user for the first letter of the name wanted when the
query runs to gen the list. A little busy on-screen, but
you could limit the list that way.

Just ideas. Hope they help.
 
Back
Top