how to monitor stock quantity

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:
IDauto number
Itemtext
EmaployeeNametext
TransactiontypetextcboboxAddition, shrinkage , & remove
Quanititynumber
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?

up

Bluemind wrote:
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:
IDauto number
Itemtext
EmaployeeNametext
TransactiontypetextcboboxAddition, shrinkage , & remove
Quanititynumber
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?

--
Message posted via http://www.accessmonster.com