You cannot add or change a record because a related record is... (1 Viewer)

setis

Registered User.
Local time
Today, 09:21
Joined
Sep 30, 2017
Messages
127
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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:21
Joined
Feb 19, 2002
Messages
42,970
You need to remove 0 as the default for the foreign key. MS has flip flopped on this several times and sadly has gone to the lowest common denominator. Most experts know there is a difference between 0 and null and would prefer that numeric fields default to null just as text fields do. The problem is that the issue is less clear when it comes to things like money fields or count fields. Most users do arithmetic with those fields and so run into a problem since they don't know how to handle nulls (use the Nz() function). Also since these same people don't understand the importance of referential integrity letting all numeric values default to 0 seemed the correct solution. I disagree but who am I to argue with MS.

Bottom line - when you design a new table you have to make the conscious decision to modify the default values of numeric fields so that anything used as a foreign key defaults to null and the others to either 0 or null depending on how you expect to use the fields and whether you need to distinguish between 0 and null.

Also for foreign keys, you have to decide whether or not to make them required. For example, in the OrderDetails table, the OrderID would ALWAYS be required because it makes no sense to allow details for a non existent order. However, in some applications it makes sense to allow CustomerID in the Order table to be null if you are recording a walk-in sale or for StateCD to be null if the address itself is not required since you are not shipping the order.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:21
Joined
May 7, 2009
Messages
19,169
Edit the relation. Click join type...
Choose include records from the otherother table (not table services).
This will create a left join.
 

setis

Registered User.
Local time
Today, 09:21
Joined
Sep 30, 2017
Messages
127
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

Immoderate Moderator
Staff member
Local time
Today, 11:21
Joined
Feb 28, 2001
Messages
26,999
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:21
Joined
Feb 19, 2002
Messages
42,970
Here's a picture of the table definition.
 

Attachments

  • TableDefault.JPG
    TableDefault.JPG
    41.5 KB · Views: 485

ifiaz

New member
Local time
Today, 09:21
Joined
Jul 13, 2019
Messages
1
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:21
Joined
Feb 19, 2002
Messages
42,970
Welcome aboard.

You are very welcome. I love it when my old posts help someone. My husband used to occasionally have to help his users who used Access and he really got a kick out of finding an answer I wrote a long time ago.
 

Users who are viewing this thread

Top Bottom