Update Table Using Form and Query

[zeusads]

Hello, I have a little problem, if anyone can help!
I have these 2 tables

Products:
ID
Product
Category
Current
Stock

Stock:
ID Product (Linked to Products Table)
Date
Type
Quantity

I would like to create a form Using the Stock Table that when completed would make a direct update in the "Current Stock" field on Products Table, based on the quantity of the product entered in the Form.

The products table is just a list of products from a particular location. In the Stock table we register inputs and outputs of these products, so when you take or enter a quantity of the product in the Stock table, it is necessary that the Current Stock value is updated in the products table.

If anyone can help on this I will be very grateful.
Thanks in advance!

This is really a simple stock table, we don't need much. The products we use are in only 2 categories "FOOD" or "Cleanliness and Hygiene" so there is no need for another table for that and this wont change.
The Type in the stock table is just to inform the types of moviments: "Purchase, Donate or Output". So we can know what kind of movement happend on the stock. Like in the stock tabler I will say that I received 10 packets of rice by donation, Or that we bought 10 packets of rice. When someone inputs that using the Stock form What I want is that the Rice register on Products table updates in the "Current Stock" Field. But I dont know how to do that, the actual mechanic to make an information Inserted on a form to automactly update another table other than the one on the form.

[jforbes]

People will tell you that you'll want to create this as a calculated field that is calculated when needed and not a static field stored in the database. That is pretty much true except in the case where there is too much data to effectively use a calculation.

So, probably your best option is to create a query based on your Products table and add the calculated field to it. You should be able to use the Query in place of your table in most cases and have your calculated field available. This means, you can change all your Forms that are based on Products table to the new Query and the Forms will continue to work.

Remove the Stock field from Products and then build a Query like the following and use it instead of the Products Table

SELECT 
  Products.ID
, Products.Product
, Parts.Category
, Parts.Current
, DSum("Quantity","Stock","ID=" & [ID]) AS Stock
FROM Products

There are a lot of other ways to go about doing this, but the above method is simple, and I got the impression that you were attempting to keep things simple.

Another resource that you may want to look at is Allen Browne's article: Inventory Control: Quantity on Hand

Lastly, there are some reserved words in Access that if you use them, they can cause you trouble as Access expects those words to have a specific meaning. Date is one of these and it can throw Access off when it attempts to decipher a SQL string. I would recommend renaming that field to TransDate or something similar. You may also want to change Type.