Adding values in a table

[aman909]

Hi,

Im trying to create a stock control system. But the stock is in two different places. What this means is the table must contain stock values from each place and then a overall total must be given. But im not sure how to be able to add the two up and display it in the same table.

Can anyone help me please?

[kepston]

It is not recommended to store a calculated value within the same record as the fields used in the calculation (unless a different value from the calculation is not wrong).
It is better to store the actual values and use a Query to display calculations.

Part, Location, Qty
M6 Screw, A1, 2000
M8 Screw, A2, 1000
M6 Screw, A3, 500

select Part, Sum(Qty) AS Total
from tblStock
group by Part;

would give

Part, Total
M6 Screw, 2500
M8 Screw, 1000

[aman909]

That is a good way of doing but im not sure if it will work for me. What i have is:

Colour Stock1 Stock2 Total Stock
Red 50 100
Blue 100 200

What i need is for the total stock be added from stock1 and stock2. Do you know a way to be able to this.

[kepston]

That is a good way of doing but im not sure if it will work for me. What i have is:

Colour Stock1 Stock2 Total Stock
Red 50 100
Blue 100 200

What i need is for the total stock be added from stock1 and stock2. Do you know a way to be able to this.

If this is a table, then I would recommend you create a query to display Total Stock.

select Colour, Stock1, Stock2, Stock1+Stock2 as [Total Stock]
from tblStock;

Or if you want to display it in a form, create an unbound text box with Data =Stock1+Stock2

If you must store [Total Stock] as a field, you should create a form with an AfterUpdate event on each of Stock1 and Stock2 to modify [Total Stock] when either changes. But be aware that if either update fails to occur, you could end up with a value in [Total Stock] that is not the sum of Stock1 and Stock2. Equally, if any of the three values are changed when viewing the table, your rules will not be enforced, again giving you inaccurate stock levels.

[aman909]

I need to be able to store that total stock in a field. how would i create a update form for that

[kepston]

I need to be able to store that total stock in a field. how would i create a update form for that

In the properties window of your text boxes, on the form, is a tab marked Event.
in AfterUpdate choose [Event Procedure] and click on the builder button [...].
A Visual Basic window will open showing something like

Private Sub Stock1_AfterUpdate()
 
End Sub

In the gap between Private Sub ... and End Sub you must write your code to manipulate the fields (in the example above that would be line 2)
For each of Stock1 and Stock2 you will need something like

Me.Total_stock = Me.Stock1 + Me.Stock2

I also recommend that on your form you Lock the field [Total Stock] so that it can't be changed by the user.