help with flagging

[Kira]

I have a database that keeps track of how much of certain items we have at a given time. I also have a report that lists the items that need to be reordered once they hit the reorder level. Is there a way to have a pop up or alert when the database is opened that tells the user that there are some items that need to be reordered?

 

[georgedwilkinson]

Sure there is. One way is to incorporate Dcount() into your startup form's On Open event. There are lots of other possibilities.

 

[Kira]

alright, so how do I incorporate this? I have a form that opens up and displays the transactions, but how do I get it to work with the report?

 

[Kira]

*bump

I really need elaboration on this please.

 

[georgedwilkinson]

Hmmm...you initially said when the database is opened. I answered that:

One way is to incorporate Dcount() into your startup form's On Open event.

I'm not sure what you're asking for now? Do you need elaboration on the answer?

Private Sub Form_Open(Cancel As Integer)
Dim x As Integer
    x = DCount("MyExpression", "MyQuery", "MyCriteria")
    If x Then
        MsgBox "There are " & x & " items that need to be reordered!!!!!!!"
    End If
End Sub

 

[Kira]

Where do I put the code?

 

[georgedwilkinson]

Do you have a startup form?

your startup form's On Open event

 

[georgedwilkinson]

Sorry, it just hit me what you need.

Open whatever form opens first in your database in design mode. Go to properties, Events, and click on "On Open". Click on the ellipses to the right. Click on "Code Builder" and press OK. Type it in there.

 

[MSAccessRookie]

Another possibility

You might also want to consider not producing a pop up alert, and instead highlighting the amount on hand field in a different color. This is what we use in our system.

Also, you can make it so that double clicking any field that is highlighted could result in a re-order form (pre-filled for the selected item) being displayed, so all the user would have to do is enter the number of items to order.

 

[Kira]

Alright well I have input the code, but when I open it is says there is an error. I presume that this is because I actually need to replace the words that are in quotations. So in the "my query" do I put the name of the report? What about the expression and criteria?

 

[georgedwilkinson]

No, you need to put a valid table name, query name, or SQL into the dcount. Look up DCount in help. Just open your report in design view, go to properties, Data, and copy whatever's in "Record Source". That'll give you a good starting point. If you can't figure it out from there, paste the value in recordsource in your response here.

 

[Kira]

here is the value in the record source:

SELECT [Inventory Stock Levels].* FROM [Inventory Stock Levels] WHERE ((([Inventory Stock Levels].[Current Stock])<nz([Reorder Level])));

 

[georgedwilkinson]

Private Sub Form_Open(Cancel As Integer)
Dim x As Integer
Dim MyQuery as String

    MyQuery = "SELECT [Inventory Stock Levels].* FROM [Inventory Stock Levels] " _
    & " WHERE ((([Inventory Stock Levels].[Current Stock])<nz([Reorder Level])));"

    x = DCount("[Current Stock]", MyQuery)
    If x Then
        MsgBox "There are " & x & " items that need to be reordered!!!!!!!"
        'Or you can take them right to the screen or report.
    End If
End Sub

Remember, this is just one way to do it. MSARookie's advice is good, if you can figure out how to do it. I'm just answering your initial question. There are a lot of things you could do differently.

 

[Kira]

when I try to execute it it says it can't find "my query" and highlights this in yellwo wehn I try to debug it:
x = DCount("[Current Stock]", MyQuery)

 

[georgedwilkinson]

Sorry Kira, I led you astray (totally my fault). I did that off the top of my head and I hardly ever use DCount.

Let's try again:

Private Sub Form_Open(Cancel As Integer)
Dim x As Long

    x = DCount("[Current Stock]", "[Inventory Stock Levels]", "[Current Stock] < nz([Reorder Level])")
    If x Then
        MsgBox "There are " & x & " items that need to be reordered!!!!!!!"
        'Or you can take them right to the screen or report.
    End If
End Sub

Notice that I changed x to a "Long".

 

[Kira]

Works perfectly now. Thank you so much!

 

[georgedwilkinson]

It's cool. Sorry about my earlier goof up. I honestly just wasn't thinking.

Let us know if you need more help.