Solved Combo boxes and inactive combo box values (1 Viewer)

[rej_co]

Hi. I have a database that stores general information about software. In that form, there's a combo box used to identify the network where the software is used. I have a General Information table for the general stuff, and a Network table for the available networks. Both tables use the Auto-number as primary keys. The primary key for the Network is linked to the foreign key of the GenInfo table. The general consensus I found over the years is that this is standard practice when it comes to PKs, FKs and relationships. In the form, the Network CBO record source query pulls the Primary Keys and associated Network Names from the Network table. It has a Column Count of 2, Columns Widths of 0";1" and Bound to Column 1 so that it works with the Primary Key in the back end, but displays the Network name in the CBO on the form.

Once in a while, there's a Network that gets decommissioned/shut down permanently. I have a True/False field in the Network table that I use to "disable" a record when it's set to True. I use this to keep the Network CBO on the form clutter free from old Networks that no longer exist. I also need to keep the historical data, so I can't just delete the Network record, nor any associated the General Info records. The Network CBO record source is set to only show "enabled" Network records.

This presents as a design flaw that I don't know how to get resolved or get around where the issue is that let's say I had a General Information record on Adobe Reader, and that record has the Network "Net1234" stored in it. A month later, that network is shut down and I disable the record for "Net1234" in the Network table by setting the "Disable" field to True. When I go to that Adobe Reader record on the form, the Network CBO shows up as blank instead of displaying "Net1234"; obviously because the record source query specifies to only show "enabled" records. Note: as a test, if I set the CBO Column Widths to 0.5";1" it does show the PK for the disabled Network record in the CBO, so the relationship is still there.

I'm sure this isn't a unique situation where once in a while over time certain ComboBox values are "retired/disabled" from any future use, but still need to keep the historical info visible on the form. The only alternative is to always show the full list, including the disabled ones, but then it doesn't keep the front end clean.

Any suggestions or pointers would be really helpful and appreciated.

Thanks

 

[June7]

You're right, this is a very common topic - known as cascading or conditional/dependent combobox/listbox.

Let combobox show complete dataset if form is allowed to show all records even if network is disabled.

Then have code change combobox RowSource to restricted set only for new record entry. However, this means on continuous or datasheet form existing "disabled" records will show blanks during this data entry.

Another option is to include networks lookup table in form RecordSource and bind a locked textbox to the descriptive field.

 

[arnelgp]

as said, there is a hack if you are using continuous form.

 

[rej_co]

Ahh. Thank you June7, i think your first suggestion will work.
I don't use continuous or datasheet forms.