473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

Nov 1 '07 #1
4 2080
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.

Nov 2 '07 #2
On Fri, 02 Nov 2007 15:12:16 -0700, Wayne <cq*******@volcanomail.comwrote:
>
>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.
Nov 3 '07 #3
Wayne <cq*******@volcanomail.comwrote in
news:11**********************@i13g2000prf.googlegr oups.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

Nov 3 '07 #4
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.
Nov 3 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Barry Edmund Wright | last post by:
I would really appreciate your assistance. I am using Access 2000 to create a form that Lists Names and Addresses based on a number of selection criteria one of which is a combo box cboPCZip. All...
3
by: James | last post by:
Hi, I have a combo box(cmboParts) and the row source is a part name. Once a part is selected I have button(cmdOrderAdd) to add data about this part to a sub-form(frmParts_Ordered). However this...
0
by: CSDunn | last post by:
Hello, In Access ADP's that connect to SQL Server databases, any time I have a situation where I have a combo box in a main form that looks up a record in a subform, the subform record source has...
3
by: Mark | last post by:
I have a form that contains the following: 2 radio buttons (Name and Number) 1 check box (for Inactive Members) 1 combo box (list of Member names or numbers) I use the radio buttons to...
2
by: edworboys | last post by:
I have designed a data entry form with a number of fields and a sub form. The first field (Country) is a combo box and the user selects a country. This, in turn reduces the number of options in the...
12
by: Nhmiller | last post by:
When I start to type the data for a new record, I would like a dropdown box to open next to it from which I can choose the data from a previously entered record that starts with the same letter....
13
by: nyt | last post by:
I have a problem of number and text field. I got the database file(mdb) that contains many combo boxes used and its list values are created by "value list" For eg field Field name= 'furniture'...
16
by: agrawal.solutions | last post by:
Hello Friends I am asking a very silly question but i dont find any solution fo this.. I am selectiong a recordset and want to populate a combobox where id would be inviseble and the content...
7
by: badboybrown | last post by:
I know that this is an age old question and it has been approached a few times, but I've hit a wall and require some help. I have various combo boxes that contain values that are both "current"...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.