Warning message box on a calculated field

[lsyrkin]

I got stuck in a task that seemed to be very simple. I have a form with
a subform (A) containing several nested subforms(B,C,D...) in
Access2000. Two of these nested subforms contain times that I want to
compare and give a warning message when it is out of range. I created a
calculated field called Uptk in A:
Uptk=DateDiff("n",[sfrmInjTracer].[Form]![InjTime],[sfrmScan].[Form]![StTime])

Then I tried to create a message box in BeforeUpdate or AfterUpdate on
Uptk :

If 50 > Me![Uptk] Or Me![Uptk] > 70 Then
MsgBox ("Uptake time is out of range! Please provide comment.")
End If

Nothing works, though DblClick event works fine:

Private Sub Uptk_DblClick(Cancel As Integer)
If 50 > Me![Uptk] Or Me![Uptk] > 70 Then
MsgBox ("Uptake time is out of range! Please provide comment.")
End If
End Sub

I really want the message to popup without any additional user 'clicks'
- just after Uptk calculation.

Will appreciate any advice on this matter.
Leonid

 

[Klatuu]

The before and after update events do not fire until the value in the control
is changed by a user and the user navigates away from the control. Since
your value is in the Control Source of the control, it is the same as
populating the control using VBA. Programmatic changes to a control does not
cause the events to fire and, since it is a calculated control the user does
not put anything in to, it is highly likely they will not enter the control
anyway.

I would suggest you use the form's Before Update event:

If Me![Uptk] < 50 Or Me![Uptk] > 70 Then
MsgBox "Uptake time is out of range! Please provide comment."
Cancel = True
Me.CommentsField.SetFocus
End If

Notice I changed the comparison logic. What you wrote is prefectly okay,
but very unusual and harder to read and understand.

Cancel = True
Will prevent the update from occuring.

Me.CommentsField.SetFocus
Just a suggestion for user convenience. I notices you ask for comments, so
it would be nice to position the cursor in the control where the user would
enter the comments.

 

[lsyrkin]

It did not work for me. If I put the code into the subform that
contains Uptk, nothing happened. Then I move it one level up to the
main form frmPatientDemo:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Forms.frmPatientDemo.sfrmVisit![Uptk] < 50 Or
Forms.frmPatientDemo.sfrmVisit![Uptk] > 70 Then
MsgBox ("Uptake time is out of range! Please provide comment.")
Cancel = True
Forms.frmPatientDemo.sfrmVisit!NotComplRsn.SetFocus
End If
End Sub

That works but the message keeps popping up. I can't get out if Uptk is
not in the range.

Leonid

The before and after update events do not fire until the value in the control
is changed by a user and the user navigates away from the control. Since
your value is in the Control Source of the control, it is the same as
populating the control using VBA. Programmatic changes to a control does not
cause the events to fire and, since it is a calculated control the user does
not put anything in to, it is highly likely they will not enter the control
anyway.

I would suggest you use the form's Before Update event:

If Me![Uptk] < 50 Or Me![Uptk] > 70 Then
MsgBox "Uptake time is out of range! Please provide comment."
Cancel = True
Me.CommentsField.SetFocus
End If

Notice I changed the comparison logic. What you wrote is prefectly okay,
but very unusual and harder to read and understand.

Cancel = True
Will prevent the update from occuring.

Me.CommentsField.SetFocus
Just a suggestion for user convenience. I notices you ask for comments, so
it would be nice to position the cursor in the control where the user would
enter the comments.

--
Dave Hargis, Microsoft Access MVP

I got stuck in a task that seemed to be very simple. I have a form with
a subform (A) containing several nested subforms(B,C,D...) in
Access2000. Two of these nested subforms contain times that I want to
compare and give a warning message when it is out of range. I created a
calculated field called Uptk in A:
Uptk=DateDiff("n",[sfrmInjTracer].[Form]![InjTime],[sfrmScan].[Form]![StTime])

