combos in large app

[miss031]

I read somewhere about loading the contents of a combo box as a user types.
In my app, I use combos for everything, to cut down on replication, because I
am expecting the amount of data to grow quickly. For example, the contact
information form has combos for first and last name, both drawn off a lookup
table of names, and stored in seperate tables for first name and last name,
each with the contact ID.

I expect that there will be a large amount of names in the name lookup
table, so I wonder if I should leave the sources of the combos empty until
the user begins typing, and load them with only the matching names.

I am looking for speed and efficiency. What will be quicker: combos with
long lists of selections, or combos that load upon the user's entry?

 

[Anthos]

The speed issue of the combo box is more on the opening of the form.
If it has no source, or the returned result of the source is very
small, then the combo box will fill very quickly.

If however, you have a large data source, you are better off building
the data source as you type, (and if using a SQL backend, use a pass
through query)

Hope this helps.

Kind Regards,
Anthony Moore

 

[Rick Brandt]

I read somewhere about loading the contents of a combo box as a user
types. In my app, I use combos for everything, to cut down on
replication, because I am expecting the amount of data to grow
quickly. For example, the contact information form has combos for
first and last name, both drawn off a lookup table of names, and
stored in seperate tables for first name and last name, each with the
contact ID.

I expect that there will be a large amount of names in the name lookup
table, so I wonder if I should leave the sources of the combos empty
until the user begins typing, and load them with only the matching
names.

I am looking for speed and efficiency. What will be quicker: combos
with long lists of selections, or combos that load upon the user's
entry?

Try both and see. It would be silly for us to speculate as there are two many
local issues (your network speed etc.), for us to give an accurate answer.

I generally filter the RowSource for Combos if the list will have more than a
few thousand rows. However; I do that to reduce LAN traffic and resources on my
database server, not because it will make the list faster for the user. In many
cases a really long list in a ComboBox with AutoExpand enabled will still be
plenty fast.

 

[miss031]

If however, you have a large data source, you are better off building
the data source as you type, (and if using a SQL backend, use a pass
through query)

Thank you. Please elaborate on how I would do this.

 

[miss031]

I generally filter the RowSource for Combos if the list will have more than a
few thousand rows. However; I do that to reduce LAN traffic and resources on my
database server, not because it will make the list faster for the user. In many
cases a really long list in a ComboBox with AutoExpand enabled will still be
plenty fast.

Thank you. That's exactly what I mean - not actual speed for the user, but
drain on resources. Please tell me how you would recommend I proceed.

 

[Rick Brandt]

Thank you. That's exactly what I mean - not actual speed for the
user, but drain on resources. Please tell me how you would recommend
I proceed.

What I typically do is use the GotFocus event to prompt the user for starting
characters and then set the RowSource of the Combo to a SQL statement using
those characters as filter criteria. Even just 2 or 3 characters will usually
reduce the list size to a manageable number of rows.

On one or two occasions I got fancier and laid a TextBox on top of the Combo and
made it look more seamless, but I generally find that to be more trouble than
it's worth. Particularly if you want a seamless response when the user
backspaces or deletes.