It’s easy to get burned when trying to set up custom validations for Access fields and enforcing them while the form is being filled out.

I have a form with the following fields (of the type):

• Vendor Name (string)
• Is Vendor Active (checkbox)
• Is On PO form (checkbox)
• Is On Inspection form (checkbox)
• Approval Status (string)
• COE Expiration date (date)
• Registration Expiration date (date)

This is just a small portion of the form, but there are some crazy dependencies.

• Vendor Name is always required – no biggie there
• If the vendor is no longer active the following requirements don’t really matter
• If Is On PO form checked, then the two date fields must be filled out.
• If only On Inspection is checked the expiration dates and Approval Status must be blank, but if both are checked the PO checkbox rule applies
• The Approval status can only be Approved if (1. the On PO form is checked, 2. Both expiration dates are filled in, and 3. Both expiration dates are in the future)

At the time of this writing, the Approval status box can not be changed to Approved when both expiration dates are filled in and one of them is expired, BUT, the user CAN enter valid expiration dates, change the status to Approved, then do anything they want to the expiration dates, either blanking them out or making one of them in the past.

Only the Approval dropdown is being checked for the valid condition.

Normally, I don’t do final approvals until the user is closing the form, which does enforce the restrictions, but the customer doesn’t even want to allow the fields to be in those states at all.

So, my plan is to write a function to specifically check the whole group for validity. On the BeforeUpdate event of the form (because in my case, each of the fields is bound to a table field) I can check the new state of the whole setup. If it’s not valid, I can simply Cancel the change. This plan will also require me to do an explicit save of the recordset either by doing Form.Dirty=False or a similar equivalent when updating any of the fields to trigger the whole form BeforeUpdate event.

An alternative would be to put the check in each control’s BeforeUpdate event. This may be a little more controlled since I know in that case I am only cancelling one control’s change vs perhaps a change to the name of the vendor made before an Approval status change. In that case, you could end up canceling the vendor name change as well as the control you don’t actually want them to change.