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.

[Homegrownandy]

In my opinion (hopefully someone else will have more input as I could be wrong.);

you shouldnt keep "stock" on the tables. you should record everything coming in and then everything going out.

stock levels can be displayed on a form via a calculation of these two things.

[Homegrownandy]

every time an event happens it should be on a new record. Like things coming in or out. these can be on the same table that is up to you. But I would advice a different procedure/forms for dealing with input and output. Also, when categorising, to minimise the data you store.. you can have a category table where by your "hygene" is Category_ID 1 and "food" is category_ID 2 for example.

So you still have all the data you need but less information is stored. It probably won t make much difference in your database but it does make it more scaleable.

[zeusads]

OK, It make sense to have 2 tables for coming in or out. But the big problem is how do I Update the Current Stock on Products Table? Or that should't even happen?

[orange]

zeusads,

A few points.

Cross posted at http://www.utteraccess.com/forum/ind...ic=2041934&hl=

When you cross-post----post same issue on multiple forums --- please include a reference/link to the other post(s).

Here is why.

Re Stock control:
See this article by Allen Browne on stock taking for more info.

Have you tried searching for Stock Control or Inventory Control and reviewing some of the resultant posts/threads? Lots of work has been done and may be useful to you.

Here is a free video on Stock Control that may give you more insight on design.


Good luck.

[Homegrownandy]

I cant answer better than orange. but I was saying not to store stock levels at all. Use a calculation of incoming/outgoing to get levels of stock.