Allow One Duplicate Value in a Microsoft Access Field
Question details
Allow the value E-Payment to appear multiple times while preventing other duplicate authorization codes.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A user enters authorization codes in an Access database, including E-Payment entries that may legitimately repeat.
- Observed behavior
- A unique index prevents every duplicate value, including repeated E-Payment entries.
Identify the table, authorization-code field, primary key, and form used for data entry. The example below assumes a text field named AuthorizationCode and a numeric primary key named PaymentID in a table named Payments.
Validate Authorization Codes in the Form Before Update Event
Use form validation to allow repeated E-Payment entries while rejecting blank codes and other duplicates.
This check applies only when records are saved through the form containing the event procedure. Direct table edits, queries, other forms, and simultaneous updates can bypass or defeat the check.
Open the Payments table in Design View, select AuthorizationCode, and set Indexed to Yes (Duplicates OK) or No. Also check the Indexes window for any separate unique index that prevents this field from repeating.
Open the data-entry form in Design View. In the form's Property Sheet, select the Event tab, set Before Update to [Event Procedure], and click the ellipsis button to open the VBA editor.
In the VBA editor, use the following form event procedure, replacing the table, field, and control names if yours differ: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim code As String Dim criteria As String code = Trim(Nz(Me!AuthorizationCode, "")) If Len(code) = 0 Then MsgBox "Enter an authorization code." Cancel = True Exit Sub End If If StrComp(code, "E-Payment", vbTextCompare) <> 0 Then criteria = "[AuthorizationCode] = '" & Replace(code, "'", "''") & "'" If Not Me.NewRecord Then criteria = criteria & " AND [PaymentID] <> " & Me!PaymentID End If If DCount("*", "Payments", criteria) > 0 Then MsgBox "This authorization code already exists." Cancel = True End If End If End Sub
Save the form, open it in Form View, and try saving two E-Payment entries, two identical non-E-Payment codes, and a blank code. Edit an existing record without changing its code to confirm that it does not count itself as a duplicate.

Work With Authorization-Code Lists in WPS Spreadsheets
WPS Office is a free, lightweight alternative for everyday document and spreadsheet work. Its familiar interface and Microsoft Office format compatibility make it easy to move spreadsheet lists into WPS, though it does not replace Access database validation.

Frequently Asked Questions
Can an Access unique index exempt E-Payment from duplicate checks?
No. A standard unique index applies to all indexed values. Allow duplicates in the field index and use a separate validation method for the exception.
Why does the validation exclude the current record?
When an existing record is edited, DCount would otherwise find that record's own authorization code. The PaymentID condition excludes it from the duplicate check.
Will the form event prevent duplicates entered directly in the table?
No. The Before Update event runs when the form saves a record; direct table edits and other entry paths need their own controls.
How can I change the value that is allowed to repeat?
In the form's Before Update procedure, replace "E-Payment" in the StrComp comparison with the value you want to exempt, then save and test the form.




