Performance issue: combo vs pop-up form

Let's say in the product table I have around 50,000 products (well,
infact it has now about 2000 only, but it's growing very fast, hence
the question here)

In the order form, users will select the product for the order lines.
They want to see the entire list of available products.

You can't handle the combo the way you've heard: limiting rowsource
until first few characters entered, because
- the product primary key is an autonumber
- and users want all the order lines display appropriately in a
continuous form

In that scenario, I load the entire product table into a pop-up
continuous form (with some search functionality for users to locate
the desired product), then when user double-click a line, assign that
productID value into a line in the orderline subform.

The question here is: Is that pop-up form approach more efficient than
combo box when the number of products reaching tens of thousand?

I would love to hear from anyone who's been there before
Thanks
NB

re: Performance issue: combo vs pop-up form

On 29 Jan 2004 02:27:37 -0800, nickbose@lycos.com (NB) wrote:
[color=blue]
>Let's say in the product table I have around 50,000 products (well,
>infact it has now about 2000 only, but it's growing very fast, hence
>the question here)
>
>In the order form, users will select the product for the order lines.
>They want to see the entire list of available products.
>
>You can't handle the combo the way you've heard: limiting rowsource
>until first few characters entered, because
>- the product primary key is an autonumber
>- and users want all the order lines display appropriately in a
>continuous form[/color]

Actually, you can. What you need is 2 different representation of the
product, a foreign key lookup in the underlying query using an outer join, and
the combo box. Put the combo box behind the textbox, make the textbox locked,
disabled, then add a transparent button (not in the tab order) on top of all
that, and set its Click event handler to set the focus to the combo box.

When the user places the focus on the combo box by tabbing to it or by
clicking on it (actually the transparent button, but they don't see that), the
combo box comes to the front, but only on that row! All other rows still
display properly because they are showing the text box with the combo box
safely hidden behind.

I use this trick often.
[color=blue]
>In that scenario, I load the entire product table into a pop-up
>continuous form (with some search functionality for users to locate
>the desired product), then when user double-click a line, assign that
>productID value into a line in the orderline subform.
>
>The question here is: Is that pop-up form approach more efficient than
>combo box when the number of products reaching tens of thousand?
>
>I would love to hear from anyone who's been there before
>Thanks
>NB[/color]

Even though my trick works, you might want to go ahad and implement the pop-up
because it's highly likely that the user will want to search on something
other than the name, starting from the beginning of the name. There may be
other product fields they want to search on. In fact, the salesperson might
want to use that screen to help determine what product the customer is really
looking for, then just pick it to add it to the order.

Oh, and we could take this a step farther. Instead of having an order and a
pop-up, there could be an order form and a search/shopping cart form. Add
items to the cart on the search form, then select from -those- in a combo box
on the order form. The 2 forms would simply be 2 windows the salesperson
could toggle between rather than one popping up from the other.

re: Performance issue: combo vs pop-up form

> Actually, you can. What you need is 2 different representation of the[color=blue]
> product, a foreign key lookup in the underlying query using an outer join, and
> the combo box. Put the combo box behind the textbox, make the textbox locked,
> disabled, then add a transparent button (not in the tab order) on top of all
> that, and set its Click event handler to set the focus to the combo box.
>
> When the user places the focus on the combo box by tabbing to it or by
> clicking on it (actually the transparent button, but they don't see that), the
> combo box comes to the front, but only on that row! All other rows still
> display properly because they are showing the text box with the combo box
> safely hidden behind.
>
> I use this trick often.[/color]

This trick sounds interesting. I will certainly use it for those large
- and - stable list. Thanks for sharing.


[color=blue]
> Even though my trick works, you might want to go ahad and implement the pop-up
> because it's highly likely that the user will want to search on something
> other than the name, starting from the beginning of the name. There may be
> other product fields they want to search on. In fact, the salesperson might
> want to use that screen to help determine what product the customer is really
> looking for, then just pick it to add it to the order.
>
> Oh, and we could take this a step farther. Instead of having an order and a
> pop-up, there could be an order form and a search/shopping cart form. Add
> items to the cart on the search form, then select from -those- in a combo box
> on the order form. The 2 forms would simply be 2 windows the salesperson
> could toggle between rather than one popping up from the other.[/color]

I have already implemented the pop-up form. It works fine.
If I understand correctly, your suggestion may add an unnecessary step
to the process: users have to search and put the wanted products into
the search form first. Then they have to select each orderline again
in the order form (with a filtered-rowsource combobox)?

(My approach sends the found product directly to the orderline)


I still would want to know if loading an entire large table (no
complex query, just the whole table) into a continuous form is more
efficient than loading that into a combobox.

NB

re: Performance issue: combo vs pop-up form

On 29 Jan 2004 14:32:02 -0800, nickbose@lycos.com (NB) wrote:

....[color=blue]
>
>
>I still would want to know if loading an entire large table (no
>complex query, just the whole table) into a continuous form is more
>efficient than loading that into a combobox.
>[/color]

Yes, it is vastly more efficient if the back-end is JET, but not if it's SQL
Server. I recommend avoiding it, but you can do it in a pinch with no
immediate consequences.