Expression to check Qty's?

[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...

 

[Ken Snell]

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

 

[John Vinson]

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.

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)

Perfectly easy with the normalized structure - you'll just change the
bin number, or subtract a value from the single Qty field.

 

[Ray Gibson]

Thanks for the responses. I'll try what Ken suggests above.

1 item has many locations.

 

[Ray Gibson]

Thanks for the response Ken.

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