Prohibit data entry based on status of existing records
I have a table that keeps track of cards issued to employees and the dates they were issued/returned. The same card number may be returned and then reissued to another employee. Obviously the physical card can only be issued to one person at a time and I would like this to reflect in the database so that if the card issuer tries to issue a card that has an existing record with no return date (say they forgot to input that when the card was previously returned) then they are forced to put in the return date for the already issued card before they can enter a new record with that card number. I was thinking there must be some way to do this with data validation or a script or a combination of the two, if anyone has any suggestions on the most effective way to accomplish this I would love to hear them.