Updating Tables

[Greggscott47]

hi
i have a mail ordering system database which i am creating , i have a link table in i witch is my orders ,

what i want to do is when i enter an order of a certain qty in my order form i want it to update the qantity in stcok in my product table

can any body please help

 

[DaltonVB]

Here is one way to achieve your goal.

Private Sub UpdateInventory_Click()

DoCmd.RunSQL "UPDATE

SET

.StockQuantity = [StockQuantity]-" & _
txtOrderQuantity & " WHERE table.ProductID=" & txtProductID & ";"

End Sub

 

[Greggscott47]

i can not get that one working m8 any other ideas , it says there is a wrong ending to the code

thanks again gregg

 

[DaltonVB]

What is the datatype of the product id? What is the name of your table where the stock quantity is stored? What are the field names that you are using in your form?

 

[Greggscott47]

hi
the qty of stock is stored in the products table field name (QtyinStock) the odrder qty which field name is (qty) is stored in order_details table ,

in the overall order from i have the fields
Order ID
Customer ID
Order_Date
Customer Name
Address
Area
Post Code
Country

and subform
Product_id
qty
product name
Price

thanks afor you help m8

 

[DaltonVB]

How are you linking the subform and the form? Are the parent/child relationships set? Is there a table that contains the orderID, customerID, and the ProductID?

 

[Greggscott47]

i think the froms are linked by order id

and ther is no table with all the fields in , but there is a query with them in

thanks a lot gregg

 

[Greggscott47]

any more help people i am stoill stuck with this one

 

[DaltonVB]

Sorry for a late reply. You should still be able to use the coded sql statement. I would add a button to the subform that runs the update sql statement on the table holding product inventories. What is the name of the table where product inventories is stored? What is the datatype for ProductID? What is the datatype for the field that stores the inventory?

This code works if both are numberic.

DoCmd.RunSQL "UPDATE [table] SET [table].StockQuantity = [StockQuantity]-" & _
              txtOrderQuantity & " WHERE table.ProductID=" & txtProductID & ";"

If I am way off base here let me know and I will stop bothering you.