Help Please - Duplicating Record also Duplicates Unique ID so is Not Unique Anymore
In my database I have several tables (lets call them tables ABC) and for each ABC table when a new record is created it needs a unique ID, thus I have a separate table (lets call that table X) that runs a script when new records are committed in tables ABC to assign a new unique_ID. The field unique_ID is set to be unique and not be modified and until now I notice no problems however I just realized that the "duplicate record" function when you right click in browse mode, which is most helpful in saving time when entering records and I have used a lot in recent record additions, also duplicates the unique_ID field, thus, it is not unique anymore, in fact I have about 100 records now all with the same "unique_ID".
I realize if I disabled the "duplicate record" function I could have my own button and script behind it that would first duplicate the record but then immediately delete the value in the unique_ID field so when my "commit record" script ran it did so correctly. Unfortunately I don't know how to disable menu options individually and I don't want to change the other menu options that a user can access.
So I thought, well maybe I can just add to the "Create ID" script that runs when the records in tables ABC are committed and the attached screen shot is what I came up with. It works wonderful! except that because it is checking to see if the unique_ID already exists EVERY TIME a record is committed it works even when I don't want it to. What I mean is if the field is not empty then I can guarantee that it already exists because it was generated by that table. So unless there is a way to modify this script I don't know what else to do.
All I want to do is to make sure when you "duplicate a record" you don't duplicate the unique_ID, or if you do it is very temporary and on "commit record" it assigns a new TRULY unique ID.
Any suggestions would be most helpful. Thank you!