Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Update Prices table with calculated data

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

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

petePrinterAuthor Commented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now