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.

[ajetrumpet]

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.

run an sql operation on it. It's as simple as that. here is an example of running such a statement using a form control called "qty" that has a value of 1:

currentdb.execute "UPDATE table SET " & _
"table.qty = table.qty - " & me.qty & " WHERE " & _
"[id] = " & me.productID

HTH!

[bakkouz]

ajetrumpet, Thanks for your reply.
I modified the code like this:

Private Sub Command51_Click()
CurrentDb.Execute "UPDATE table SET " & _
"SpareParts.Quantity = SpareParts.Quantity - " & Me.Quantity & " WHERE " & _
"[Item] = " & Me.item_ID
End Sub


but I keep getting a compile error on Me.Quantity.
What am i doing wrong?

[ajetrumpet]

maybe try to use a variable instead:

Private Sub Command51_Click()

dim var as long

var = dlookup("quantity", "spareparts", "[item] = " & me.item_id) - me.quantity

CurrentDb.Execute "UPDATE table SET " & _
"SpareParts.Quantity = " & var & " WHERE " & _
"[Item] = " & Me.item_ID
End Sub