Changing a Field in one table according to the other

[Guest]

Hey, I have a table called register which contains the following fields, [barcode] , [desciption], [price] , [quantity]
I add a barcode from another table called stock. The two tables have identicall barcode, description except from quantity but whenever i put a barcode in the register table along with its quantity and i save the record, i want the quantity of the same barcode to reduce the quantity in the stock table. How may i achieve this?

 

[Dirk Goldgar]

Hey, I have a table called register which contains the following
fields, [barcode] , [desciption], [price] , [quantity]
I add a barcode from another table called stock. The two tables have
identicall barcode, description except from quantity but whenever i
put a barcode in the register table along with its quantity and i
save the record, i want the quantity of the same barcode to reduce
the quantity in the stock table. How may i achieve this?

I would not handle inventory processing this way, as it's quite prone to
error. However, assuming that records are added to table Register only
by way of a form, you could do what you ask, in its simplest form, with
code in that form's AfterInsert event:

Private Sub Form_AfterInsert()

CurrentDb.Execute _
"UPDATE Stock " & _
"SET Quantity = Quantity - " & Me.Quantity & _
" WHERE BarCode = '" & Me.BarCode & "'", _
dbFailOnError

End Sub

The above code assumes that BarCode is a text field, and that it won't
contain the single-quote (') character. If BarCode is a numeric field,
the WHERE-clause line should be

" WHERE BarCode = " & Me.BarCode, _

Note that this code contains no code to check for a reduction of
Stock.Quantity below zero.

 

[Guest]

Im getting a syntax error, what can i do to fix it?

 

[Dirk Goldgar]

Im getting a syntax error, what can i do to fix it?

That would be impossible to say unless you post the code you're using,
the error message you're getting, and (ideally) indicate exactly which
line of code is giving you the error.