Related Record Error When Saving Form

[dashingirish]

Argh! I'm screwing up something with my table relationships but I just can't figure it out.

Basically, I've got a main table with several tables linked to it via one-to-one relationships. I broke the data into several smaller tables (11, to be precise) so as to keep it manageable.

The problem is, when I enter data into a form which contains fields from all tables, I get a message stating that I cannot add or change a record because a related record is required in one of the tables.

I've messed about with the relationships but I'm not having any luck setting up relationships that enables a record in a smaller table to stay linked to a single record in the main table. If I force referential inegrity, I get that error.

I'm guessing - like almost all my problems - this is an easy one, but I'm just not seeing the solution.

Thanks, as ever, for your help and time. I always appreciate it.

 

[jzwp22]

Typically one-to-many relationships are handled with a main form/subform design. The main form would be based on the table that makes up the one side of the relationship while the subform is based on the table that makes up the many side of the relationship.

Can you post your table structure? or your database (with any sensitive data removed)?

 

[gemma-the-husky]

one of your subtables has a key value that is not present in the main/master table

use a unmatched query to find it

and personally, i think trying to use 11 one-to-one tables is hardly worth the effort. just leave everything in one table

 

[dashingirish]

Thanks very much for the thoughtful replies. I figured out that there was no way to place the data into the main form without making a sub-form - a scenario I don't want. I can leave much of the data in the one table (thank you for suggesting that), but there's so many fields I did have to break a chunk off. However, that WILL work as a sub-form, so I'm in much better shape now. Thanks!