Solved Preventing user from updating quantities from Sales details (1 Viewer)

[RickHunter84]

Hello friends,

Happy new year 2021! I hope this year is much better than last one, I don't think it will be really hard to make it better

Here is my finding while troubleshooting/testing. this is Inventory management DB - MS ACCESS 2016

I have tblSalesOder connected to tblSalesOrderdetails, and tblSalesOrderdetails related to tblInventoryOnHand (all relationships work), my question is regarding handling the user interaction after inputting the quantity he wants to sale. For example:

I have product XYZ qty 100ea : The product is typed in, all the information is retrieve, then the user sales qty 25ea out of this product. After the line is added the inventory is reduced by 25ea (no problem).

The problem I'm having is that what if the user enters the quantity and realizes that he missed typed the number (25ea) and the correct number was 20ea. Since the quantity was already entered and reduced from tblInventoryOnHand, if the user changes the quantity again, the 5ea difference wont go back to inventory, since the inventory was already removed. Any suggestions on how to handle this situation? opinions?


Thank you in advance,
Best regards,
Rick

 

[Gasman]

OnHand should be calculated, not stored, for this very reason.?

 

[RickHunter84]

OnHand should be calculated, not stored, for this very reason.?

Good point. fair enough, thank you! ill start working on the solution based on that opinion.

Rick

 

[Gasman]

Good point. fair enough, thank you! ill start working on the solution based on that opinion.

Rick

Wait for some experts to chip in, but I think they will concur?

 

[gemma-the-husky]

I would certainly agree with the point about not storing values that can be easily calculated, although sometimes you may decide to do so for a particular reason. Foe instance you will probably store the total value of an invoice, even though it could be recalculated from the invoice lines.

In general, maybe another issue is not committing the entry until it's completed. If there is any possibility of needing to cancel an entry it may be better to try to find a way of storing the new action in a temporary structure until the user is ready to commit the whole process. If the user enters rows, and then starts to delete or amend them, it may cause problems if other users relied on the now modified/deleted entries. If you are just working on temporary data, you can make whatever changes you want, until you decide to either cancel the whole thing, or accept the final version.

 

[CJ_London]

I concur - calculated values should be calculated when required, not stored.

depends how your tables are structured but typically you have a minimum of two tables - products and transactions. Products might link back to suppliers/manufacturers, transaction might link back to other tables providing more information about the transaction such as location, on reserve/on hold/customer PO/company PO etc, but the core transaction values would be in the transaction table.

incoming stock would be stored as a positive number, outgoing stock negative. Or you might store all transactions as positive and have a transaction type field which can be used to indicate if the value should be multiplied by -1. Current stock is then simply the sum of transactions to date. Some transactions are indicators such as reserved so depending on requirement current stock might include it (as 'sold') so it can't be sold again, but is still here for stocktake purposes since it isn't actually sold so still a company asset.

In my stock control system I have a transaction type 'stock take'. When this is entered by the user (who enters the actual stock) a second stock record is created being the adjustment. The benefit of this is when calculating current stock, the system looks for the latest stocktake value and only sums the transactions from there forward. If one isn't found then it sums from the first record. Otherwise 3 years down the line, there may be a lot of transactions to sum.

 

[Pat Hartman]

To prevent this data anomoly, you should lock the quantity field in the AfterUpdate event of the form, and in the Current event of the form, unlock it for Me.NewRecord = True and lock it for records that are not new.
Then the user can add another adjustment transaction either + or - to fix the error. This gives you an audit trail. Don't forget to keep transaction types. The second one would be an adjustment and adjustments can be + or -. You can fix most other transactions aa either + or - if you want to.

If you don't want to do this, I agree that it is best to simply not store the current balance. In that case, you could allow quantity to be changed.

The code to reconcile this if you want to both store a calculated result and allow changes to quantity requires you to do two calculations in the FORM's BeforeUpdate event. Do not do the update to the CurrentQty in any other event. Control level events can run multiple times and you'll neve get the right answer. You need to "unapply" the Me.txtQuantity.OldValue and then apply Me.txtQuantity so, the calculation is something like:

Me.txtCurrentQty = Me.txtCurrentQty - Me.txtQuantity.OldValue + Me.txtQuantity

That expression works if the quantity fields have natural signs. If you go with assumed signs, then the expression is more complex. But keep in mind that I am NOT RECOMMENDING this solution.

 

[RickHunter84]

Thank you everyone for your points of view. I went for the calculated field option, I think it simplifies the handling of the problem.

Have a nice rest of weekend!

Rick

 

[Pat Hartman]

I went for the calculated field option, I think it simplifies the handling of the problem.

The experts have all disagreed. Do what you want.