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

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

Update Prices table with calculated data

Hi
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.
Pete

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

Open in new window

0
petePrinter
Asked:
petePrinter
1 Solution
 
fridomCommented:
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.

0
 
petePrinterAuthor Commented:
fridom
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
Pete
0

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