Hide 'inactive' Status' from dropdown box in other table

[jurbin]

Hi, I have some tables with data where in one column the user will choose a status between 30-40 different options. They are choosing this option to show a reason why a trade was pended and not approved immediately. These reasons seem to change frequently and sometimes we no longer want to use an old reason.

I do reporting back several years, so I cannot just 'remove' a reason or it will be removed from the table and I will have blank reasons. When selecting the specific reason, the table takes the dropdown from another table that simply lists every possible reason. I want to know how I can make it so that the reason that is no longer in use remains on that connected table, but when the person entering data clicks the dropdown button, the removed or inactive reason no longer shows up as an option.

Previously I had seen people add a column to the connected table of reasons and use a "yes/no" check box to show if the reason is now Inactive. When you checked YES on inactive, the reason would disappear from the dropdown list. My issue is that I do not know what language or formula to use (or where to use it) so that when I click Inactive = Yes, the reason disappears from the dropdown menu.

Please let me know if anyone can help.

 

[Frothingslosh]

The dropdown menu (normally called a combo box) uses a SQL statement/query to generate the list that you see. That is saved as the 'Row Source'. If you design it right, it can simply pull a list of all status results where Inactive = False.

An example would be

SELECT tblStatus.StatusName FROM tblStatus WHERE tblStatus.Inactive = False;

That would keep the list in the combo box current no matter how many statuses go active or inactive.

 

[BigHappyDaddy]

Change the rowsource of the dropdown box to a query based on the reason table. For the Inactive field, just set the criteria as "No" (or False or 0). This will return only the reasons you have flagged as active.

 

[BigHappyDaddy]

Argh! Frothingslosh beat me to it!

 

[spikepl]

This is not simple, if you display a table in a datasheet form or continuous form. If you remove items from the rowsource, you'll remove them from ALL instances of the combo, which means your old values - because removed from the RowSource, will not display but something else will (don't remember what).

There are ways to overcome this:

Keep all the values, but for the ones marked Inactive, run an Undo, and display a meaasge saying that you cannot select that value.

Alternatively, you can have two columns: 1 with a combo in which you can change the rowsource, and that is alone used to pick a value... which then is stored in a textbox in a second column, and that is the valid value. This would not be affected by items removed from RowSource.

Finally, check out this fiddle by Pat Hartmann, where a textbox is palced in fornt of the combo: http://www.access-programmers.co.uk/forums/showthread.php?t=223980

Update: I just realised you posed this question in Tables - forum. Just to be certain: you are NOT using lookup fields in you tables, are you? If so then remove them. This question belongs under Forms, because it is in the form that you need to do something.