Then I tried to create a message box in BeforeUpdate or AfterUpdate on
Uptk :

If 50 > Me![Uptk] Or Me![Uptk] > 70 Then
MsgBox ("Uptake time is out of range! Please provide comment.")
End If

Nothing works, though DblClick event works fine:

Private Sub Uptk_DblClick(Cancel As Integer)
If 50 > Me![Uptk] Or Me![Uptk] > 70 Then
MsgBox ("Uptake time is out of range! Please provide comment.")
End If
End Sub

I really want the message to popup without any additional user 'clicks'
- just after Uptk calculation.

Will appreciate any advice on this matter.
Leonid

 

[lsyrkin]

It works fine now. Klatuu, thanks a lot for your help.

Okay, my bad. I did not think it all the way through. Since you want to
allow the out of range data to be saved, but want to require a comment as to
why it is out of range, you can try this (untested air code):

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Forms.frmPatientDemo.sfrmVisit![Uptk] < 50 Or
Forms.frmPatientDemo.sfrmVisit![Uptk] > 70 And
IsNull(Forms.frmPatientDemo.sfrmVisit!NotComplRsn)Then
MsgBox ("Uptake time is out of range! Please provide comment.")
Cancel = True
Forms.frmPatientDemo.sfrmVisit!NotComplRsn.SetFocus
End If
End Sub

This will allow an out of range value to be accepted if a comment has been
entered.
--
Dave Hargis, Microsoft Access MVP

It did not work for me. If I put the code into the subform that
contains Uptk, nothing happened. Then I move it one level up to the
main form frmPatientDemo:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Forms.frmPatientDemo.sfrmVisit![Uptk] < 50 Or
Forms.frmPatientDemo.sfrmVisit![Uptk] > 70 Then
MsgBox ("Uptake time is out of range! Please provide comment.")
Cancel = True
Forms.frmPatientDemo.sfrmVisit!NotComplRsn.SetFocus
End If
End Sub

That works but the message keeps popping up. I can't get out if Uptk is
not in the range.

Leonid

The before and after update events do not fire until the value in the control
is changed by a user and the user navigates away from the control. Since
your value is in the Control Source of the control, it is the same as
populating the control using VBA. Programmatic changes to a control does not
cause the events to fire and, since it is a calculated control the user does
not put anything in to, it is highly likely they will not enter the control
anyway.

I would suggest you use the form's Before Update event:

If Me![Uptk] < 50 Or Me![Uptk] > 70 Then
MsgBox "Uptake time is out of range! Please provide comment."
Cancel = True
Me.CommentsField.SetFocus
End If

Notice I changed the comparison logic. What you wrote is prefectly okay,
but very unusual and harder to read and understand.

Cancel = True
Will prevent the update from occuring.

Me.CommentsField.SetFocus
Just a suggestion for user convenience. I notices you ask for comments, so
it would be nice to position the cursor in the control where the user would
enter the comments.

--
Dave Hargis, Microsoft Access MVP


:

I got stuck in a task that seemed to be very simple. I have a form with
a subform (A) containing several nested subforms(B,C,D...) in
Access2000. Two of these nested subforms contain times that I want to
compare and give a warning message when it is out of range. I created a
calculated field called Uptk in A:
Uptk=DateDiff("n",[sfrmInjTracer].[Form]![InjTime],[sfrmScan].[Form]![StTime])

Then I tried to create a message box in BeforeUpdate or AfterUpdate on
Uptk :

If 50 > Me![Uptk] Or Me![Uptk] > 70 Then
MsgBox ("Uptake time is out of range! Please provide comment.")
End If

Nothing works, though DblClick event works fine:

Private Sub Uptk_DblClick(Cancel As Integer)
If 50 > Me![Uptk] Or Me![Uptk] > 70 Then
MsgBox ("Uptake time is out of range! Please provide comment.")
End If
End Sub

I really want the message to popup without any additional user 'clicks'
- just after Uptk calculation.

Will appreciate any advice on this matter.
Leonid