Store ID or Store Text?

[micks55]

When I select from a Product combo, the ProductID is stored in the OrderDetails table.
The Product combo excludes where Product.Discontinued = True.
In time old Products become discontinued.
Now when I view an old order, the combo is blank because its RowSource excludes discontinued products.

The solutions that I can see are…
1) Forget about storing the ProductID and store the Product Name instead. This increases the size of the OrderDetails table and would need a text control as well as the combo on the form but this seems to be what MS advocate in Northwind.
2) Include discontinued products in the combo and sort by Discontinued then Product Name so that all of the Discontinued are at the bottom of the list. In the combo AfterUpdate tell user that they can’t select a discontinued product if they try to. Occasionally run a delete query that dumps Products that are discontinued and not included in an order.

Is there another way? If not, I’m inclined towards 1, but then what’s the point of having a ProductID field?

I’m using A2k because it ain’t broke (ok, it presents challenges but I’m a glutton for punishment).

 

[micks55]

I’ll try to simplify…
A combo that excludes discontinued products shows blank if the product is discontinued after it was selected.
How do I resolve this?
Is there a best practice?

 

[boblarson]

Yes, include them in the rowsource but set it so that they sort at the bottom and perhaps have a prefix added.

So, if you had a SQL rowsource of

Select ProductID, ProductName
ORDER BY ProductName

You could use this:

Select ProductID, IIf([Discontinued]=True,"Disc_" & [ProductName], [ProductName]) As ProdName
ORDER BY [Discontinued], ProductName

And that way they are in the list so they will show but the discontinued will show at the very bottom of the list and with a visible thing which shows it is discontinued.

 

[micks55]

Bob,
I'll follow your suggestion. Many thanks for taking the trouble to reply.
Mick