Exception for Data Validation Rule

Question

I'm working between two tables: Jobs and Price Increases. One job can have many price increases. I'm using a data validation rule to not allow a Price Increase to be added if the value in the Increase Effective Date matches a summary field that looks at the parent Job and pulls in the Maximum Increase Effective Date (one job should not have more than one price increase scheduled for one day).

This works great when adding new records. But if I try to edit and then save the Price Increase record that has the Maximum Increase Effective Date, I get my data validation error (As I should, the two date fields match).
Any ideas of how can I set an exception to the data validation rule?

This is the rule: If([Rate Effective Date]=[Service - Maximum Effective Date], "error message".

Thanks!

------------------------------
Heather
------------------------------

adamkeever1 · Answer

Try using a separate form for editing and do not include the [Rate Effective Date] field. You can assign which form opens for which type of action view, edit, add, grid edit:
------------------------------Adam Keever------------------------------

briancafferelli · Answer

Hi Heather, you can add the exception by taking advantage of the fact that record ID#s are generated when a new record is saved, but not before that time. So when you first add a record, before saving, Record ID# is blank. But if you come back later and edit the record, then Record ID# has a value. So you can tweak your custom data rule to check whether record ID# is null, to add the exception you're looking for:

If([Rate Effective Date]=[Service - Maximum Effective Date] and IsNull([Record ID#]) = true, "Can only have one price increase scheduled per day")

------------------------------
Brian Cafferelli
------------------------------

Forum Discussion

Exception for Data Validation Rule