How can I apply multiple table-validation texts to the same table in Access?

Question

My table's records need to satisfy two conditions:

• Entries in the ClosingDate field cannot correspond to a date before what is in the RequestDate field.
• A BidBond value may be supplied if, and only if, the record's Type field is set to "Tender"

The two resultant multi-field validation rules are:

1. ([ClosingDate] Is Null) Or ([ClosingDate] >= [RequestDate])
2. ([Type]="Tender") Or ([BidBond] Is Null)

• If Rule 1 is violated, the validation text should be "Closing date cannot occur before the request is made."

• If the data entered falls foul of Rule 2, "Bid bonds can only be assigned to tenders." should greet the user.

I know I can combine the two validation rules with an And clause, but then how would I specify two different validation texts depending on which validation rule is violated?

Answer 1

I don't see how to do what you want with validation rules at the table level. Instead I will suggest using forms for data editing. Forms give you more flexible options to handle complex business rules. You can handle your validation requirements with events (such as Before Update and After Update) associated with individual form fields, or events at the form level.

One possibility your question didn't address is what should happen if both your validation rules are violated in the same record. Your validation text issue becomes even more complicated. I wouldn't attempt it with table validation rules.

Furthermore, Access MVP types recommend limiting user interactions to forms. The users should never even see datasheet views of tables or queries.