Using a Data Field As The Rowsource For A Combo Box

I've used a data field as the rowsource for a combo box many times.
The main advantage that I see is that a separate table of say,
"Customers" does not have to be constantly updated. When a new
Customer is added to the data table in question, they appear in the
combobox that uses the Customers field as its rowsource the next time
round.

I've only done this in databases where the record count will never get
to more than about 10000. Are there any inherent dangers in doing
this? For instance, if the record count gets up into the hundreds of
thousands would the combo be slow to populate, especially over a LAN?

Are there any alternatives to the method that I am using other than
maintaining a separate list of Customers in its own table and using
this as the recordsource for the combo? I use this method in some
situations, but if the list is constantly growing it is inconvenient
for the user to be constantly adding to it.

Quote:

Wayne,
>
If your concern is a very long list in the combobox, would adding a textbox
where the user would enter the first (or possibly the first few) letters of the
customer name and use that to filter the combobox list be of any help?
>
Chuck

Chuck, my concern is not that the combobox list will be long, but
rather the list of records in the main data table that supplies the
rowsource for the combo is very long, say 200,000 records. While
there may be only 200 unique Customers, the combo box still has to
resolve the 200,000 underlying records, pick out the unique values and
then display them in its list. This takes time, especially over a LAN
and if the user is relying on the "Autoexpand" property to populate
the combo as they type there is a significant lag. If the underlying
data table only has a couple of thousand records this isn't an issue
because everything happens far more quickly.

I don't know if there is any way around this other than having a
dedicated "Customer" lookup table for the rowsource of the combo which
is the method I use when the underlying list, in this case Customers,
is fairly static. The downside of this is that the user has to add to
this table every time there is a new customer and this becomes
tiresome if the list of customers is growing rapidly. With the first
method, new customers are automatically added to the list as they are
entered into the underlying Customer Details data table.

On Fri, 02 Nov 2007 15:12:16 -0700, Wayne <cqdigital@volcanomail.comwrote:

Quote:

>

Quote:

>Wayne,
>>
>If your concern is a very long list in the combobox, would adding a textbox
>where the user would enter the first (or possibly the first few) letters of the
>customer name and use that to filter the combobox list be of any help?
>>
>Chuck

>
>Chuck, my concern is not that the combobox list will be long, but
>rather the list of records in the main data table that supplies the
>rowsource for the combo is very long, say 200,000 records. While
>there may be only 200 unique Customers, the combo box still has to
>resolve the 200,000 underlying records, pick out the unique values and
>then display them in its list. This takes time, especially over a LAN
>and if the user is relying on the "Autoexpand" property to populate
>the combo as they type there is a significant lag. If the underlying
>data table only has a couple of thousand records this isn't an issue
>because everything happens far more quickly.
>
>I don't know if there is any way around this other than having a
>dedicated "Customer" lookup table for the rowsource of the combo which
>is the method I use when the underlying list, in this case Customers,
>is fairly static. The downside of this is that the user has to add to
>this table every time there is a new customer and this becomes
>tiresome if the list of customers is growing rapidly. With the first
>method, new customers are automatically added to the list as they are
>entered into the underlying Customer Details data table.

Make a query based on the table that has all the customer names listed.
Working in design mode, only include the customers name. In the menu bar,
click on the large sigma symbol. A new row "Total" will appear. Click on the
down arrow in that row and select Group By. Open the query. Only one instance
of each customer name should appear. Save the query with a good distractive
name. Make that query be the source for the data in your combobox.

Wayne <cqdigital@volcanomail.comwrote in
news:1194041536.953500.309950@i13g2000prf.googlegr oups.com:

Quote:

Chuck, my concern is not that the combobox list will be long, but
rather the list of records in the main data table that supplies
the rowsource for the combo is very long, say 200,000 records.
While there may be only 200 unique Customers, the combo box still
has to resolve the 200,000 underlying records, pick out the unique
values and then display them in its list. This takes time,
especially over a LAN and if the user is relying on the
"Autoexpand" property to populate the combo as they type there is
a significant lag. If the underlying data table only has a couple
of thousand records this isn't an issue because everything happens
far more quickly.
>
I don't know if there is any way around this other than having a
dedicated "Customer" lookup table for the rowsource of the combo
which is the method I use when the underlying list, in this case
Customers, is fairly static. The downside of this is that the
user has to add to this table every time there is a new customer
and this becomes tiresome if the list of customers is growing
rapidly. With the first method, new customers are automatically
added to the list as they are entered into the underlying Customer
Details data table.
>
>

Wayne,

This scares me, because if you don't have a customers table, with
one row per customer, storing not just the name, but address, phone,
etc, you are really slowing things down and clogging the lan if you
have that information repeated 1000 times in the Details table.

That's a lot of redundant information that can give you all sorts of
other problems too, Like having to rekey the address every time, or
you look up from a previous record. Say the customer moves. If you
have one place where the address is stored, et's easy to change.
1000, not so easy.

As to adding info to a separate form, that is not necessarily true
that it's tiresome if the design is well planned. The customers form
can automatically pop up if the name isn't found in the combobox,
with that name filled in, ready for the other info, which othrewise
needs to get added on the main form anyways.

Just something to think about.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Thanks for the info folks. Bob, I know exactly what you mean and
using "Customers" as an example was probably not the best choice on my
part.

Using a Data Field As The Rowsource For A Combo Box

re: Using a Data Field As The Rowsource For A Combo Box

re: Using a Data Field As The Rowsource For A Combo Box

re: Using a Data Field As The Rowsource For A Combo Box

re: Using a Data Field As The Rowsource For A Combo Box

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about