Been trying to figure out how to do this without luck.
I've got a subform bound to a wholesale order table which contains a (bound) combo box pulling values from a table of available items. Among the things in this table are prices of the item at certain buyer quantity thresholds, e.g., a field named WSPrice5 that gives the price of of each unit when the buys five, WSPrice6 which gives the price per item when they purchase six, etc. I need to do two things:
When a user selects an item from the combo box, it needs to automatically populate the List Price text box with the value of that item's USListPrice field from the available items table and, most importantly, save it to the CurrentListPrice field in the wholesale table. I know how to make the value display in the text box, but I don't know how to get it to save and it's important that it does (list prices change from time to time, so we need a record of its value at the time of sale).
More importantly, I need to conditionally populate another text box based on 1) the item selected in the combo box and 2) the quantity entered by the user (and save the value to the wholesale order table as well). So for example, if the user selects item 3 and enters a quantity of 7, it would display/save item 3's WSPrice7 value in the Discounted Price text box and save it to the DiscountPrice field. One additional complication is that one of the WSPrice values corresponds to the discount price on orders of 10 or more units, so it needs to grab an item's WSPrice10 price if the quantity entered is 10, 11, 12 or any other larger number.
Any help would be appreciated.