Automatically set "Time to Reorder" field

[Joshua61679]

I'm a little rusty on my VBA, and I've not done this specifically before, so I need some help.

I'm trying to get a field to tell me its time to reoder when my quantities get to low. I've got a "UnitsInStock", a "UnitsOnOrder", a "MinumumLevel", and a just created "Reorder" field. Corrisponding Form and Table. I've also got 2 Macros, "Reorder Yes" and "Reorder No". These are set to setvalue the Reorder field to yes, or no, respectively.

What I'd like to happen is when the "UnitsInStock" + "UnitsOnOrder" is < "MinimumLevel" then the "Reoder" field is set to 'Yes'. Otherwise, I'd like it to set itself to 'No'. This is my attmept at it that isn't working, but I've just been guessing for the most part, so I'm probably totally off.

Private Sub Reorder_Exit(Cancel As Integer)

Dim strHave As String
Dim strMin As String

strHave = Me!UnitsInStock + Me!UnitsOnOrder
strMin = Me!Reorder

If strHave > strMin Then
    Call DoCmd.RunMacro("Reorder No")
End If
If strHave = strMin Then
    Call DoCmd.RunMacro("Reorder No")
End If
If strHave < strMin Then
    Call DoCmd.RunMacro("Reorder Yes")
End If

End Sub

Thanks for any help.

 

[PHV]

Provided Reorder is a boolean (Yes/No, True/False), in the BeforeUpdate event procedure of the form:
Me!Reorder = ((Me!UnitsInStock - Me!UnitsOnOrder) < Me!MinumumLevel)

You may also put this code in the AfterUpdate procedure of the UnitsOnOrder control.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Joshua61679]

Worked like a charm. Many, many thanks.