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.

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

"Wayne" wrote

> 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.

Do you mean you use an SQL statement that retrieves one Field from a Table,
or a Query that returns one Field from a Table? Row Source can be either of
those, or a Value List defined in the Properties of the Combo Box.

I can't imagine any justification for maintaining a separate table of
Customers for this purpose... that would violate relational database design
guidelines (redundant, not normalized), and, as you point out, would be a
maintenance nightmare.

Of course, populating the Row Source will be slower over a LAN, or
especially over a slower WAN if you are using a Server DB back end (I've
never found performance to be satisfactory using a split Jet back end over a
WAN... used one on an old 4 MBPS LAN in one location, but it there was not a
sufficient quantity of data to be a real problem). But, if you have a 100
MBPS LAN, it probably will not be significant.

Larry Linson
Microsoft Access MVP

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

On Nov 2, 2:04 pm, "Larry Linson" wrote:
> "Wayne" wrote
>
> > 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.
>
> Do you mean you use an SQL statement that retrieves one Field from a Table,
> or a Query that returns one Field from a Table? Row Source can be either of
> those, or a Value List defined in the Properties of the Combo Box.
>
> I can't imagine any justification for maintaining a separate table of
> Customers for this purpose... that would violate relational database design
> guidelines (redundant, not normalized), and, as you point out, would be a
> maintenance nightmare.
>
> Of course, populating the Row Source will be slower over a LAN, or
> especially over a slower WAN if you are using a Server DB back end (I've
> never found performance to be satisfactory using a split Jet back end over a
> WAN... used one on an old 4 MBPS LAN in one location, but it there was not a
> sufficient quantity of data to be a real problem). But, if you have a 100
> MBPS LAN, it probably will not be significant.
>
> Larry Linson
> Microsoft Access MVP

Thanks for the response Larry. What I mean is using a query that
returns one field from a table. Say for instance I have an "Orders"
table and "Customer" is one field of the table. I use the Customer
field as the rowsource for the Customer combobox on the data input
form so that all previously entered customers are listed in the
combobox. Obviously I have to set the "Unique Values" property to
"Yes" in the query properties because the same Customer may appear
many times.

My main concern is that the combobox may be slow to populate as the
record count gets into the hundreds of thousands. I'm not sure that
this will happen. It was just a thought that I had.

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

On Thu, 01 Nov 2007 23:32:21 -0700, Wayne wrote:

>On Nov 2, 2:04 pm, "Larry Linson" wrote:
>> "Wayne" wrote
>>
>> > 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.
>>
>> Do you mean you use an SQL statement that retrieves one Field from a Table,
>> or a Query that returns one Field from a Table? Row Source can be either of
>> those, or a Value List defined in the Properties of the Combo Box.
>>
>> I can't imagine any justification for maintaining a separate table of
>> Customers for this purpose... that would violate relational database design
>> guidelines (redundant, not normalized), and, as you point out, would be a
>> maintenance nightmare.
>>
>> Of course, populating the Row Source will be slower over a LAN, or
>> especially over a slower WAN if you are using a Server DB back end (I've
>> never found performance to be satisfactory using a split Jet back end over a
>> WAN... used one on an old 4 MBPS LAN in one location, but it there was not a
>> sufficient quantity of data to be a real problem). But, if you have a 100
>> MBPS LAN, it probably will not be significant.
>>
>> Larry Linson
>> Microsoft Access MVP
>
>Thanks for the response Larry. What I mean is using a query that
>returns one field from a table. Say for instance I have an "Orders"
>table and "Customer" is one field of the table. I use the Customer
>field as the rowsource for the Customer combobox on the data input
>form so that all previously entered customers are listed in the
>combobox. Obviously I have to set the "Unique Values" property to
>"Yes" in the query properties because the same Customer may appear
>many times.
>
>My main concern is that the combobox may be slow to populate as the
>record count gets into the hundreds of thousands. I'm not sure that
>this will happen. It was just a thought that I had.

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

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

On Nov 1, 7:32 pm, Wayne wrote:
> 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.

Wouldn't it be better to filter the list in some way, maybe from
another combo box? A combo list with that many items is practically
unusable. I would recommend opening up a form to help people filter
that to a shorter list.

Years ago, I created a form for botany taxonomy for the Smithsonian
Museum: classes, family, order, genus, species, etc. As you can
imagine, the species table was very large.

It was a series of combo boxes, that filtered the subsequent one (via
a criteria reference in the combo box's row source query). Works very
nicely and only needed a bit of code to requery the next combo box on
the onclick event of the previous one.

Luke Chung
President
FMS, Inc.
http://www.fmsinc.com

P.S. Here's a paper I wrote on validating combo boxes which you may
find helpful:
http://www.fmsinc.com/free/NewTips/Access/ComboBox/AccessCom boBox.asp

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

> 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.

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

Wayne wrote in
news:1194041536.953500.309950@i13g2000prf.googlegroups.com:


> 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

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

On Fri, 02 Nov 2007 15:12:16 -0700, Wayne wrote:

>
>> 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.

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

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.