I have a system (Access 2013) that needs a change and, after several different attempts, I am admittedly stumped. It is a relatively simple idea - an order entry system made up of a form (contains general customer info - name, address, purchase order, etc) and a subform (continuous form with quantity, product, unit price and total price) that allows for several line items to be ordered for this customer. The price data for each line item is calculated by either "AfterUpdate" on the product selection field or a button on the subform (both execute the same "pricing code" that reads a pricing table and loads the appropriate fields on the form). Everything works very well... Now, we have a new requirement - if a customer's order surpasses a certain threshold (in this case total units ordered on this purchase order) the unit price for all ordered products is discounted a fixed dollar amount.
The part I cannot make work... The threshold can be crossed on any line item and once crossed, I need the unit price and total by line item updated for all items. In other words each subform record must be updated after subform record "N" passes the threshhold. I cannot get the prior subform data to update correctly. I have tried reading through the database records related to the subform and changing the unit price and total price data and updating the DB. However, the prior subforms do not reflect the change properly - I have tried requerying multiple ways to no avail.
This cannot be the only system like this discussed in the forum. I have searched for similar issues. My real request is for some sample code or method to do this (I even tried to refocus the forms to the first item in the subform and then skip subform to subform (yeah - bad idea...)).