Prevent Duplicate DocumentId Entries in a Microsoft Access Form
Question details
Prevent users from entering duplicate numeric DocumentId values through an 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.
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.
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.
Provide the database administrator with the server table and DocumentId column names. Ask them to identify and resolve duplicate values before creating the constraint.
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.
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.
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.
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.

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.

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.




