Updating two fields in two different tables from one fold in a form (1 Viewer)

[FabioW]

Hi guys. I am sure it is easy and someone allerwdy asked for it, but I just can't Google solution for it. I have a table and form where I registere usage of some chemicals. What I want is to update a quantity I have left in stock (another table) when registrering usage.
I was trying to use a VBA code to update it but it is just not working. Can you guys help me? I have put the code on a form ChemRegister in "events on update" of the form. And the code I was using was:
PRIVATE SUB Form_AfterUpdate()
Set [TblChemType]![ChemQuantity] = [TblChemType]![ChemQuantity] + [TblChemRegister]![ChemUsage]
End Sub

 

[Cronk]

If your form ChemRegister is bound to your table TblChemType and ChemQuantity and ChemUsage are the names of text boxes on your form which are bound to the respective fields, then you could put as your code

ChemRegister!ChemQuantity=ChemRegister!ChemQuantity + ChemRegister!ChemUsage

or more simple

Me.ChemQuantity=Me.ChemQuantity + me.ChemUsage

 

[The_Doc_Man]

Note, however, that this is not the normal way to track "quantity in stock." Usually, you have some kind of "transaction" - a record of adding or removing stock on your shelves. Doing what you are doing doesn't give you the ability to provide a report of to where stock item "XYZ" disappeared. If you have that need, then you need to look more into things like normalization. But if you are doing this for a VERY limited number of items that can NEVER increase with respect to how many different items you track, then Cronk's advice might work.

 

[FabioW]

Maybe I didn't make may self clear. I have a table and a form to register what is happening witch chemicals - TblChemRegister. And there are informational like: Date, Employee (lookup from another table), Chemical (lookup from TblChemType) quantity that was used (ChemUsage), and a comment what was it used for.
The goal is that when usage is registered (John took 5l of paint), then field ChemQuantity in table TblChemType is updated.
I would like to do it by some simple VBA code. The one that Cronk wrote does not work. It may be because I don't know how to bound two tables to one form... Can you guys help me please.

 

[Acropolis]

What do you do when you receive new supplies of chemicals?

If you added them to the tblChemRegister table as well as the usage, then you wouldn’t need to update another table with the quantity available, as you could just do a sum of the quantity field in the register table and it will give you the information you need.

 

[FabioW]

I Have fixed it, I simply added the field ChemQuantity from TblChemType to the TblChemRegister form and the simple:
Me.ChemQuantity=Me.ChemQuantity + Me.ChemUsage
worked just fine

Cheers