detecting duplicates of primary key at once

[Benj]

Hi everyone! Just wanting to know if there is a after update code to immediately detect a primary key duplicate? I know it would eventually would but it happens after i've entered other fields for that duplicate. Thanks in advance!

[isladogs]

Use something like

If DCount("*", "TableName", "FieldName = 'SomeValue'")>0 Then

After update is too late. Use the BeforeUpdate event so it can be cancelled if a duplicate exists

[June7]

Why would a primary key duplicate? Is this an autonumber field or a custom key constructed with code?

[Benj]

Hi June7! i have to enter my own primary key. its not an autonumber. its actually a tag for an animal. sometimes instead of entering 907Y i would enter 907W which has a duplicate.

[June7]

As Colin said, do data validation in BeforeUpdate event.

[CJ_London]

Really don't recommend you use this as a primary key - what if you type it in wrong and have to correct it? - all related tables will need to be updated as well.

Have an autonumber primary key field and link to other tables on that. And for your tag number specify the field as indexed no duplicates. That will prevent a duplicate being added, but I would still uses the form beforeupdate event to check if the tag key already exists (or perhaps better the after update event of the tag control) if only for more control on the messaging when a duplicate is entered

[orange]

I agree with others --use autonumber as Primary Key for the table.
If you want to use a field for your info --eg: TagNo that you identify as indexed No Duplicates.
I would include an error handler and check specifically for error 3022 (duplicate value).
The DBMS will highlight this error when you attempt to add a duplicate PK, and/or add a duplicate to an index identified as No Duplicates.

Good luck.