Office Settings & Configuration

Allow Specific Duplicate Values in a Microsoft Access Table

Adam DavisAdam Davis Nov 22, 2026 868 views

Question details

Allow a defined list of values to appear more than once in an Access table while preventing other duplicate values.

Allow Specific Duplicate Values in a Microsoft Access Table
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.
Before you start

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.

Solution 1Recommended

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.

1
Review the field index

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.

2
Open the form event

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.

3
Add the duplicate check

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

4
Test the form

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.

Check for Unapproved Duplicates in an Access Form
Form validation has limits: Direct table edits, imports, other forms, and concurrent users can bypass this check. Use back-end enforcement if every write must follow the rule.
Free Microsoft Office alternative

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.

Review lists and duplicate values in WPS SpreadsheetsOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar interface for everyday Office tasksWork with documents and spreadsheets in a lightweight suite
microsoft office alternative - wps office

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.