Share via

Duplicate Record Error Message

Anonymous
2015-02-19T01:15:57+00:00

The below message has cropped up several times in my dealings with Access.  I searched the community for a solution to this and found one that made sense to me. The person said that in a subform you should not use the record source for the main form as part of the record source in the subform.

“The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.”

I did have this situation and removed the main table from the query.  I haven't tested it yet, but hopefully it works because this problem is very frustrating for my clients and myself.

My question is as follows:

My main form contains 3 subforms. One of the subforms contains information in a query that uses the main table as a record source. This query has criteria that only brings in the data I want to see from the main table.

Will this cause the duplicate record error to occur? Or does this happen only in the case where a subform record source comprises 2 tables, one being the main table.

Hope I have explained this correctly, and thanks in advance for your help.

Connie

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-02-19T02:29:48+00:00

    It just depends on the query and what you are doing with it. As Karl says, please post the SQL! The reason for the message is just what it says - if the Subform attempts to add a record to the table already in use by the mainform, and doing so would create a duplicate, you'll get the error.

    One question: WHY display the same data twice, once on the mainform and again on the subform? If you do need to do so (for screen space issues, or readability), consider using textboxes on the subform with control sources like

    =[Parent].[fieldname]

    to pull data from the main form and just display it on the subform; these controls will not be editable, but in general you wouldn't want the same data being edited in two places anyhow.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2015-02-19T02:04:08+00:00

    “The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.”

     The error would only occur when trying to add a new record in the table that has a field set for unique values.

    Post the SQL of query used for subform for suggestions on how it maybe revised to avoid the problem.    Also say which table and field of main form query is set for unique.

    1 person found this answer helpful.
    0 comments