Others

Prevent Duplicate DocumentId Entries in a Microsoft Access Form

Huma Ashraf ChHuma Ashraf Ch Nov 13, 2026 869 views

Question details

Prevent users from entering duplicate numeric DocumentId values through an Access form.

Prevent Duplicate DocumentId Entries in a Microsoft Access Form
Product
Microsoft Access
Device & OS
not provided
Scenario
An Access form submits records to a table hosted on a company server, and the user cannot change the table through application code alone.
Observed behavior
Duplicate DocumentId entries must be prevented, including when multiple users submit the same value at the same time.
Before you start

Identify the server table and DocumentId column used by the form, and confirm who administers the database. Ask the administrator to check for existing duplicates and determine whether DocumentId must also be required.

Solution 1Recommended

Request a Server-Side Unique Constraint and Handle Duplicate Errors

Use this approach when the table is managed on a server and duplicate values must be prevented for all users.

A database-enforced unique constraint or unique index protects against simultaneous submissions. A primary key can also enforce uniqueness if DocumentId is suitable as the table's primary key.

A form-level lookup can provide an early warning, but it cannot guarantee uniqueness because another user may save the same value between the lookup and submission.

1
Ask the administrator to check existing data

Provide the database administrator with the server table and DocumentId column names. Ask them to identify and resolve duplicate values before creating the constraint.

2
Request database-level enforcement

Ask the administrator to create a unique constraint or unique index on DocumentId, or make it the primary key if appropriate. Also agree on whether null values should be allowed.

3
Open the form's error event

In Access, open the form in Design View, open the Property Sheet, and select the Event tab. Choose the On Error event and open its code builder.

4
Display a duplicate-entry message

In the Form_Error procedure, handle the duplicate-key error reported by your configuration and show a message asking the user to enter a different DocumentId. For Access error 3022, set Response to acDataErrContinue after displaying the message; test the actual error returned by the server because it may differ.

5
Test the constraint

Use the form to save a DocumentId, then try saving the same value again. Also test submissions from two user sessions and confirm that only one record is accepted.

Request a Server-Side Unique Constraint and Handle Duplicate Errors
Server error codes can vary: A linked server table may report a different Access or ODBC error than 3022. Inspect the error returned in your environment before suppressing a default message, so unrelated errors remain visible.
Free Microsoft Office alternative

Work With Office Documents in WPS Office

WPS Office is a free, lightweight alternative for everyday document and spreadsheet work. Its familiar interface and Microsoft Office format support make it easy to move existing files, but it does not enforce constraints on an Access database.

Review numeric data in spreadsheets alongside your database workflowOpen and edit Microsoft Excel XLSX, Word DOCX, and PowerPoint PPTX filesUse a familiar interface for a smooth transition from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why isn't a VBA duplicate check enough?

Two users can both pass a VBA lookup before either record is saved. A unique constraint or index enforced by the database prevents both records from being stored with the same DocumentId.

Can I add the unique index from Access?

For a server-hosted table, index creation depends on the database system and your permissions. If you cannot change the table, ask the database administrator to create the constraint or index on the server.

What if the table already contains duplicate DocumentId values?

The administrator must identify and resolve the duplicates before a unique constraint or index can generally be created. Agree on which records to keep or correct before making the change.

How do I show a friendly message when a duplicate is submitted?

Open the form in Design View and use Property Sheet > Event > On Error to create a Form_Error procedure. Handle the duplicate error returned by your setup, show a message, and suppress the default message only for the error you have identified.