Greeting,

I have posted this subject in Microsoft community  and Mr. Ken Sheridan. He
answered my question but there is a problem now. Here is my question:
I have a database of inventory management. I have a table called inventory,
which has the following fields:
ID>>>auto number
Item>>>text
EmaployeeName>>>text
Transactiontype>>text>>cbobox>>Addition, shrinkage , & remove
Quanitity>>number
I have all these fields in a spilt form and when the user wants to add or
deduct or remove, he should input item description employee name, transaction

type, and quantity. What I want to do is when the user is going to perform
deduction transaction; I want access to go over the item quantities and sum
all transaction that is addition and check if the quantity which being
processed as deduction is as follows:
-If the remaining quantity is less than 0, message box appears and do not
allow him to perform the deduction.
-If the remaining quantity is equal to or less than 3, message box appears
and warns him and ask him if he want to continue or not (if the requested
amount is more than the remaining, access will apply the first condition
above)
- If the remaining quantity is more than 3 , the transaction will be
preformed

Ken answer is as follows:

You can call the DSum function in the subform's BeforeUpdate event procedure
to return the stock in hand for the item in question.  This has a Cancel
argument whose return value can be set to True, preventing the record from
being saved.

I'm puzzled why you should only want to sum the additions to stock, however,
as the stock in hand would be the sum of all additions to stock less the sum
of all removals from stock.  So if additions to stock have a Transactiontype
value 'addition' and removals from stock have any other value then, you can
compute the current stock in hand by summing the quantity values for the item
in question multiplied by 1 if the Transactiontype value is 'addition', or by
-1 if not, so the code would be along these lines:

   Dim strMessage As String
   Dim strCriteria As String
   Dim intStockInHand as Integer

   If Me.Transactiontype  <> "addition" Then
       strCriteria = "Item = """ & Me.Item & """"

       intStockInHand = _
           DSum("Quantity * IIf(Transactiontype = ""addition"",1,-1)", _
               "Inventory", strCriteria)

       If intStockInHand - Me.Quantity < 0 Then
           strMessage = "Insufficient " & Item & " stock in hand."
           MsgBox strMessage, vbExclamation, "Invalid Operation"
           Cancel = True
       ElseIf intStockInHand - Me.Quantity < 3 Then
           strMessage = "This transaction will leave " & _
               intStockInHand - Me.Quantity & " of " & Me.Item & _
               " in stock." & vbNewLine & vbNewLine & _
               "Do you wish to continue?"
If MsgBox (strMessage, vbQuestion + vbOKCancel, _
               "Warning") = vbCancel Then
           End If
       End If
   End If

But now when I applied the code, the warning message appears in case of
addition a stock for the same item. Also, it appears in case of shrinkage e.g
1 from e.g. existing stock. Please advise?