Meesage Box When Citeria Is Met 1

[pjd218]

I have a form with an unbound field that is calculated by the entries to 20+ bound fields.

When the value of the unbound field goes below a preset parameter, a message box pops up to warn the user. The user can then close the message box and continuing entering/editing data on the main form.

My problem is I cannot figure from what event to fire the code. I tried the OnChange event of the calculated field, but no luck.

Currently, I am using the MouseMove event and everything works fine, but as you would expect, the message box keeps firing.

I only need this message to appear once when the criteria is first met. I am using Nz()+Nz()... to do the addition in the calculated field and it works properly.

Current code is:

Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
If [Text25] <= [Target Cap Rate] - 0.02 Then
DoCmd.OpenForm "CAPRATEWARN_FRM", acNormal, "", "", , acNormal
End If
Cancel = True
End Sub

As always, any help is appreciated.

 

[PHV]

the message box keeps firing
Use a global variable, eg:

Public gblWarned As Boolean
Private Sub form_Current()
gblWarned = False
End Sub
Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
If gblWarned = False And Me!Text25 <= Me![Target Cap Rate] - 0.02 Then
  gblWarned = True
  DoCmd.OpenForm "CAPRATEWARN_FRM", acNormal, "", "", , acNormal
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pjd218]

PHV,

Worked like a charm.

The last piece of my project puzzle.