Subform issue with growing dropdown list

[wizard_chef]

I have a fairly typical customer-product Access DB that uses the
following scheme to log in an order:

A customer is selected from the customer table using a form showing
customer contact info, and a popup window is used to enter the order.
The popup window is a form that includes a subform. The subform is in
datasheet format, and constains a list of product description (select
from drop-down list), quantity, price, etc.

Here's the problem. The product list is growing, so now the product
drop-down list is getting hard to navigate to select the product and
the problem is getting worse. What I want to do is to create a product
"sub-table" that contains a list of only the current products, and only
include those in my drop-down list for selection of a sale. When I try
to do this, it breaks the list of previous orders for a given customer,
since the previous items no longer show up in the product sub-table.

What is the best way to do this? Create an entirely new popup order
form with the new product sub-table? Modify the current form in some
way (how?)?

wizard_chef

 

[Arvin Meyer [MVP]]

I would:

1. Add a field (boolean) for current products.
2. Add current to the combobox criteria.
3. Sort the existing orders so that only the newer ones show up.

Then, I'd add code to change the rowsource of the combobox back to show all
the products in the combobox if you searched for an order the occured before
a date cutoff.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[KARL DEWEY]

You might also turn on the AutoExpand property of the ComboBox.

 

[wizard_chef]

Yes, autoexpand has been on all along.

I added the 'current' field to the product table, and selected default
as 'no'. Then, I changed row select for the combo box to 'yes'. That
gave me the results I am looking for for the entering new orders (and
seeing only 'new' products), but I could no longer see the descriptions
of the previous orders that are shown in the combo box. So, I tried
this filter:

IIf([Orders]![OrderDate]<Date(),No,Yes)

applied on the 'current' field. This does not work. Now I see all of
the products for all dates and for the dropdown for new products.

Another solution for my problem that I thought of is to simply set the
focus of the combo box list to the top of new product list (which
products are always grouped together) when the combox box is expanded.
How/can one do this? Also, if the 'current' field method can be made to
work, or if the focus method can be made to work, I'd like to be able
to set these parameters with a form so that non-Access users can set
these parameters. How hard is that?

Thanks for the help!!!

wizard_chef