ACC: How to Check for Duplicate Values in Primary Key Fields
ID: Q102527
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
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 in Microsoft Access 7.0 and 97 (or NWIND.MDB in Microsoft
Access 2.0 or earlier). The Customers form is based on the Customers
table; CustomerID is the primary key in the table.
NOTE: Microsoft Access 97 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:
NOTE: CustomerID is called Customer ID in Microsoft Access 2.0 or
earlier.
- Create the following new macro. Make sure to select Conditions
on the View menu before you begin entering this macro.
NOTE: In the following sample macro, an underscore (_) is used as a
line-continuation character. Remove the underscore when re-creating
this macro.
MacroName Condition Action
----------------------------------------------------------------
IsItDup DCount("[CustomerId]","Customers", _ MsgBox
"[CustomerId]=Forms![Customers]! _
[CustomerId]")<>0
... CancelEvent
IsItDup Actions
---------------------------------
MsgBox
Message: Duplicate Customer ID
CancelEvent
- Open the Customers form in Design view. Change the BeforeUpdate
property of the CustomerID field as follows:
Text Box: CustomerID
BeforeUpdate: 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.
Additional query words:
count before update
Keywords : kbusage FmsOthr
Version : 1.0 1.1 2.0 7.00 97
Platform : WINDOWS
Issue type : kbhowto