Expression to check Qty's?

  • Thread starter Thread starter Ray Gibson
  • Start date Start date
R

Ray Gibson

I simply am at a loss as to how to program for this. (I suck at VB)

Here's the scenerio.

2 tables, Inventory & Inventory_MovementLog.
Tbl_Inventory contains Qty-Bin1, Qty-Bin2, Qty-Bin3 etc.

I have a Form for entering the movement FROM a bin: TO a bin:
So the user puts in a sku, enters a qty and picks from Bin# to Bin#

I want to write some code that doesn't allow the movement transaciton to
occurr if you try to take too much Qty from any bin (too much qty means that
it will make it <0)

So, if Qty-Bin1 = 50
and I try to move 75 from BIN1 to BIN2, It will tell me that there is not
enough qty.

Can someone please help!
thanks in advance.
ray
happy new year...
 
You can use the Form's BeforeUpdate event to do this validation:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Qty - Bin1) < Me.txtBoxQuantity.Value Then
Cancel = True
MsgBox "There is insufficient quantity in Bin1 to allow this
transfer to occur."
Me.txtBoxQuantity.Value = Null
End If
End Sub
 
I simply am at a loss as to how to program for this. (I suck at VB)

Here's the scenerio.

2 tables, Inventory & Inventory_MovementLog.
Tbl_Inventory contains Qty-Bin1, Qty-Bin2, Qty-Bin3 etc.
Click to expand...

Then it is improperly designed. If you have a Many (items) to Many
(bins) relationship, a properly normalized structure would have a
table with fields ItemID, BinNo, and Qty - rather than several
*fields* in your table you would have several *records*.
I have a Form for entering the movement FROM a bin: TO a bin:
So the user puts in a sku, enters a qty and picks from Bin# to Bin#

I want to write some code that doesn't allow the movement transaciton to
occurr if you try to take too much Qty from any bin (too much qty means that
it will make it <0)
Click to expand...

Perfectly easy with the normalized structure - you'll just change the
bin number, or subtract a value from the single Qty field.
 
Thanks for the responses. I'll try what Ken suggests above.

1 item has many locations.
 
Thanks for the response Ken.


Ken Snell said:
You can use the Form's BeforeUpdate event to do this validation:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Qty - Bin1) < Me.txtBoxQuantity.Value Then
Cancel = True
MsgBox "There is insufficient quantity in Bin1 to allow this
transfer to occur."
Me.txtBoxQuantity.Value = Null
End If
End Sub
Click to expand...
 
Back
Top