Duplicate Values Error Message 1

[KerryL]

I've been using an application for months without incident. Now all of the sudden I'm receiving the following error message even though no changes have been made to the database, the program, or the tables:

"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 assume the problem is that the application is trying to add records into tables using a number that already exists in a key field? The key fields in each table are automatcially numbered, so I don't understand how this problem can all of the sudden appear.

Any help will be appreciated very much.

Kerry

 

[JoeMiller]

One of your other fields may be indexed and have it set to No Duplicates allowed. Go into the table definition and look at the Indexed Property to find out which one it may be. Hope that helps!

Joe Miller
joe.miller@flotech.net

 

[PamelaG]

Hi Kerry!

Just wanted to add a couple of things. First of all, remember that you can uniquely index any field, not just the primary key!

If you really think that you have made no structural changes to the database, and you can't find any indexed fields as Joe suggested, then you may have a corrupt application. It happens more than we would like! Try creating a new database and importing your tables. Can you now enter data? Now add your queries and forms and see if the problem disappears. This is so often the solution!

Pamela

http://www.accessinsight.com

 

[tlbroadbent]

Is one of the columns in the index an autonumber? When you use an AutoNumber field to generate counter values, a duplicate value may be generated.

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database

http://support.microsoft.com/support/kb/articles/Q257/4/08.ASP

ACC2000: Append Query Results in Duplicate AutoNumbers

http://support.microsoft.com/support/kb/articles/Q209/7/10.ASP

Terry

http://members.home.net/tlbroadbent/prog.htm

_____________________________________
Man's mind stretched to a new idea never goes back to its original dimensions. - Oliver Wendell Holmes

 

[KerryL]

Thanks to Joe, Pamela and Terry for your assistance. So far, however, I haven't been able to figure out the source of the problem.

To answer Joe's question, I do have other indexed fields. There are 6 indexed key fields that don't allow duplicates (4 = auto-number) and one regular indexed field that doesn't allow duplicates. (All other indexed fields do allow duplicates.)

Terry suggested MS article #Q257/4/08, so I downloaded a newer version of the Jet Engine, but that didn't help either.

I guess my confusion comes from not understanding how I can verify which of the indexed fields the form is attempting to add duplicate data into. It seems like a Catch-22 to me; the tables don't actually contain duplicate data in the indexed and/or key fields because I'm not allowed to save records with duplicate data. But without saving the data I can't open the tables to verify which field the error originates from by finding the duplicate data.

For now I will concentrate on the non-key indexed field that doesn't allow duplicates to see if that's where the problem lies.

If anyone can think of another possibility or give me an idea of how I can zero in on this problem I welcome your input. (Hey, I'm not proud!)

Thanks again,
Kerry

 

[JoeMiller]

When are you getting the error? DUring an append query? Or when the user enters data? How about making a dummy table with no data that is the exact structural copy of the table giving you the error. Then do whatever it is you're doing and see what records it's adding, then you'll know where the duplication is coming in and fix it.

Joe Miller
joe.miller@flotech.net

 

[KerryL]

Thanks Joe...

The error occurs when the user attempts to save the record after making selections in combo boxes and entering data in a couple other fields. I believe there are a couple different tables that are updated, but I'll follow your suggestion and try to narrow it down.

Kerry

 

[Guest_imported]

Try closing out of the table, reopening it and make the changes again. See if this helps.

 

[MoGryph]

This might sound like a lot of work extra code, just to find the error, but I'd consider doing it if you positively can't find the answer.

Create a copy of the form, and put a new button on it. Have the code of the button create the new record, and update each field seperately.

STOP
Dim DB as Database
Dim RST as Recordset
Set DB = CurrentDB
Set RST = DB.OpenRecordset("SELECT * FROM

tablename

;")

RST.AddNew
RST!

field1

 =

Control1.Value

RST.Update
RST!

field2

 =

Control2.Value

RST.Update
RST!

field3

 =

Control3.Value

RST.Update

Set RST = Nothing
Set DB = Nothing

I have the STOP in there so that you can get to the code module, and then step-through the code, and you should be able to see which field is causing the error. I hope.

B.T.W. - I use Access 97, not 2000. If you have a problem with the code not running- that specifically, not recognizing a Database or Recordset object, you may need to set a reference to the Microsoft DAO 3.5 Object Library (I don't know how to work with records any other way!)

-MoGryph
[8O)

 

[Garridon]

It may be a corrupted index. I have the same problem on my database. Run repair and compact. If that doesn't work, then import the database into a new one and run repair and compact. That might fix it permanently or not. If not, just run repair and compact once a day.

Linda Adams
Visit my web site for writing and Microsoft Word tips:

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com