Change Dialog Box based on Field contents of Different Record
We use our database to track maintenance issues for objects. Objects is one table. Maintenance Issues is another table, and they are linked by the Object ID. When a new maintenance issue is reported (by creating a new record in the maintenance table) and assigned to an object, I'd like to the database to check if there are already issues (i.e. other records) reported for that object which have not been marked as "resolved". The dialog box would say "There is already an unresolved issue reported for this object. Are you sure you want to submit a new maintenance issue? If they say No I'd like the just created record to be deleted. If they say Yes they would then be free to finish inputting data.
Any help someone can give me would be much appreciated.