Dealing with Inactive Items in a Combo Box Row Source

I love the combo box control, but there is a common challenge when dealing with combo boxes on continuous forms:

What do you do with inactive row source items on continuous forms?

You want the user to be able to easily choose from a list of active items.  But the combo box needs to deal with inactive items that belong to historical records.  If you set the row source to active items only, then some of your historical records will appear blank.

This is much easier to demonstrate with an example.

EXAMPLE: Discontinued Products on Invoices

Data Structure

Consider the following two tables:

The Product table has three fields:

• ProductID: the autonumber surrogate primary key
• ProductName: the name of the product
• DiscontinuedOn: the date the product was discontinued; if this field is filled in then the product is no longer sold, but it could still be a part of past invoices

The InvoiceDetail table has four fields:

• InvoiceDetailID: the autonumber surrogate primary key
• InvoiceID: foreign key to the Invoice table (not shown)
• ProductID: foreign key to the Product table
• Quantity: the number of products ordered

Sample Form

The InvoiceDetail form is a simple continuous form with two controls in the detail section:

• cbProductID: combo box bound to the InvoiceDetail.ProductID field
• tbQuantity: text box bound to the InvoiceDetail.Quantity field

Discontinued Product: Purple Stars

The Purple Stars product was discontinued on 3/2/2020.

This means that users should not be able to choose this product from the combo box.  However, InvoiceID 2 includes an order for a single Purple Stars product:

The Objective

We want to find a solution that meets the following criteria:

1. The combo box is populated for each entry
2. Users can't accidentally choose the discontinued product, Purple Stars
3. The row source is optimized for choosing active products

Attempt #1: Show All Products

In the first attempt, demonstrated in the above screenshots, this is what the RowSource property looks like for the cbProduct combo box control:

SELECT ProductID, ProductName FROM Product; 

Here's what the combo box contents look like:

Let's grade our three criteria from above:

1. PASS
2. FAIL
3. FAIL

The combo boxes are all populated, but there is no distinction between active and inactive products when creating new records.

Attempt #2: Show Only Active Products

To address our failures in criteria 2 and 3, let's change our row source to eliminate discontinued products:

SELECT ProductID, ProductName FROM Product 
WHERE DiscontinuedOn Is Null; 

Here's how the form looks now:

1. FAIL
2. PASS
3. PASS

We passed criteria 2 & 3 with flying colors.  It is now impossible to select a discontinued product from the combo box.  

But that comes at a price.

Since every detail record uses the same row source for its combo box, the Purple Stars item is no longer visible in record 2.  So far, we appear to be stuck in a Catch-22.  The solution to criterion 1 causes criteria 2 and 3 to fail.  The solution to criteria 2 and 3 doesn't meet our needs for criterion 1.

Let's try again.

Attempt #3: Show the Discontinued Status

In this attempt, we will add another field to the row source to indicate which products are discontinued:

SELECT ProductID, ProductName, IIf(DiscontinuedOn Is Null, "", "Discontinued") FROM Product; 

To make things a bit more interesting, I decided to discontinue the Blue Squares product in addition to the previously discontinued Purple Stars:

1. PASS
2. PASS ... sort of
3. FAIL

We're getting closer.  We can see the Purple Stars record again.  And now at least we are showing the user which products are discontinued.  However, it's still too easy to accidentally choose one of our discontinued products.  Also, if the user starts typing in "Bl" into the combo box, it will autocomplete to the discontinued product "Blue Squares" instead of the active product "Black Triangles."

Attempt #4: Change the Name and Sort Order

This time we are going to change the display name and also modify the sort order to make it clear which products are inactive and prevent the user from choosing them accidentally:

SELECT ProductID,
  IIf(DiscontinuedOn Is Null, "", "*") & ProductName,
  IIf(DiscontinuedOn Is Null, "", "Discontinued " & DiscontinuedOn) 
FROM Product 
ORDER BY IIf(DiscontinuedOn Is Null, 1, 2), 
  ProductName; 

With this final attempt, all three of our original criteria are met:

1. The combo box is populated for each entry
2. Users can't accidentally choose the discontinued product, Purple Stars
3. The row source is optimized for choosing active products (typing in "Bl" now autocompletes to the active product Black Triangles)