Upating table from anothr Form

[Freddie81]

Good day Guys,

In my database I have a table called ProductsT and in this table, there is a field called StockQty. The idea is to have a field that holds the total stock available of that item.



I then have a form called StockDataEntryF that is used to capture the buying new stock.

When I hit Submit button the form I want the Qty that is entered into it to be added to the field in ProductsT called StockQty.

How would I do this?

[orange]

Can you post a jpg of your tables and relationships---expand tables to show all fields?
Here is an often referenced article on Stock/Inventory by Allen Browne.

[John_G]

When I hit Submit button the form I want the Qty that is entered into it to be added to the field in ProductsT called StockQty.

A simple SQL statement in VBA will do that for you. In the code behind you Submit button you could have something like this:

Dim SQL as string
SQL = "Update ProductsT set StockQty = StockQty + " & me!Qty & " where ProductID = " & Me!ProductID
Currentdb.Execute SQL,dbfailonerror

The where clause " where ProductID = " & Me!ProductID is necessary to identify the one record in table ProductsT that you want to update with a new quantity.

This assumes that the field ProductID in the table is unique for each record; it might be the Primary Key of the table.
It also assumes that you have a field on the StockDataEntryF form that holds the ProductID.

Replace the names in italics with the actual names of the fields in the table and on your StockDataEntryF form.