how to monitor stock quantity

[Jon]

Greeting,
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
Is there any solution? Thank you in advance

 

[Jon]

Thank you, but when I add new stock quantity , the same msg appears . also,
if I shrinkage e.g 1 out of 10 the same msg appears, Please advise?

 

[Jon]

Hi,

Thank you for response, you are right , but I think there is a
misunderstand. What I want to do is case of shrinkage, access should count
all the stock of the initial item which being processed. For example:

Initial item is pen

Transaction type : Shrinkage

Quantity: 15 pen

*Total stock of pen is: 16 pens


Pervious transaction: Addition
1-
Item is pen
Transaction type : addition
Quantity: 5

2-

Item is pen
Transaction type : addition
Quantity: 6

3-
Item is pen
Transaction type : addition
Quantity: 5



I want access to sum quantity of all transaction type Addition , which is 16

After that , compare it with transaction Shrinkage which being processed and
if the quantity is less than the sum quantity , a msg box will appear and
warn the user.

 

[Jon]

hi again,

I have developed this code and it does not work (no message executed)

Dim strMessage As String
Dim intStockInHand As Variant
intStockInHand = _
DSum("[Actual Quantity]", "[Inventory Transactions Extended]",
"[Inventory.ID] = " & [ID])
If intStockInHand < 0 Then
strMessage = "Insufficient " & Me.Item & " stock in hand."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
ElseIf intStockInHand <= 5 Then
strMessage = "This transaction will leave " & _
intStockInHand & " of " & Me.Item & _
" in stock." & vbNewLine & vbNewLine & _
"Do you wish to continue?"
If MsgBox(strMessage, vbQuestion + vbOKCancel, _
"Warning") = vbCancel Then
End If
End If


The dsum line returned a value could be – or + of item stock (in hand)

Please note that the argument is based on query