How did I violate the referential integrity?

[BananaJelly]

Products for Access Forum.zip

I have Access 2007.

I have two tables: "Mix Recipes" and "Mix Trials." The "Recipe ID," which is also the Primary ID, from Mix Recipes has a relationship with the "Final Mix ID" from Mix Trials. The relationship is already established with the "1" labeled next to "Recipe ID" and the infinity symbol next to "Final Mix ID."

I copied an entry from the Recipe ID field and pasted it in one of the Final Mix ID entries. Then I get this error:

You cannot add or change a record because a related record is required in table <name>. (Error 3201)

You tried to perform an operation that would have violated referential integrity rules for related tables. For example, this error occurs if you try to change or insert a record in the "many" table in a one-to-many relationship, and that record does not have a related record in the table on the "one" side.
If you want to add or change the record, first add a record to the "one" table that contains the same value for the matching field.

I don't get it. I copy/pasted one entry to the next.

I should mention that the other entries in that same row in Mix Trials has names and other entries already. I just deleted the entries under "Final Mix ID," so I can enforce the referential integrity when I create the relationship.

Now what could be the problem? Is it because I added an option under format?



Also, I realize I cannot enforce referential integrity when I use LookUp. How else do I add a scroll thing that lists unique values from Recipe ID of Mix Recipes in Final Mix ID of Mix Trials?

[June7]

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

[BananaJelly]

Okay. I attached the zip file. Please see first post.

[June7]

I NEVER set lookups in table. Many experienced developers don't bother with relationships because they use form design and code to manage data integrity.

You have a link between MixRecipeID (PK in MixRecipes) and FinalMixID (FK in MixTrials) with the enforce referential integrity option set to yes. This linking will automatically save MixRecipeID to FinalMixID field when data entered into MixName field in a table/subtable (or form/subform). Therefore, apparently, must open MixRecipes table to do data entry into both tables. Recommend you use form/subform arrangement instead of working directly with tables. I have NEVER set up db for data entry on tables and queries.

Recommend no spaces or special characters/punctuation (underscore is exception) in naming convention.

[BananaJelly]

June 7, I don't think they are connected by a LookUp table now, right? I thought that might be the problem, so I deleted that entire field and created a new one without the LookUp.

I still don't understand what I am doing wrong. Can you revise my file with your recommendations, so I can understand what I did wrong? Thanks.

[June7]

No lookups in your db at this time, I only made that comment because you mentioned lookups.

I don't know your data nor your business process so I am not going to try designing. I will refer you to a tutorial site that might help you better understand relational database concepts. http://www.rogersaccesslibrary.com/

[BananaJelly]

June7, the file I attached is pretty simple; I deleted a lot of stuff to keep it simple. There are only two tables and I'm just linking one field from one table to the other. Anyway, I'll look into the tutorial.