Allow Specific Duplicate Values in a Microsoft Access Table
Question details
Allow a defined list of values to appear more than once in an Access table while preventing other duplicate values.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A table field must accept repeated approved values such as abc, def, and ghi, but other values should be unique.
- Observed behavior
- Approved values can be repeated, while duplicate entries outside the approved list are rejected.
Identify the table, the field to check, its unique record ID, and the approved values. Check for existing unapproved duplicates and test the rule in a test database before applying it to production.
Check for Unapproved Duplicates in an Access Form
Use a form validation procedure when users enter or edit records through a controlled Access form.
A standard unique index cannot make exceptions for selected values. This example checks entries before the form saves them; it does not protect edits made outside the form or guarantee uniqueness during simultaneous edits.
The example assumes a text field named ValueField, a numeric primary key named ID, and a form bound directly to YourTable. It permits null and empty values and treats the approved values as case-insensitive.
Open YourTable in Design View and select ValueField. If its Indexed property is set to Yes (No Duplicates), change it to Yes (Duplicates OK) or No so approved values can repeat. Also review View > Indexes for a separate unique index on this field.
Open the data-entry form in Design View, open its Property Sheet, and select the form. On the Event tab, set Before Update to [Event Procedure], then select the ellipsis button to open the VBA editor.
Replace the generated Form_BeforeUpdate procedure with the following code, changing YourTable, ValueField, and ID to your actual names: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim v As String Dim criteria As String If IsNull(Me!ValueField) Then Exit Sub v = CStr(Me!ValueField) If Len(v) = 0 Then Exit Sub Select Case LCase$(v) Case "abc", "def", "ghi" Exit Sub End Select criteria = "[ValueField] = '" & Replace(v, "'", "''") & "'" If Not Me.NewRecord Then criteria = criteria & " AND [ID] <> " & CLng(Me!ID) End If If DCount("*", "YourTable", criteria) > 0 Then MsgBox "This value is already in use.", vbExclamation Cancel = True End If End Sub
Save the form and open it in Form View. Enter an approved value twice, then try entering an unapproved value twice. Edit an existing record without changing its value to confirm that it does not conflict with itself.

Enforce the Exception Rule in the Back-End Database
Use back-end enforcement when records can be changed through multiple forms, imports, or applications.
Review Spreadsheet Data With WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface and compatibility with Microsoft Office files, making it easy to move everyday document and spreadsheet work. WPS does not enforce Microsoft Access database constraints.

Frequently Asked Questions
Can an Access unique index allow only certain duplicate values?
No. An Access unique index does not provide a filtered list of exceptions. Use a form procedure for controlled data entry or an appropriate back-end enforcement mechanism.
Why does the form check exclude the current record?
When an existing record is edited, the DCount criteria excludes its ID so the record does not count itself as a duplicate. The example assumes ID is a numeric primary key.
Will the form procedure stop duplicates entered through an import?
No. The form's Before Update event runs for edits made through that form. Imports, direct table edits, and other applications need their own checks or back-end enforcement.
What happens to blank values in the example?
The procedure exits without checking null or empty strings. If blanks must be prohibited, add a separate required-field rule in the table or form.




