dbUpdate and field value update

Adam, this type of updating another table can be tricky and involved because there are various situations you have to account for when processing data in the respective update forms, particularly when adjusting quantities and products simultaneously. This is not a criticism, but I believe your approach will not work. I put together the coding necessary to update stock levels. It manages the integrity of the stock levels so input mistakes will not cause stock levels to go below zero. Test out various situations to make sure I covered everything. I used the project from your first post, not your most recent one.

Hi EHW,

Excellent logic and coding as usual !
Thank you very much...................
Truly appreciated............................

Maintaining proper stock levels in coding seems to be a tricky business... at least for me. I'm in the process of reading and digesting your code at present. I see that I have to get a proper grasp of using variables.

Good spotting and thank you very much for the fix too.
I'll try to implement credit and debit invoices to achieve further stock level integrity, if I can.

In order to make code reading easy and minimize mistakes I try to use idiot proof (that's me) naming convention. i.e. Instead of  'Adj_Qty' I use 'AdjQtyVAR'  This way I don't mix it with events (as _ is used in them by default) and I can see them their own variables, buttons and field names. It's nothing to do with your style, it's just shortcoming on my side.
During renaming variables, changing  var Init_Qty to InitialQtyVAR returns "Undeclared identifier" around //...(frmSaleInv.InitialQtyVAR.Text);//  After changing it to back Init_Qty it works. All other var renaming worked fine. I tried to find if anywhere else it's referenced beside script tab page but couldn't find. I replaced them using find and replace.

Thanks a lot EHW............

Renamed variables except Qty as I couldn't recognize clearly which ones refers to var and which ones to field name. No worries, it's OK.

I wanted to add another condition to sale inv, where sale qty value entered bigger than products' current stock qty value, when adding a new sale inv with the following but didn't work. Getting minus stock qty on products.

If (frmSaleInv.cbSaleInvProduct.sqlValue = frmSaleInv.SaleInvInitProd.Text) and (frmSaleInv.dbAction = 'NewRecord') then
     Begin
        If CurrQtyVAR - Qty <= 0 then
          Begin
            MessageBox('This sale qty value will cause the stock level to go below zero.','Error',MB_OK+MB_ICONERROR);
            frmSaleInv.edSaleInvQty.SetFocus;
            Cancel := True;
            Exit;
          End;
     End;

Hi EHW,

Managing stock levels is very tricky indeed. I have tried to add refunds to "Stock Level" sample project that you kindly coded but failed miserably.

I have added DBCR table and relevant fields to sale and purchase invoices without all the checks as priority was getting quantities working when product/s returned. There are few things needs to be taken into account:
-- Not all refunds may be full, there will be partial refunds.
-- Purchase refunds subs qty from product current stock level.
-- Sale refunds adds back qty to product current stock level but not all sale refund qty added back to current stock level;
----- Customer may return faulty product which are not added back to current stock level.
----- Customer may ask money back due to not receiving products  which are not added back to current stock level.
For these kind of situations, I have added new field to sale inv where user can enter qty to be added back to current stock level. Therefore refund qty and qty added back to current stock level may be two different values.
edit: Partial refunds would apply to purchase invoice refunds too. I missed adding extra field on form purch inv. There again, additional fields on sale an purch invs may not be needed.

There seems to be issue with "Add New Product" button on both sale and purchase invs when clicked on Add sale/purch invs buttons on Form1.
Issue seems to be caused by checks on Save buttons on both sale/purch inv forms. I have commented out product selected from combo check as I have checked not null on tables. I may be totally wrong here.

I know you have limited time and to date you have been very kind on helping. Please don't feel any pressure or whatsoever. If and when you have a free time your help on this matter of completing stock levels project with refunds would be greatly appreciated as always.

I have attached sample project file.