Returning Relevant Records for Lookup on Subform

[JimmyG]

My database contains an Orders form, from this form the user can select which customer has placed an order using a lookup from the Customers table.

The Orders form also contains a subform, named OrderDetails on which the order details are entered. From this subform the user can select the item that has been ordered. No two customers can order the same item as they are personalised but a customer can order the item every week. The Items are entered on a separate table which stores the item name and the CustomerID.

The problem is that items for different customers can have very similar names leading to the user selecting the wrong item from the list, this then affects reports, which are based on order details.

I would like to be able to limit the list of items available in the order subform item dropdown box dependent on which customer is selected on the main orders form, i.e. only allow items for that customer to be displayed.

Can anyone suggest how I can do this?

At the moment the dropdown box uses a lookup from the item table which list every item in the database.

 

[Pat Hartman]

The requirements are not specific enough to give a definitive answer. It really depends on how you keep your inventory. If your products were t-shirts and towels for example and you carried them in 5 colors and customized them with a customer's logo but custA only orderded Blue items and custB only ordered Green items, you would create a new table that indicated the customer's color requirement so that for his orders, he would only see the item's available in "his" color. This is different from if the items were customized prior to being put into inventory. In this case, you might have dozens of customers who used blue shirts/towels but you wouldn't really care about the color, you would care about the logo which was already placed on the item. Therefore, you would store the customerID in the product table to keep from accidentally selling the wrong product to a customer.

 

[JimmyG]

Nothing as complicated as that...

We sell labels to several customers so each customer has their own unique ranges (each customer has several ranges). When I said the items can have very similar names I meant the range name eg Christmas Labels.

The Labels are stored in the label table by the name of the range and the customerID.

Does this help?

I have just realised another issue which may effect the solution to the problem. There are two standard labels which we keep in stock, these can be ordered by any customer.