Need Help with this query (1 Viewer)

[MajP]

Check with 2222 and see and see if this covers the 2 Checks?

Still there are some pieces missing, but I think we will be able to do it on our own. For example we need some validation to prevent the quantity in Stock out form. As you see bellow, StockMovementPK 24 has a quantity of 100. But Stock out for it is limitless. We also need to add some code to prevent moving out stock from 25 & 26 until all stock of 24 is used.

 

[KitaYama]

@MajP Once again thanks. Millions of thanks.

Unfortunately the problem still persists. I will try to change your code based on our needs.
But for the sake of completeness, I'll try to explain where the problem is.

Still there are some pieces missing, but I think we will be able to do it on our own. For example we need some validation to prevent the quantity in Stock out form. As you see bellow, StockMovementPK 24 has a quantity of 100. But Stock out for it is limitless. We also need to add some code to prevent moving out stock from 25 & 26 until all stock of 24 is used.

For better understanding I separate this comment into 2 rules.

Rule 1:
User should not be able to send out a product more than what is possible under the same StockMovementPK.
This rule has been employed partially. The forms are OK until the balance is 0. But once the balance is 0, then you're locked out of Editing a record.
Example:
Part 2222. The first StockMovementPK is 35. This MovementPK has +1234, -100, -12, -1122. Balance is 0. Perfect. While entering data, if the entered quantity is more than what is possible, We receive a message and the quantity is corrected. Better than what we expected.
But now try to change -1122 to -1000.
User's misunderstanding, typo or whatever. The record is not correct and needs to be edited. The user notices the fault value (after saving the record) and now he wants to edit it to the correct value: -1000. But it's not possible to edit it.
It seems that the validation is missing the point that more than 1122 is locked, but less than that can be possible.


Rule 2:
While the balance of a StockMovementPK is not 0, later PKs should be locked. What comes in, goes out first. When the balance of that PK reaches 0, the next immediate PK is open for use.
This is exactly a dream come true. I couldn't expect it better.


Once again, I don't want to put you under pressure and ask you to do it all. I will go through your queries and validations and correct it.

I really can not find the suitable words to thank you and explain how much you helped us to solve a problem we've been fighting with for a long time.
The only words I can find with my poor English is Thank you.

 

[KitaYama]

@MajP
I changed Form's validation rule to the fallowing and now it's perfect.
No, It's more than perfect.

    Dim newval As Long
    Dim CurrentStock As Long
  
    newval = Me.Quantity
    If newval > 0 Then newval = -1 * newval
        CurrentStock = mdlUtilities.ExistingStock(Me.StockMovementInID_FK) + newval
        If CurrentStock < 0 Then
            If Quantity.OldValue < newval Then Exit Sub
            MsgBox "Cannot remove " & Me.Quantity & ". Remaining Stock balance = " & ExistingStock(Me.StockMovementInID_FK)
            Cancel = True
            If MsgBox("Do you want to remove max amount?", vbYesNo, "Max Amount?") = vbYes Then
            Me.Quantity = -1 * ExistingStock(Me.StockMovementInID_FK)
        End If
    End If

Million thanks from the edge of the world and from a bunch of people whom you saved.
Cheers