Update field value in a table based on the sum (or abstraction) value of other table

[bakkouz]

Hi there,
I'm still a beginner so please take it easy on me

Ok, So, I have two tables, One is called SpareParts, and the other is called Spare_Parts_Operations.

Table "SpareParts" basically stores Spare parts names and quantities, Such as:

Item Quantity EA
CD-Drive 1 Unit
Cable 300 m
Keyboard 5 Unit

And so on.

In table "Spare_Parts_Operations" the user (Using a Data Entry Form) stores information about spare parts he installed on machines, Such as:

Item Serial Number Quantity Installed By Installed To
Keyboard AB123D 1 John Mick
HDD C2334G 1 Sarah Jim
Mouse 67JT5 1 Sarah Peter

and so on.
Usually the Quantity field in "Spare_Parts_Operations" is always 1, since he only installes one item at a time with a unique serial number.

Now, what I want to do is: after the user clicks the "Add" button on the "Spare_Parts_Operations" form, I want the according quantity of the Item in "SpareParts" table to get decreased by 1.

ie: if i had 10 Keybaords in "SpareParts" and the user/tech installes a new keyboard on a machine, and uses the form "Spare_Parts_Operations" to records this operations, the Quantity of keyboards in "SpareParts" should now become 9.

I can of course make him update the Quantity field in "SpareParts" table manually, But I want it done automatically to avoid users from messing with the "SpareParts" Table, I've been trying to figure out how to do, even searched the web for answers, it but I honestly came up with nothing.

help is very much appreciated,
Thank you.

 

[AliyuKatsina]

Hi,

Create a bcommand button on your Spare_Parts_Operations form, and add the following code.

Dim Rst as Recordset, mySQL as string
.
.
mySQL = "SELECT SpareParts.* FROM SpareParts WHERE (((SpareParts.ITEM)= "
mySQL = mySQL & "name of the item to add" & " ));"

set Rst = currentdb.openrecordset(mySQL)

' You can add code here to verify that Rst contains valid record.

With Rst
!Quantity = !Quantity - 1
.Update
.End with

Pls note that Rst must contain only one record for this code to work. therefore Item must have a unique name, with no duplicate records.

Hope this will work.

 

[MarkK]

Hey there! Welcome to the forum.
The sort of industry standard here would be to never store an actual amount for a quantity in SpareParts. Rather you'd sum the records where stock is added to inventory and sum the records where stock is consumed from inventory, sum any reconciliations adjustments and that's your stock quantity.
The thinking here is that the database stores data in as raw a forms as possible, and never stores any of the same data twice. So the same way that in an accounting system, your bank balance is only ever the result of a calculation of your deposits and withdrawals, so too your 'stock on hand' is always a calculated number like that.
See how that eliminates any need to update your stock balance? Your stock balance is only ever a calculation so as soon as a record consuming stock is added the next time you query the stock balance that record is counted and stock cosumed there is decremented from the total.