Ongoing problem!!!

[JimmyG]

Hi everyone!

My database contains two tables. The first table containing customer details and the second table product details. As the products are bespoke they are assigned to a customer (no two customers can order the same product)

I have an order form which contains a subform. On the main order form I select a customer from a list, on the subform I then select the product from a dropdown list and quantity etc. The dropdown list currently lists every product from my products table. I would like to limit the list to only those products that are assigned to the current customer displayed, i.e. when I select a different customer I would like a list of their products to be displayed.

Is this possible?

I have posted this problem, going into much more detail, several times with no luck.
Hopefully this simple description should help you understand what I mean.

 

[FoFa]

You need some way to tie products to a customer.
If your products truly are unique per customer, you could include a customer foreign key field in the products table tying a product to a customer, and use that in your filter/criteria in the product display. But if the "each product is unique to a customer" ever changes, this would become a problem. A better solution would be to create a third table and cross reference the products to a customer that way (via Primary Keys). Then if you ever do have a product that crosses more than customer, it only requires an entry in this table. But this would be a tad more complicated initially to develop the maint. of this table.
Just my 2 cents.

 

[JimmyG]

The products are tied to the customer by CustomerID in the product table.

When I expand the combo box the product name and the Customer ID is displayed. I need to be able to filter the results of the combo box to only display thoses products with the same CustomerID as the CustomerID selected in the main form.

I imagine I can do this in the criteria section of the row source of the drop down box! This would also solve the potential problem you mentioned as I could also include the customer ID for stock porducts (these products are assigned to a customer I have created named "stock items".

I have played around with the builder and can get the drop down list to display the products for the customerID on screen but when I chose a new customer the list does not update. the only way I can get the list to update is to open then close the query controling the combo box row source.

How can I make the code update the list each time a different customer is selected.

Current code:

[Forms]![Orders]![CustomerID]

 

[FoFa]

You have to use the requery method of the control to refresh it.
ControlName.Requery
usually this goes on the OnCurrent event of the form, but you may need it on some other.

 

[JimmyG]

Excellent!

Thanks alot for your help.
This problem has been driving me mad for ages
I thought a simple solution was close!!!!