Validation rule - Duplicates exception - Access 2016 table/form

Question

I need to set my table/form to not allow duplicates, except for three commonly strings.

something like this primitive logic:

if string is not "abc" or "def" or "ghi" then

   No duplicates for this field

end 

*******

is this possible? If so, HOW?

Sue

Answer 1

Can you explain the logic behind this? I'm wondering if there is not a better way.

Answer 2

It can be done by applying a CHECK CONSTRAINT to the table.  In Access this cannot be done directly in SQL view, however, but must be executed in code, which can be entered as a single line in the debug (aka 'immediate') window, which can be opened by pressing Ctrl+G.  The statement would be as follows:

CurrentProject.Connection.Execute "ALTER TABLE YourTableName ADD CONSTRAINT SelectiveDuplication CHECK((SELECT YourColumnName FROM YourTableName WHERE NOT (YourColumnName = 'abc' OR YourColumnName = 'def' OR YourColumnName = 'gh1') GROUP BY YourColumnName HAVING COUNT(*)>1)IS NULL)"

To remove the CONSTRAINT you'd enter the following:

CurrentProject.Connection.Execute "ALTER TABLE YourTableName DROP CONSTRAINT SelectiveDuplication"

The table must be closed when either of the above are entered, and, if the database is split the statement must be executed in the back end, not the front end.