Combo Box List count limit

Guest · May 3, 2004

Hi

I have a single view form. This form has its Record Source property set to the 'Customers' table containing about 75,000 records. On this form I created an unbound Combo Box. I displayed the First_Name, Middle_Name and Last_Name of customers in this box. The data in combo box is being picked from the same 'Customers' table that contains the details to be shown on the form. I want to display the details of the customer selected by the user in the unbound combo box.

I created the combo box using the wizard. I selected the option: 'Find a record on my form based on the value I selected in my combo box'.

My problem: The combo box doesnâ€™t display all the names from the table. But if I type a name that is present in the table, and hit the 'Enter' key on keyboard, it displays the corresponding data on the form.

My queries
1. Is there a limit on number of rows that can be displayed in the combo box? If yes, then how to overcome this barrier to show all the names.
2. Is there a way to increase the lookup speed in the combo box?

Thanks a lot for your help

Shobh

Van T. Dinh · May 4, 2004

IIRC, there is a limit of 64K rows in the RowSource of the ComboBox.
However, 64K rows is a lot of data to pull thourgh the network. Generally,
ComboBoxes are designed to work with much less number of rows.

Use a set of *cascaded* ComboBoxes. For example, the first ComboBox select
the first letter of theLast_Name and the second ComboBox, whose RowSource
depends on the selection in the first ComboBox, shows only Last_Names
starting with the selected letter in the first ComboBox.

For coding, see The Access Web:

http://www.mpvs.org/access/forms/frm0028.htm

For a very nice solution using the LabelControl, see Stephen Lebans:

http://www.lebans.com/selectalpha.htm

--
HTH
Van T. Dinh
MVP (Access)

Shobha said:
Hi !

I have a single view form. This form has its Record Source property set to

the 'Customers' table containing about 75,000 records. On this form I
created an unbound Combo Box. I displayed the First_Name, Middle_Name and
Last_Name of customers in this box. The data in combo box is being picked
from the same 'Customers' table that contains the details to be shown on the
form. I want to display the details of the customer selected by the user in
the unbound combo box.

I created the combo box using the wizard. I selected the option: 'Find a

record on my form based on the value I selected in my combo box'.

My problem: The combo box doesn't display all the names from the table.

But if I type a name that is present in the table, and hit the 'Enter' key
on keyboard, it displays the corresponding data on the form.

My queries:
1. Is there a limit on number of rows that can be displayed in the combo

box? If yes, then how to overcome this barrier to show all the names.

Chips · May 4, 2004

There are two things regarding this I have run into.

1. In design view, under format properties for the combo box there is a
"List Rows" property that is at 8 for default. You can set it much higher
without running off the screen. A low count here does not prevent you from
scrolling through all the records, it just limits the rows displayed.

2. This is a little different. This prevents all the records from being
displayed, even if you scroll all the way down the combo box.

If you have messed around with the "column count" in the format group of
properties for the combo box, or if you have messed around with the query
that feeds the combo box and changed the number of columns. The "Column
Count" has to equal the columns in your query. If it doesn't, the combo box
won't display all the records it should.

I've never tried a combo box with 75,000 records to display, so I don't know
about that.

Don't know why this is, but in troubleshooting this when it happened to me
this is what I found.

GC