How can I do this?

[marystewart78]

I am making a database for a small company who charge different amounts for their products depending on the customer - I'd like the products to be choosen from a list and that particular customers unit price to automatically appear!

Currently I have a Customers table, a products table, a price list table (consisting of custID, prodID and unit price) and an orders table.

How do I link these and create a list of products and prices specific to the customer which can be chosen and inserted into the orders table?

Any help is graciously recieved,

Regards,

Mary

 

[Pat Hartman]

Create a query that joins the price list table with the products table and use that as the RowSource for the combo where you choose a product. In this query, add selection criteria to limit the list to only products for the current customer.

Select tblPrice.ProdID, tblProduct.ProdDesc, tblPrice.Price
From tblPrice Inner Join tblProduct on tblPrice.CustID = tblProduct.CustID
Where tblPrice.CustID = Forms!YourForm!CustID;
In the AfterUpdate event of the combo, save the price list price in the order details table.

Me.Price = Me.cblProduct.Column(2)

.Column(2) is actually the third column of the combo's RowSource. If the price is in a different column, adjust the number. The columns are a zerobased array so column 1 is .Column(0), 2 is .Column(1), etc.