Unanswered: Access Inventory Form - I'm out of ideas
I built a database that tracks customer orders and inventory. When a customer places an order, one of the items on the order form is a Combo Box that displays items available to order from the Inventory Table. When an order is placed, a macro decreases the quantity of items left in the Inventory Table by one. The Combo Box is built on a query that looks into the Inventory Table. A criteria in the query is to show items if the quantity left is >= 1. The reason for this is so that over time as new items are added to the Inventory Table, the list in the Combo Box doesn't grow to an unmanageable size plus it should only show items that are in stock. The issue is that if you go back to a customer order that had been placed previously and the item ordered is now out of stock, i.e. the quantity is zero, the Combo Box is blank because of the >= criteria. I would like to be able to continue to display in the Combo Box what was ordered. Can anyone help?
I think that when the order has been placed (and stored in the 'order table'?), then when you redisplayed the order the information comes from the order table (which can/should refer to the inventory table for Item details) with the quantity ordered in the order table. Therefore, there is not need to display a combo box as this order is fixed in time (or should be). P you have relationship problem if you cannot display ordered Items independently of the Inventory table quantity.
Just my thought, which, to say the least, have been known to be wrong!