Update a specific field in a specific record in another table (1 Viewer)

[Ole]

Hi
I have a table ("Vare") which contains products and stock.
When I make an order in my order module ("Lagersystem") i would like to update the field "Antalpålager" in the table "Vare".
The information comes from "Form_Lagersystem.Antal" which I want to use to subtract from "Vare.Antalpålager".

The problem is that I can't get it to find the specific record I want to adjust/update. I always update ALL records in the table ("Vare").

Here is some pictures since I can't upload the full database (I too big):

It says that I'm about to update 81 records (the full table)

It doesn't need to be a Query, but it needs to be run when I leave the field "Lagersystem.Antal".

Any ideas?

 

[Gasman]

You would need to use a where clause, BUT it is not recommended to amend it like that. You should just calculate it when needed.

 

[The_Doc_Man]

Inventory can be done that way but risks desynchronization with reality.

Most modern inventories take the approach that your quantity-on-hand of some item is just a sum of all transactions that would add or remove one or more items in that inventory. You buy a bunch of the items from your supplier? Supplier-Add 100 items. Your customer bought some items? Customer-Purchase 6 items. And what you do is in the transaction table, you have the actual amount as a positive integer, but either by query or by some other method, you change purchases and loss-shrinkages to negative integers. Then, quantity on hand is the sum of all the adds and removes. Of course there is more to it than that, but if you search this forum for "Inventory" you will see literally hundreds of articles.

 

[Ole]

Well the point is not inventory. It's just one of the functions I need.
I can refrase it like this.

I need to update a specific field in a specific record in a specific Table from another Form.
Is that possible?

 

[Pat Hartman]

Sort of. Forms don't store data. Tables store data so you wouldn't update formB from formA, you would update, using an update query, tableB from formA

In order to update ONE specific record, you MUST know the value of its primary key or have some other piece of unique information that can identify the row you want to update.

 

[Ole]

Sort of. Forms don't store data. Tables store data so you wouldn't update formB from formA, you would update, using an update query, tableB from formA

In order to update ONE specific record, you MUST know the value of its primary key or have some other piece of unique information that can identify the row you want to update.

I know the primary key.
But I'm aware that forms don't store data. It was just to make it clear the I'm handling it from a Form (AfterUpdate)

 

[Pat Hartman]

Use the QBE to create the append query. Add criteria that references a control on your form

Where SomeField = Forms!yourform!SomeField