Preserve selections from Combo box after Lookup table entry is deleted

How can I preserve selections made on a form from a Combo box if an item that was previously selected is deleted from the source lookup table?

i.e.  On a purchase order form, a combo box provides for selection of a vendor to be associated with the purchase order ID.
At a later time that Vendor is removed or flagged inactive from the source lookup table but I want to be able to re-open the purchase order form and still see what vendor was selected at the time the purchase order was created.  I do not want the deleted/inactivated vendor to show up in the combo box for future purchase orders.
Wayne MarkelOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
I generally add an "Active" or "Thru_Date" column to my lookup tables to allow me to inactivate those options.  The difference is that on the forms Current event, you must requery those combo boxes, or use a different query as the RowSource.  So you might do something like:  Create two querys

SELECT * FROM yourLookup where Thru_Date is NULL

SELECT * FROM yourLookup

Then, in the Current event of your form:

Private Sub Form_Current

    if me.NewRecord then
        me.cbo_Something.RowSource = qry_Combo_Active
        me.cbo_Something.RowSource = qry_Combo_All
    End If

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  You need to detect if your adding a new PO or editing an old one.  You can set a flag (a hidden control or variable) to indicate if your adding or not.

 Set the flag to true in the BeforeInsert, and clear the flag in the on current.

 When you set the flag, change the combo's rowsource to a SQL statement or query that returns all the vendor records (ignoring the inactive flag you've set) and requery the combo.

In the oncurrent event, check the flag and if set, clear it and set the combo back to the way it was.

 If you really have a case where the vendor record has been deleted, then you have a design problem because that vendor record should not have been allowed to be deleted as long as a PO was on file.

 Another way to approach this which is not as clean is to always show all vendors, but include an inactive column in the combo.  Then in the BeforeUpdate of the combo, if your on the new record, check that the currently selected vendor is active.  If not, message the user "You must select an active vendor for a new PO" and cancel the update.

I usually include all the options for a combo but I sort them so that the inactive items fall to the bottom of the list so they don't interfere with data entry but I also don't have to worry about requerying on a continuous form to show values for rows with inactive items selected.  When a combo shows both active and inactive options, I include a visible column so the user has a visual clue as he is scrolling through the list.   Of course, if you include all the inactive values, you must as Jim says, check the inactive flag and prevent inactive items from being selected.
Wayne MarkelOwnerAuthor Commented:
I used the rowsource method suggested by Dale Fye
I used Jim Dettman's suggestion of setting up referential integrity
I used Pat Hartman's suggestion for display of fields in the combo box and sort order of the recordsource query

The solution is in the Production Database!

Great Work Guys!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.