VB to prompt a message when a value reach a certain limit

[cwwaicw311]

Hi,



I need help on a VB code whereby when a value after all the transaction reach a limit a message will be prompt. I noe i could use IF ELSE statement for this function but i not sure how.

For example,

I put 100 as the limit on the field balance. So if the field Balance after all the transaction reach 100 or below. Then a message could be prompt out.

[maximus]

I think you have made another similar post today were you mentioned that you wanted to compare the values of same fields on two different forms.

My understanding of your problem:

1) You have a database which records Product inflow and out flow.
2) You have a re-ordering level for various products.
3) you want to receive a prompt to order when the stock level reaches a re-ordering level.

suggestion

Tables:

Product:{Product_id(Autonumber PK), Product_Name (Text),Product_re_ordering_level(Number)}
Product_inflow:{Entry_id(Autonumber PK), Product_id(Lookup Feild linked to Produt_id of Product_table),Quantity(Number)}
Product_Outflow:{Entry_id(Autonumber PK),Product_id(Lookup Feild linked to Produt_id of Product_table),Quantity(Number)}

Now where would you like to check the re-ordering level. I have a Product_outflow Form and in that I have a CommandButton that Save record say CommandButton1.In its Click Event I would add the following code:




Private Sub Command1_Click()
Dim intStockInflow as integer
Dim intStockOutflow as integer
Dim intRe_ordering_level as integer
Dim intStock as Integer

intStockInflow=iif(isNull(DCount("[Quantity]","Product_inflow","[Product_id]="&Forms!Product_Outflow!Product_id)),0,DCount( "[Quantity]","Product_inflow","[Product_id]="&Forms!Product_Outflow!Product_id))

intStockOutflow=iif(isNull(DCount("[Quantity]","Product_Outflow","[Product_id]="&Forms!Product_Outflow!Product_id)),0,DCount( "[Quantity]","Product_Outflow","[Product_id]="&Forms!Product_Outflow!Product_id))


intRe_ordering_level=Dlookup("[Product_re_ordering_level]","Product","Product_id"="&Forms!Product_Outflow!P roduct_id)

intStock=intStockInflow-intStockOutflow


'Now here you have to put the if block

if intStock<=int_Re_ordering_level then

msgbox "Re-ordering level has been reached"
end if
end sub

This will ensure that the user will receive a prompt to reorder every time stock is equal to reordering level. As good practice you can add a danger stock level. When that stock level is reached no more issue can be made. Please feel free to ask if you need any more help.

Mark the thread solved if this solves your problem.

[cwwaicw311]

thank for the guide... But is there a shorter way whereby i can check the balance on the same form. Coz i have a form where i do all my transaction together including the balance. So maybe i could create a IF ELSE statement where the balance equal or lower then the limit then a message would be prompt out. Is this possible...??

[maximus]

Sure suppose you have you balance Feild (Stock) and reordering_level Field and the Quantity_issued on the same form then this code will help.

same save button is used to check criteria:
if Me.Balance+Me.Quantity_issued<=Me.reordering_level then
'What you want to do
else
'What you want to do
end if

remember this will work if you have all the feilds in the same form.

[cwwaicw311]

thank for the help... u solved my problem once again...

[maximus]

No problem I was happy to do so!!!