Validating one field based on another in the same record
Posted on 2011-03-15
My database tracks barrel racing events. There is an event table with one record for each event and a class table that contains a record for each class in each event. I have a form, Add/Update Events, which contains event information on the main form and a datasheet view of class records related to the event currently displayed. In the class table I have many fields which contain information relevant to the class but for purposes of this question, the class table has these fields: EventID, ClassType (text), IncentiveOnly (yes/no), RollFromClass (text). The field, RollFromClass, should be left null unless IncentiveOnly is set to yes. In that case, RollFromClass should be assigned a value. That value should only be one of the values previously assigned to ClassType for a class in the current event.
There are 2 data validation issues I am not sure how to handle. The first is to ensure that RollFromClass is initialized when IncentiveOnly equals yes. The second is how to ensure that RollFromClass is initialized correctly, i.e., to one of the values previously entered as ClassType for this event.
Should any of this validation be defined at the table level? Should I handle it all in the form? I tried writing code to execute "on exit" from the RollFromClass field but I'm not sure I know how to reference specific rows in the datasheet view correctly. What do you suggest?