The changes you requested to the table were not successful because they would create duplicate values in the index (1 Viewer)

[SachAccess]

Hi,

Am trying to edit an existing form on a MS Access Database file. This DB s not built by me.
When am trying to open a particular form, it is giving me below two warnings.
There are few combo boxes on the form. Some are working perfectly fine.
For few CBs, if you I try to select the drop-down option, it gives me error and sometimes it allows me to use drop-down without any issue.
What things I should check in this form to resolve the issue. My apologies if this is in-complete information.
My primary aim for posting this question is to understand meaning and reasons for the pop-up am getting.
Can anyone please help me in this.

First pop-up
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 fields that contains duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Second pop-up
You cant save tis record at this time.
MyToolName may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?

PS - Might be delay from my side today to respond further.

 

[Ranman256]

Is this db shared? If so, Is it split? (it MUST be split in multi user environ)

Multiple users cannot edit the same record.
Also you cannot add a duplicate rec with the same key.

BUT I have got this error in a single user db, (my db on my pc, no other users)
this is due to the design of the screen. Access has a lock and you cant edit your record with you as the sole user.
The best method to prevent this is have a header record as the master, (not getting edited)
then a subform to edit the detail.
This seems to prevent DROP CHANGES error, and prevent adding duplicates.

 

[The_Doc_Man]

Your first pop-up occurs because the table underlying that form has indexes on some of its fields and one of them says "No Dups" (no duplicates) as a field-level constraint. A "No Dups" constraint for the table's prime key (PK) is normal and is in fact part of the requirement of being a PK, but having that constraint for another field than the PK is extremely rare. It is possible that you have a rare situation in which you have an autonumbered PK and also had a "natural" candidate for the PK - but didn't declare it as the PK for some reason and forgot the constraint. If it is a query that drives the table, you have to descend to the tables that comprise the query to locate the field that is the real cause.

The other possibility is that it isn't due to another field; instead, you have a PK that isn't auto-generated but rather is manually entered and the value being entered already exists.

The second pop-up can have multiple causes. Look for a case where the underlying record is open through the form AND is opened for edit (or is a new record) through some other means like another form or a programmed recordset. This one most likely represents a conflict of attention to a single record.

 

[Solo712]

Hi,
if you see these messages all of a sudden in a Db that was working fine before, I would recommend doing a Compact & Repair routine before anything else. If that does not solve the problem you need to find which of the indexes allows no duplicates, starting with the primary key. If you have more than one field in the table that restricts it to a non-duplicated value then you will have to do a deep dive to find out which one runs afoul of the restriction. But most of the time this message is telling you that the table's primary key has issued a duplicate value when inserting a new record. If your PK is an autonumber, you might have to reseed it. Here is how that is done.

I would not concern myself with the second message. In the context, it simply tells you that Access cannot save the record because of the problem of the first message. It will go away as soon as you handle the duplicate issue.

Best,
Jiri

 

[The_Doc_Man]

Jiri could be right about the second pop-up. I forgot to ask whether the second pop-up message IMMEDIATELY follows the first pop-up with no discernible delay or at most, literally 1 or 2 seconds. If so, then Jiri is probably right - the 2nd message derives from the 1st message. IF there is a delay of more than a couple of seconds then maybe the events are not related.

 

[SachAccess]

Hi @Ranman256 @Solo712 and @The_Doc_Man thanks a lot for the help.
I will revert with details in some time. Have a nice day ahead.

 

[SachAccess]

Is this db shared? If so, Is it split? (it MUST be split in multi user environ)

Multiple users cannot edit the same record.
Also you cannot add a duplicate rec with the same key.

BUT I have got this error in a single user db, (my db on my pc, no other users)
this is due to the design of the screen. Access has a lock and you cant edit your record with you as the sole user.
The best method to prevent this is have a header record as the master, (not getting edited)
then a subform to edit the detail.
This seems to prevent DROP CHANGES error, and prevent adding duplicates.

Hi @Ranman256, thanks for the help, there are two files. One is Back-end and other is Front-End.
BE only has tables, nothing else.
FE has linked tables, forms, queries, reports, macros and VBA.
Both the files are saved in same folder only and at the moments am the only user of these files.