Results 1 to 2 of 2
  1. #1
    nbdwt73 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2009
    Location
    Charlotte NC
    Posts
    8

    Help with updating pricing on an order entry system

    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...)).

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us about your database. What tables do you have? Can you show us a jpg of your tables and relationships?

    If your underlying application is Stock/Inventory management, you may get some ideas from this free youtube video.

    Here is a generic, free data model for Customers and Orders from Barry Williams' site


    Here's one for Customers and Inventory


    Data is stored in tables. Forms are a means for the user to interface with the database. Processes related to form events are a means to update/insert/delete data in the tables. It is critical to get your tables designed to meet your needs. Business facts dictate your relationships.
    Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Order/Stock Control Database System
    By grantdredelic in forum Access
    Replies: 3
    Last Post: 04-29-2016, 07:20 AM
  2. Updating vendor pricing from Excel
    By sstiebinger in forum Queries
    Replies: 5
    Last Post: 11-20-2015, 09:27 AM
  3. Order Processing System
    By ankit.goenka in forum Access
    Replies: 3
    Last Post: 08-13-2015, 05:44 AM
  4. Product Order/Pricing Database
    By atg in forum Database Design
    Replies: 3
    Last Post: 06-28-2012, 05:45 AM
  5. Double Entry System
    By mkc80 in forum Access
    Replies: 3
    Last Post: 04-27-2012, 03:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums