Items no longer available in combobox

[spikepl]

Imagine you have an order form, and an order details subform. On the subform, for each line, you have a combo, allowing you to select a product. All works fine.

Your product list undergoes changes with time, and some products may become unavailable. So you have an IsAvailable=True/False in the tblProducts. So your combo row source only includes products where IsAvailable=True. That's great, and prevents errors in ordering.

But, if you review past orders, and some ordered products are no longer available, then the combo has a glitch, since the discontinued product is no longer in the row source.

I have some ideas, but I'd like to see whether anybody has any brilliant thoughts about coping with this.

 

[vbaInet]

Include it in the Row Source for past orders. Have a separate tab for past orders.

 

[NickHa]

My solution to this one is to use the NewRecord flag to determine what is shown in the combo box. When true, the combo data source query has 'where valid' to restrict the selection and when false, no where clause. In essence, filtering according to circumstance. That way records which use the obsolete records still show the proper value and new records can't choose them.
Hope this helps!

 

[vbaInet]

My solution to this one is to use the NewRecord flag to determine what is shown in the combo box. When true, the combo data source query has 'where valid' to restrict the selection and when false, no where clause. In essence, filtering according to circumstance. That way records which use the obsolete records still show the proper value and new records can't choose them.
Hope this helps!

spikepl is talking about a Continuous form or a form in Datasheet here. It won't work. You can't have different criteria per row unless via some complicated means.

 

[spikepl]

#2 Good idea, and very workable, as long as one does not have to mix old with new.
My specific problem is on order details, when copying an old order to a new one, where some products may have become discontinued - I still need to show what was ordered, but indicate that the items shown were discontinued and that a replacement needs to be found. There are clumsy ways to do this and smart ones.

#3 That's tricky, as vbaInet points out in #4. There is a sample here on the site somewhere, I believe, that deals with cascading combos in a datasheet/continuous forms (where you again have the problem, that a combo can only have one rowsource, no matter which record in a continuous form/datasheet), and it shows a lot of fiddling with textboxes and combos.

Thinking more closely about this, one could perhaps do some fiddle with two combos made visible/invisible in the same spot - one containing all products, for display, and one the currently available set, for ordering.

#4 Exactement!

 

[vbaInet]

You can always show the old orders on a separate subform just below the active/new orders subform.

 

[spikepl]

#6 That's a great idea. In the actual case I am stuck, because the application has progressed too far, and I have no screen real-estate left ( and scrolling is not acceptable to the user), but I will remember it .. while still thinking ...

 

[vbaInet]

It will still be the same amount of space you require if you separate them. For example your active/new orders subform will show a maximum of 3 records whilst your old orders subform will show 2 records. That makes it just 5 rows. No space still?

 

[spikepl]

#8 In this particular instance, no. The reason is that order-detail lines contain other items that are not products, such as comments on delivery and freight. This is dictated by the workflow of the business, and is the reason why I am short on screen. Above all, scrolling is to be avoided.

I'll come up with something after sleeping on it, one of these days

 

[spikepl]

I havde decided to include all products in the combobox, also the discontinued ones.

In the situation when a credit note has to be issued, based on copy of the original invoice, the fact that the products are no longer available has no influence on the amounts to be credited.

In the situation when an old order is copied, and some products have become unavailable, I color a field red, in the relevant line of order details, to notify the user that the product is no longer available and some replacement must be selected or line discarded.. And I do not allow the order to be processed until all order lines only contain available products.

 

[vbaInet]

I thought about that but I thought you said you wanted filtered records? I guess that works anyway.