Record(s) cannot be added; No corresponding record on the 'one' side

[whisp]

Hey!

I've created two tables, one containing order data, the other additional order data. Not every order has additional order data.

First i've created them with no specific relationship and filling in data via form worked fine. If i added additional data, a new record in the additional order data table was created automatically.

Later i changed those tables to a "one to one" relationship by setting the long int field that links to the order data table to no duplicates. I just did it because i thought that's how it should bew. But since then i can't add additional order data via the form anymore, but get the error "Record(s) cannot be added; No corresponding record on the 'one' side" instead. I could just revert back to the one to many relationship, but it bothers me.

Any hints? Thank you!

whisp

 

[jdraw]

Please describe what it is you are dealing with and what you are trying to do in plain English. Often, when the readers understand your situation/issue, many suggestions will be posted.

 

[whisp]

I'm not english, the above text is the best i can do - i thought it's understandable and don't see what i could fix - i'm sorry about it -, so let me try to explain it in different and more words.

There are two tables with data that belongs to each other. The one table contains production data, like a material, components, production status. The second table contains additional production data that is only needed when the production is a resource-production (harvesting). It contains data of that specific resource. Per production there can be one or no entry in the resource data table.

First i didn't create a specific relationsship from production data to resource data, so it was default, one to many: one production data to many resource data. This wasn't a problem at all, since the user uses a form to enter this data and has no way to enter multiple resource data entries for the same production data entries, and foremost it worked, without the error message mentioned in the title.

But actually it's a one to one relationsship: One production data cannot be linked with more than one resource data (but it could not be linked with any resource data, it's optional). Because of this i changed the relationship to one-to-one: The production data table has an automatic generated primary key, the name of the field is [Order ID]. The production data table contains no link to the resource data table, but the resource data table has a field that links to production data's [Order ID], the field is called prodID. So i changed prodID to indexed (no duplicates).

But since i changed prodID to indexed (no duplicates), entry of resource data in the form doesn't work anymore, instead, when i try to enter something i get the in the title mentioned error message. But instead of changing back to the one to many relationship i would like to know how it's done properly, with the one to one relationship, and hope that i can get some hints here.

Thank you!

whisp

 

[Pat Hartman]

The relationship is defined in the wrong order. Just as 1-many relationships have a "parent" and a "child" so do 1-1 relationships and the "parent" (which should be the table with the autonumber PK) must be added before any "child" can be added.

 

[whisp]

I'm trying to add that data related to an existing record, the parent - the production data - is definately there. The data i would like to add to the "additional data" table is however also a key to another table's entry (combobox selection). Can this be the problem? It's a required field, duplicates are allowed. I'm used to access creating a new record, when data is added to the table, but i never played with relationships before.

Or is there anything that sometimes needs to be changed in the form's select query once a table relationship changed from one-to-many to one-to-one? Or any other form properties? (The table relationships in the query designer are ok, they're reflecting the true table relationships, but i don't know much about the meanings of the query properties.)

 

[Pat Hartman]

The relationships of other tables to the 1-1 table set should always be to the parent table and never to the child table.

Say you have a Customer table and a CustomerSpecial table with a 1-1 relationship. The Address table should be related to the Customer table not the CustomerSpecial table.

 

[whisp]

The additional table contains resource harvesting specific data (data that is only needed when a production is related to resource harvesting). The field i have spoken of in the last post was the harvesting machine and it doesn't belong to the common production data, it belongs to the harvesting data only. And it's not the harvesting machines table linking to the harvesting data, it's the harvesting data linking to the harvesting machines, the standard scenario for what the form wizard creates a combobox for.

There are three more things the user choses there: A resource and a harvestesting head, both also links to other tables, and a resource density, which is directly stored in the harvesting data table (as opposed to the others which are stored as links to other tables entries).

(I get the same error message when trying to enter a value into the density-field, btw, also if i revert the relationship of the production data to the harvesting data to one-to-many, it works: The harvesting machines and harvesting heads tables are probably not a problem, but foremost not one i could solve by linking them from the production order table, where they not belong to.)

 

[Pat Hartman]

Perhaps if you post the database (remove any sensitive data but leave at least a few records so we can see what they should be).

 

[whisp]

Sorry for the late reply, and thanks for yours. Maybe i will understand the problem once i am more experienced, maybe it's just another access bug. Either way, I decided to revert it back to a one to many relationship.