Would Like to Create an "Alert" Box for Stock in a Form

[Guest]

I'm after a little help, please!

I have set up a simple database with details of products we use at work.
These products need to be re-ordered regularly.

What I want to be able to do is this:

Each product has the fields "Items in Stock"; and "Minimum Requirement".
When the product's Stock Level falls below its "Minimum Requirement", I would
like a box in the form to say "NEEDS RE-ORDERING" or some similar type of
alert.

In Excel, I would do this with an IF statement, but I don't know if I can do
this in Access.

Many thanks as always for your help

 

[Guest]

Yes, you can do this. There are a couple of ways. The easiest would be to
create a report that would provide a list of products at or below their
reorder point.
What you want to do, however, would need to be done at the point orders are
placed and stock allocated. If you have a form from which the user places
orders, I would suggest that in the After Update event of the form, you check
the on hand status and present a message box that alerts the user. For
staters, it would be beneficial to have all the information you need in a
text box on the form. You can make its Visible and Tab Stop properties False
if it is not something you want the users to see. What you don't say is
whether the On Order quantity has any bearing on this. In many cases, when
considering whether a product is at its order point, what has already been
ordered is considered. I will not address that since you did not mention it.
For this, we will assume you have all the data elements you need on your
form:

If Me.txtQtyOnHand <= Me.txtReorderPoint Then
Msgbox "Product Needs Reoder"
End If