A common way to enforcing uniqueness across the data in two or more fields is to add a text field with an auto-enter calculation that combines them such as:
KeyField1 & " " & KeyFIeld2 //do not separate values with returns!
Then specify a unique values validation option on this new field.
OK, this makes great sense and works to eliminate duplicates. When a duplicate is discovered, however, the error message pops up on the join table and gives what could be considered confusing options to the users. Is there any way I can simply have the option automatically to be not to create the duplicate record and simply tell the user one of their joins already existed?
Option 1, Use OnObjectValidate to run a script that uses set Error Capture [on], Commit records and Get ( LastError ) to detect this error, but then your script offers it's own less confusing dialog and supporting scripting to catch and correct the error.
Option 2, Use the same basic script and trigger, but detect the duplicates via a self join that uses both fields as match fields.
Is there a way I can just have the duplicate record not be created (or automatically deleted) without worrying about offering a dialog to the user? I essentially just don't want duplicate records to be created in the first place.
The script I described could automatically delete or revert the record, but wouldn't that create confusion for you user? I guess it depends on the design of your database.
And some methods you might use for data entry can also prevent the creation of duplicates. Value lists--sometimes a dwindling value list, can limit user options to avoid duplication. Or the user enters data in a global field and a script then creates a new record only if a matching record does not already exist.