You cannot add or change a record because a related record is...

[setis]

Dear all,
I have seen this question posted in several forums but I can't simply understand the answers and I'm sure that it's very simple.


I have a form filling the table "cases" where a combo box in a form picks the options from a table called "services". There is a relationship enforcing integrity (one to many)
The problem is that services is optional and if I don´t fill anything, when attempting to create a new record, I get the error "You cannot add or change a record because a related record is required in tblServices"

I can see that the problem is that if i don't choose and option, access is setting the default value as "0" and there isn't any "0" key in the table "services"
In the combo box propertied the "default value" option is empty.

I'm not sure what I am missing.
It obviously work if I unmark the "enforce data integrity" in the relationship.

 

[arnelgp]

Edit the relation. Click join type...
Choose include records from the otherother table (not table services).
This will create a left join.

 

[setis]

You need to remove 0 as the default for the foreign key.

Thanks for your answer. Pardon my ignorance but how do I do this? I coudn't find any option in the table properties.

Edit the relation. Click join type...
Choose include records from the otherother table (not table services).
This will create a left join.

Thanks but this didn't work in my case.

 

[The_Doc_Man]

The problem is that your relationship "arrow" must be pointing the wrong way.

The record to which the optional service applies must be the "one" side of the relationship. The service record (that can sometimes be zero) must be the "many" side.

If this is not possible, then you have a design flaw in the data layout.

 

[setis]

Here's a picture of the table definition.

I found it and removing the "0" made it work. Thank you so much, sir!

 

[ifiaz]

This was exactly it. I had the same problem and the solution was changing the "Default Value" from 0 to Null in table design of the child table.

I have a main table (pk main_data_id). and 2 child tables. tbl1 (fk data_id), tbl2 (fk data_id)

one to one relationship for child tables (main to tbl1 and main to tbl2)

i have a query that links all of the three tables. and when i create a record in the query without typing anything in child data_id (fk) it works automatically data_id gets filled in from main_data_id (pk).

But in a form, it kept giving error "You cannot add or change a record because a related record"

it worked for tbl1 but not for tbl2 in the form.

then i noticed that tbl1 has default set to "null", tbl2 has default set to 0. that's why tbl1 worked.

after seeing this forum posting.. the problem resolved.. after 2 hours fighting with it.

Thank you.. after 17 years. @Pat Hartman