flag when stock level drops below minimum

[KeMoAforika]

I have a product form based on a query. The query has the current stock level
and the minimum stock level.
I would like a flag or notification to show on the form when the stock level
drops below the minimum level.
Wayne

 

[Damon Heron]

In the Current Event of the form, test the stock level -

If Nz(Me.[your field for UnitsOnHand, 0) < Me.[your field for ReorderLevel]
Then
' do something - maybe change color of text, or add a msgbox.....
Me.ReorderLevel.ForeColor = vbRed
Me.ReorderLevel.FontBold = True
Else
Me.ReorderLevel.ForeColor = vbBlack
End If

Your could also have a label with visible property changed to true or false
based on the above test.

Damon

 

[Arvin Meyer [MVP]]

You'll need to use a form event, probably the AfterUpdate event of textbox
with the quantity ordered to check the query. You can use a recordset to
return the quantity:

If rst!Qty <= rst!MinStockLevel Then
MsgBox "Time to reorder", vbOKOnly
End If

Of course you'll need to build the recordset. If you need help with that,
post back.

 

[NG]

Hi,

when using Access 2007, the easiest way is to use the conditional formatting

 

[KeMoAforika]

Thanks - I used the following:
Private Sub Form_Current()

If Nz(Me.[StockOnHand,0] < Me.[StockLeveMinimum]) Then
MsgBox "RE-ORDER STOCK."

Me.[StockLeveMinimum].ForeColor = vbRed
Me.[StockLeveMinimum].FontBold = True
Else
Me.[StockLeveMinimum].ForeColor = vbBlack
End If
End Sub

I get an error message that says:
run time error '2465'
CSA can't find the field (I) referred to in your expression.

When I debug - the following is highlighted:
If Nz(Me.[StockOnHand,0] < Me.[StockLeveMinimum]) Then

Any ideas?

In the Current Event of the form, test the stock level -

If Nz(Me.[your field for UnitsOnHand, 0) < Me.[your field for ReorderLevel]
Then
' do something - maybe change color of text, or add a msgbox.....
Me.ReorderLevel.ForeColor = vbRed
Me.ReorderLevel.FontBold = True
Else
Me.ReorderLevel.ForeColor = vbBlack
End If

Your could also have a label with visible property changed to true or false
based on the above test.

Damon

I have a product form based on a query. The query has the current stock
level
and the minimum stock level.
I would like a flag or notification to show on the form when the stock
level
drops below the minimum level.
Wayne

 

[AccessVandal via AccessMonster.com]

It should be like

If Nz(Me.StockOnHand,0) < Me.StockLeveMinimum Then

Thanks - I used the following:
Private Sub Form_Current()

If Nz(Me.[StockOnHand,0] < Me.[StockLeveMinimum]) Then
MsgBox "RE-ORDER STOCK."

Me.[StockLeveMinimum].ForeColor = vbRed
Me.[StockLeveMinimum].FontBold = True
Else
Me.[StockLeveMinimum].ForeColor = vbBlack
End If
End Sub

I get an error message that says:
run time error '2465'
CSA can't find the field (I) referred to in your expression.

When I debug - the following is highlighted:
If Nz(Me.[StockOnHand,0] < Me.[StockLeveMinimum]) Then

Any ideas?

 

[KeMoAforika]

Thanks, I am using access 2003 and am clueless about conditional formatting
and record sets- any helpwould be appreciated.

 

[KeMoAforika]

Thanks - I did that but: compile error: Method or data member not found
The MsgBox "RE-ORDER STOCK." is highlighted.
If I delete that then same error but Me.[StockLeveMinimum].ForeColor = vbRed
is highlighted.

It should be like

If Nz(Me.StockOnHand,0) < Me.StockLeveMinimum Then

Thanks - I used the following:
Private Sub Form_Current()

If Nz(Me.[StockOnHand,0] < Me.[StockLeveMinimum]) Then
MsgBox "RE-ORDER STOCK."

Me.[StockLeveMinimum].ForeColor = vbRed
Me.[StockLeveMinimum].FontBold = True
Else
Me.[StockLeveMinimum].ForeColor = vbBlack
End If
End Sub

I get an error message that says:
run time error '2465'
CSA can't find the field (I) referred to in your expression.

When I debug - the following is highlighted:
If Nz(Me.[StockOnHand,0] < Me.[StockLeveMinimum]) Then

Any ideas?

 

[AccessVandal via AccessMonster.com]

It is weird that the simple message box had an error. I don’t see any syntax
or typos.

"Method or data member not found" is probably a missing control in your form
or the name of the control is incorrect. You’ll have to make sure that the
name "StockLevelMinimum" and "StockOnHand" are spelled correctly in each of
the control’s properties tab.

Here is site on conditional formating. You can download a sample to see how
it works.

http://www.blueclaw-db.com/download/conditional_formatting.htm

And here is a site on Access tutorial for beginners

http://www.functionx.com/access/index.htm

Thanks - I did that but: compile error: Method or data member not found
The MsgBox "RE-ORDER STOCK." is highlighted.
If I delete that then same error but Me.[StockLeveMinimum].ForeColor = vbRed
is highlighted.

 

[Arvin Meyer [MVP]]

Build a recordset in code like this (The event might be the after update
event of the OrderQty field. This code is untested and just type directly
into the newsreader):

Sub txtOrderQty_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL= "Select * From YourTable Where ID = & Me.txtID
Set rst = db.OpenRecordset(strSQL)

If rst!Qty <= rst!MinStockLevel Then
MsgBox "Time to reorder", vbOKOnly
End If

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

End Sub

You'll want to add error handling.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[KeMoAforika]

OK - I am a chop - your code was perfect - Thanks for the help.
Any idea how I can get the ""StockOnHand box to flash? when the level is
wrong?

It is weird that the simple message box had an error. I don’t see any syntax
or typos.

"Method or data member not found" is probably a missing control in your form
or the name of the control is incorrect. You’ll have to make sure that the
name "StockLevelMinimum" and "StockOnHand" are spelled correctly in each of
the control’s properties tab.

Here is site on conditional formating. You can download a sample to see how
it works.

http://www.blueclaw-db.com/download/conditional_formatting.htm

And here is a site on Access tutorial for beginners

http://www.functionx.com/access/index.htm

Thanks - I did that but: compile error: Method or data member not found
The MsgBox "RE-ORDER STOCK." is highlighted.
If I delete that then same error but Me.[StockLeveMinimum].ForeColor = vbRed
is highlighted.

 

[AccessVandal via AccessMonster.com]

Create a function instead, keeping calculated quantity does not help you to
maintain the correct balance in stock.

See here on how to from Allen Browne.

http://allenbrowne.com/AppInventory.html