This isn't exactly what I've go tut it's easier to explain this way!
I have 2 tables. Items and Orders.
Some of the items are longer and have been discontinued.
The order form it is bound to a table that contains the item id.
I have a combo box. The control source is the item ID from the order table.
The row source is a select from the items table.
Now when an item can no longer be ordered it becomes discontinued. It is left on the item table but can not be used for FUTURE orders but should be visible for historic orders.
Now the record source for the for the combo box doesn't include those that have been discontinued and looks like "SELECT Items.ID, Items.Description, Items.[Is Discontinued] FROM Items WHERE (((Items.[Is Discontinued])=False));" Now this works as the record doesn't appear in the list now and therefore can not be picked for future orders however those orders that used to have a discontinued item in now show a blank where it is displayed.
I can get around this by taking off the where in the above select and validating the item instead but the user down't want to see it in the combo box. Am I missing something here?
I have 2 tables. Items and Orders.
Some of the items are longer and have been discontinued.
The order form it is bound to a table that contains the item id.
I have a combo box. The control source is the item ID from the order table.
The row source is a select from the items table.
Now when an item can no longer be ordered it becomes discontinued. It is left on the item table but can not be used for FUTURE orders but should be visible for historic orders.
Now the record source for the for the combo box doesn't include those that have been discontinued and looks like "SELECT Items.ID, Items.Description, Items.[Is Discontinued] FROM Items WHERE (((Items.[Is Discontinued])=False));" Now this works as the record doesn't appear in the list now and therefore can not be picked for future orders however those orders that used to have a discontinued item in now show a blank where it is displayed.
I can get around this by taking off the where in the above select and validating the item instead but the user down't want to see it in the combo box. Am I missing something here?