Update of stock quantity

[andymartin3186]

Hi Everyone,
I'm sure this has been asked before so if needed please just point me to a video or thread that I need to study.
I have a job form where I can search for parts from a parts table, and add them to the job (jobparts table).
In the screenshot the top subform is a search lookup from the parts table and when you click the far right button it inserts them into the bottom subform which is the jobparts. You can then enter in a qty used. That all works great.
The thing I can't get my head round is keeping the qty's correct. If I for example add in qty 1, I want it to deduct this value from the stock qty, and I have had this working. However if I change the qty used to 2, the code I written was obviously too basic, and it then deducted a further 2 from the qty, instead of just a further 1.
Similarly if I change the qty used from 2 to 1, it should readd 1 back into the stock qty.
I can understand that I need to look at the initial value, the new value and then take the difference, but I'm struggling putting this into code.
Could anyone help me or point me in the right direction?

 

[arnelgp]

the normal approach is you don't save the Stock quantity on the table.
you add them up (from the beginning inventory + qty received - qty issued).

in your case, since you are saving it to the Stock Qty field, add this formula to the BeforeUpdate (or maybe AfterUpate event):

[Stock Qty] = [Stock Qty] + [Qty].OldValue - [Qty]

 

[andymartin3186]

the normal approach is you don't save the Stock quantity on the table.
you add them up (from the beginning inventory + qty received - qty issued).

in your case, since you are saving it to the Stock Qty field, add this formula to the BeforeUpdate (or maybe AfterUpate event):

[Stock Qty] = [Stock Qty] + [Qty].OldValue - [Qty]

Amazing!!!
Thank you so much

 

[Mark_]

I'll second Arnel's comment on how this is normally done. Avoids any issue of "people playing games" when trying to calculate remaining inventory.

 

[June7]

Review http://allenbrowne.com/AppInventory.htm

 

[GPGeorge]

And I'll add additional support to not calculating and storing values in tables. It creates a higher risk of data anomolies. (In plain words, wrong data being used in reports.)

Although it is possible to use a calculation as provided, good database design would not avoid it.

We used the Allen Browne method in Northwind Developers Template. You would do well to download and study that.

GIven that there have been reported problems with reinstantiating the NW Developers version form the current template, I keep a version on my downloads site.

https://www.gpcdata.com/downloads/NorthwindDev_Clean_2_4.zip

 

[MarkK]

The HUGE advantage of calculating inventory? If you can calculate it for today, you can also calculate it for next Tuesday.

Say right now you are out of ProductA, but you have a Purchase Order for ProductA scheduled to arrive next Monday. A customer calls to place an order for ProductA to pick up next Tuesday. What information do you need?

• Sometimes you need to know what you have right now.
• Plenty of times you need to know what you will have next Thursday, after the PO arrives on Monday, and after someone already ordered some for Tuesday.