Checking field values

I have been trying for ages to write a sub that runs through the values of a specific field , "currentlevel" and compares it with the value of "reorderlevel".
However, I can't get it to work. Here's the code I'm using:

Sub CheckLevel()
Current = CDec(Data1.Recordset.Fields("CurrentLevel").Value)
Reorder = CDec(Data1.Recordset.Fields("ReorderLevel").Value)
Data1.Recordset.MoveFirst
While Not Data1.Recordset.EOF
If Current < Reorder Then
lblWarning.Caption = "Low Stock Warning!"
Exit Sub
Else
lblWarning.Caption = "Product Stock Level okay"
End If
Data1.Recordset.MoveNext
Wend
Data1.Recordset.MoveFirst
End Sub

If anybody can help I'd be greatly appreciative. Thanks



Knows a little about a lot

What's not working?

Q...

Basically, it runs through the database, which I know happens, but it doesn't check the values in Current and Reorder. The problem is this. I need for it to select one record at a time, analyse the two fields, CurrentLevel and ReorderLevel, display a suitable message and then move on to the next record.

Knows a little about a lot

It looks like you are setting your values for currentlevel and reorderlevel outside your loop and then you test them inside your loop. The values for currentlevel and reorderlevel never change in your subroutine once you set them. I think this might be what you are wanting to do!!:

Sub CheckLevel()

Data1.Recordset.MoveFirst
While Not Data1.Recordset.EOF
Current = CDec(Data1.Recordset.Fields("CurrentLevel").Value)
Reorder = CDec(Data1.Recordset.Fields("ReorderLevel").Value)
If Current < Reorder Then
lblWarning.Caption = "Low Stock Warning!"
Exit Sub
Else
lblWarning.Caption = "Product Stock Level okay"
End If
Data1.Recordset.MoveNext
Wend
Data1.Recordset.MoveFirst
End Sub

Also you might want to use CCur instead of CDec it uses a little less overhead!!

Could you try the following SQL? It should give you the items that need to be reordered...

select st1.*
from StockTable st1
join StockTable st2
on st1.CurrentLevel <= st2.ReOrderLevel

Needless to say you guys sussed it out again. By moving the Current = .... and the Reorder = .... lines to inside to the With statement, I managed to fulfill what I wanted. Thanks

Knows a little about a lot