this is my code, it is updating QTY on Hand when you make a new sale or
if you got some product returned. Now as it is updating QTY when qty
textbox lost focus if some one is going through records and whenever
this qty text box will losee focus it will be fired and change the
quantity in the product table. I want it, to be fired only ig user
clicks qty text box or only if new record is inserted.Can you help.
Private Sub qty_Click()
On Error GoTo Err_qty_Click
Dim TotalQty As Integer
TotalQty = 0
Product = Forms![frmSale]![tblSaleDetail]![Product]
TotalQty = DLookup("[QtyOnHand]", "[Products]", "[ProductID]=" &
Product)
If (TotalQty - [qty]) < 1 Then
MsgBox "Inventory Below 0. Quantity Issued is To High!"
Exit Sub
End If
TotalQty = TotalQty + [qty]
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [Products] SET " & _
" [Products].[QtyOnHand] = " & TotalQty & _
" WHERE [Products].[ProductID] =" & [Product]
DoCmd.SetWarnings True
Exit_qty_Click:
Exit Sub
Err_qty_Click:
MsgBox Err.Description
Resume Exit_qty_Click
End Sub
Private Sub qty_LostFocus()
On Error GoTo Err_qty_LostFocus
Dim TotalQty As Integer
TotalQty = 0
Product = Forms![frmSale]![tblSaleDetail]![Product]
TotalQty = DLookup("[QtyOnHand]", "[Products]", "[ProductID]=" &
Product)
If (TotalQty - [qty]) < 1 Then
MsgBox "Inventory Below 0. Quantity Issued is To High!"
Exit Sub
End If
TotalQty = TotalQty - [qty]
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [Products] SET " & _
" [Products].[QtyOnHand] = " & TotalQty & _
" WHERE [Products].[ProductID] =" & [Product]
DoCmd.SetWarnings True
Me.subtotal = Me.qty * Me.SalePrice
Exit_qty_LostFocus:
Exit Sub
Err_qty_LostFocus:
MsgBox Err.Description
Resume Exit_qty_LostFocus
End Sub