ProgrammingGrandma
asked on
Validating one field based on another in the same record
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My issue is not resolved.
But I don't really understand what you mean by..
"That value should only be one of the values previously assigned to ClassType for a class in the current event."
This sounds like you have to be referring to other records in the table, which is a bit of a pain in itself, but I don't quite get what you would do with the first record ( and the logic which follows is that that the second record must be the same as the first, the third must also be the same and so on)