Duplicate Values Error?

[batowl]

Hi All,



I have a new database made of 29 tables. Users enter data via form that consists of one main form and several subforms. Users keep running into the following error when they move on from the fields on the main form and first begin to enter data on any one of the subforms (they can enter data into one field on the subform but as soon as they attempt to leave that field, the following error message appears):

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 checked the tables and I do not believe that I have any inappropriate indexed fields (very few in fact, and none that do not allow duplicate values...except the primary key)

Users can get around this error message and fully enter data for a given record if they:
1. Undo the Current Field/Record (to get rid of the value they entered into the first field on the subform)
2. Close the form
3. Re-open the form

Obviously that is not an appropriate solution, but I do not know what is causing this problem. There is a one-to-one relationship between the parent table and the child table(s) that sit on the subform(s), referntial integrity is enforced, and cascade update and delete are checked.

Any thoughts on what the problem and solution might be? Thank you.

[ranman256]

Your table is not indexed correctly...the users are entering a duplicate index values.
OR
the master form and subform do not connect correctly using the master field vs child field. See if these properites are correct. They could also cause a duplicate index.

Master table vs child table prob. should not be 1 to 1. A person (1 master) can have many phones (many childs). Is this what your tables are?

[batowl]

The master and child tables are all 1:1 because the master can only have one child. If I enter one value into the master table (a unique value in the primary key field) via the main form and one value in any child table (call is Table X) via a subform, I get this error. Going to the backend of this database to examine the main table and Table X, the only values that are indexed are the primary keys (both have the same primary key since it is 1:1).

Could the issue be with how the query that made these forms is set up? Did I make a mistake by not including the primary key of each child table in the query?

[John_G]

You should not be getting that error when just moving from one field to another - you should only see it when moving to a new record.

Do you have any code in the subform that might be causing the problem?

[ranman256]

Yes , it is set to 1:1 but is that what you want?
or do you need more childs per master?

[June7]

Cannot link on two autonumber fields, if that is what you are doing.

One table can have autonumber PK and the related table would have a number field to hold FK. Although the FK field is holding the PK value from master table, it can also be defined as a PK in the child table but doesn't have to be - can just set it to Index Yes No Duplicates.

If these tables are 1-to-1, why not just one table? Will there always be related records in both tables?

[batowl]

No linking to autonumber fields. No code in the subform. And I made these 1:1 because parent can only have one child as intended. If I put all the fields in one master table it would exceed the limits of Access.

Did I mess up by only including the PK field from the parent table when buidling this subform? Here is the SQL statement from the Query Builder: (Campylobacter_VDH.State_Case_ID is the PK on the parent table)

SELECT Campylobacter_VDH.State_Case_ID, Campylobacter_Dairy.Drink_Raw_Milk, Campylobacter_Dairy.Raw_Milk_Origin, Campylobacter_Dairy.Raw_Milk_Purchased, Campylobacter_Dairy.Raw_Milk_Cheese, Campylobacter_Dairy.Raw_Milk_Yogurt, Campylobacter_Dairy.Raw_Milk_Description, Campylobacter_Dairy.Pasteurized_Milk, Campylobacter_Dairy.Pausterized_Milk_Cheese, Campylobacter_Dairy.Artisinal_Cheese, Campylobacter_Dairy.Cheese_Curds, Campylobacter_Dairy.NEDSS_Consume_Raw_Milk_Product , Campylobacter_Dairy.NEDSS_Raw_Milk_Product_Purchas ed, Campylobacter_Dairy.NEDSS_Raw_Milk_Product_Source, Campylobacter_Dairy.NEDSS_Raw_Milk_Description, Campylobacter_Eggs.Eat_Eggs, Campylobacter_Eggs.Eggs_Raw_Runny, Campylobacter_Eggs.Eggs_Home, Campylobacter_Eggs.Egg_Brand_Type, Campylobacter_Eggs.Eggs_Purchase_Location, Campylobacter_Eggs.Eggs_Outside, Campylobacter_Eggs.Eggs_Outside_Location, Campylobacter_Eggs.Eat_Raw_Egg_Product, Campylobacter_Eggs.Eat_Raw_Product_Description, Campylobacter_Eggs.NEDSS_Consume_Raw_Eggs, Campylobacter_Eggs.NEDSS_Raw_Egg_Description
FROM (Campylobacter_VDH LEFT JOIN Campylobacter_Dairy ON Campylobacter_VDH.State_Case_ID = Campylobacter_Dairy.State_Case_ID) LEFT JOIN Campylobacter_Eggs ON Campylobacter_VDH.State_Case_ID = Campylobacter_Eggs.State_Case_ID;

[June7]

If you are exceeding the 255 field limit, I suspect non-normalized data structure and that query supports my suspicion.

If you want to provide db for analysis, follow instructions at bottom of my post.

[John_G]

How is the SQL you show above used? If it is for one of the sub-forms, that is likely the issue. Have the record source for each of the subforms select from only the table
that form is dealing with.

In the main form, set the Link Master fields and Link Child Fields properties of the subform containers to the field they have in common (State_Case_ID). Include State_Case_ID on each of the sub-forms (just so you can see what it is), but set the Locked property to Yes so you cannot edit it.

You will see that whenever you add a new record in the sub-form, the link field State_Case_ID is automatically populated (you are only adding 1 I know, but the concept is the same). No need to worry about fancy joins. If you make State_Case_ID the PK of all the tables, that will effectively ensure all the relationships are 1:1.