Display previously selected record in dropdown if record no longer in record source. (1 Viewer)

[jacko6]

I have a few ideas, but just wondering how others handle this situation.

Dropdown recordsource is a query.
The query returns tblCust.Firstname, tblCust.Lastname fields where the tblCust.Inactive field is false.
1 year ago a user had selected John Smith in this field on a form and saved the record.
Since then John Smith was marked inactive as they are no longer a customer.
When the record from a year ago is viewed on the form now, the dropdown field is blank because they are inactive.

How could I still display John Smith in the field if it is not in the dropdown recordsource?

All feedback appreciated.

 

[theDBguy]

One workaround is to overlay a textbox to display what's in the table, but I prefer to sort the list to move all inactive records to the bottom rather than filter them out. You can use the BeforeUpdate event to reject inactive items from being selected.

 

[Pat Hartman]

You need to do two things:
1. Add an order by to the RowSource query so that all the inactive items drop to the bottom to keep them from cluttering the list but allow them to remain in it so you don't lose the text value as you are now.
2. Add code in the click event of the combo. If the inactiveFlg is True, then undo the change and cancel the update. Give the user an appropriate message - "This item is inactive and cannot be added to new records or used to change existing records." Since this code is used in every combo, I would place it in a function in a standard module which I call from the click event of the combo and pass in a reference to the control so that the common code can undo the correct control.