Others

Allow One Duplicate Value in a Microsoft Access Field

Amos GikundaAmos Gikunda Nov 18, 2026 868 views

Question details

Allow the value E-Payment to appear multiple times while preventing other duplicate authorization codes.

Allow One Duplicate Value in a Microsoft Access Field
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.
Before you start

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.

Solution 1Recommended

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.

1
Allow duplicates in the field index

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.

2
Open the form event

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.

3
Add the validation code

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

4
Test new and existing records

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.

Validate Authorization Codes in the Form Before Update Event
Validation scope: Ensure all relevant data-entry paths use equivalent validation. The example checks a trimmed input but does not normalize existing stored codes; consider how spaces and case should be handled across your data.
Free Microsoft Office alternative

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.

Review authorization-code lists in a spreadsheet.Open and edit Microsoft Excel XLSX files.Use a familiar interface for everyday office tasks.Move compatible Office documents into a lightweight suite.
microsoft office alternative - wps office

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.