validation rule text not displaying

[quaddawg via AccessMonster.com]

I have a subform with a field for which I set a validation rule and
validation text (on the form, not table). When I enter data that violates
the rule in that field I cannot procede, but no message (default nor one with
my language) displays.
As a workaround of sorts, I cribbed this code from a post:

Private Sub End_Date_LostFocus()

If Me.StartDate > Me.ThirdRenewal Then

MsgBox "End Date must be later than Start Date!", vbQuestion + vbOK,
"Wrong End Date"

End If
End Sub

This gives me the error message when the rule is violated, however it is post
hoc and does not prevent the user from entering the violative datum.

So,
1. Any ideas why the Validation Text will not display?
2. What might be a better way of handling this issue so that the wrong data
is prevented and an appropriate error message is displayed?

Thanks for your help!

 

[Allen Browne]

The Validation Text of the text box should work, but only if the Validation
Rule of the text box is violated. If something else is wrong (e.g. too long,
wrong type), the Error event of the form will fire instead.

You can use the BeforeUpdate event of the control (rather than LostFocus).
Cancel the event to keep the user in the control.

Because you are actually comparing 2 fields, and you cannot know which one
the user will enter first (or if they will go back and change the other one
later), use the BeforeUpdate event of the *form*, not the text box.

You could avoid all the code by setting a Validation Rule on the *table*,
rather than on a field in the table, or the text boxes or form:
1. Open the Table in design view.
2. Open the Properties box (View menu.)
3. Beside the Validation Rule in the Properties box, enter:
([StartDate] Is Null) OR ([ThirdRenewal] Is Null) OR ([StartDate] <
[ThirdRenewal])
4. Enter whatever text you want for the Validation Text.
5. Save the table.

(Note that the Validation Rule in the Properties box in table design is
different from the Validation Rule in the lower pane of table design.)