Update Prices table with calculated data

Posted on 2011-04-24
Last Modified: 2012-05-11
i have a form (frmNationalPrices)
a unbound cbo is used to select a product.(tblPricesProductsID)
this feeds a subfrm to display a datasheet of suppliers who have submitted a price (tblPricesItemRetailPrice)

On the same form i enter some data and end up with a new price in a unbound txtbox(txtCaculatedPrice)

please help with how on a event like a button press i can update a single price from the prices tbl.
i can run a select qry to display the correct row but can not find a way to update it without reentering the price.
Thanks for your help
i have attached the sql used to selected the prices.

SELECT qrySubSuppPrices.pricesID, qrySubSuppPrices.tblPricesProductsID, qrySubSuppPrices.tblPricesSupplierID, qrySubSuppPrices.tblSupplierName, qrySubSuppPrices.tblPricesItemRetailPrice
FROM qrySubSuppPrices
WHERE (((qrySubSuppPrices.tblPricesProductsID)=[Forms]![frmNationalPrice]![cboProduct]));

Open in new window

Question by:petePrinter
    LVL 24

    Accepted Solution

    You do not use the price in the where clause but e.g the other fields you an use the price field also but that would be unusual.
    you want something along this lines:

    update some_table
    set price = new_price
    where tblPricesSupplierID = Me!tfSupplierID

    If you'd posted a test database one could have helped you much better.


    Author Closing Comment

    Thanks that put me on the correct track
    this sql solved it

    UPDATE qrySubSuppPrices SET qrySubSuppPrices.tblPricesItemRetailPrice = [Forms]![frmNationalPrice]![txtCalculatedPrice]
    WHERE (((qrySubSuppPrices.tblPricesProductsID)=[Forms]![frmNationalPrice]![cboProduct]) AND ((qrySubSuppPrices.tblPricesSupplierID)=45));
    Best Regards

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now