ACC2000: How to Check for Duplicate Values in Primary Key Fields (209487)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q209487
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY

When you are adding records, Microsoft Access does not normally check the values in primary key fields for duplicates until you move to the next record. If you enter an invalid or duplicate value in a control and move to the next record, you may invalidate all previous entries. However, there is a method to force an immediate check for duplicate values. This article describes how to check for duplicate values immediately.

MORE INFORMATION

The following example uses the Customers form in the sample database Northwind.mdb. The Customers form is based on the Customers table; CustomerID is the primary key in the table.

NOTE: Microsoft Access 2000 has an example of the following in the sample database Northwind.mdb. On the Customers form there is a macro on the BeforeUpdate event of the CustomerId field that verifies that the CustomerId is unique.

To force an immediate check for duplicate values, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Start Microsoft Access and open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
  2. Create the following new macro. Make sure to click Conditions on the View menu before you begin entering this macro.
       Condition                                   Action
   ----------------------------------------------------
   DLookUp("[CustomerID]","[Customers]", _     MsgBox
   "[CustomerID] = Form.[CustomerID] ") _
   Is Not Null
   ...                                      CancelEvent
 
   Action            Arguments
   ------------------------------------------------
   MsgBox            Message: Duplicate Customer ID
  3. Save the macro as IsItDup and close the macro.
  4. Open the Customers form in Design view. Change the BeforeUpdate property of the CustomerID field to IsItDup.
After you make this change, adding a duplicate value in the CustomerID field results in an error message. The insertion point remains in the field so that you can change the value.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbhowto kbusage KB209487
©2004 Microsoft Corporation. All rights reserved.