I am having an issue with my DB subform. I have configured a subform with Columns: ProductCode (Primary Key), No, Colour-ID (which is converted into a colour name from another reference table), Quanity, Price/Piece( In Greek…Τιμή/Τμχ.) , Total Price.
The last 3 columns are listboxes pulling data from queries. Price/Piece can take 3 different result-sets depending on the No-range. So I wrote the following query to define the 3 result sets:
SELECT
CASE
WHEN “tbl_Order_Products”.“No” BETWEEN 1 AND 3
THEN (SELECT “tbl_Product_Prices”.“Price” FROM “tbl_Product_Prices” WHERE “tbl_Order_Products”.“Προϊόντα” = “tbl_Product_Prices”.“Product” )
WHEN “tbl_Order_Products”.“No” BETWEEN 4 AND 6
THEN ( SELECT “tbl_Product_Prices”.“Price” + 5 FROM “tbl_Product_Prices” WHERE “tbl_Order_Products”.“Προϊόντα” = “tbl_Product_Prices”.“Product” )
WHEN “tbl_Order_Products”.“No” BETWEEN 7 AND 10
THEN ( SELECT “tbl_Product_Prices”.“Price” + 10 FROM “tbl_Product_Prices” WHERE “tbl_Order_Products”.“Προϊόντα” = “tbl_Product_Prices”.“Product” )
ELSE 0
END AS “Τιμή/Τμχ.”
,“tbl_Order_Products”.“Προϊόντα”
FROM “tbl_Order_Products”
,“tbl_Product_Prices”
WHERE “tbl_Order_Products”.“Προϊόντα” = “tbl_Product_Prices”.“Product”
So it checks which “No” condition is true for each record of the Order Products table which is the subform data table, and outputs a SELECT for each case.
It actually works fine untill you enter a product record for a second time, in which case it does not execute the query to know which price to display, it automaticaly outputs the cached price from the last entry of the same product, which is incorrect because the last product was for example a No2 instead of a No10 which has a different price.
I cant seem to know how to bypass this procedure does anyone happen to know the answer?
Thanks!