Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-06-2017, 06:50 AM   #1
setis
Newly Registered User
 
Join Date: Sep 2017
Posts: 127
Thanks: 106
Thanked 1 Time in 1 Post
setis is on a distinguished road
You cannot add or change a record because a related record is...

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 by setis; 10-06-2017 at 07:10 AM.
setis is offline   Reply With Quote
Old 10-06-2017, 07:40 AM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,772
Thanks: 13
Thanked 1,498 Times in 1,424 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: You cannot add or change a record because a related record is...

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
setis (10-07-2017)
Old 10-06-2017, 07:47 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,534
Thanks: 57
Thanked 2,419 Times in 2,319 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: You cannot add or change a record because a related record is...

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
setis (10-07-2017)
Old 10-07-2017, 01:48 AM   #4
setis
Newly Registered User
 
Join Date: Sep 2017
Posts: 127
Thanks: 106
Thanked 1 Time in 1 Post
setis is on a distinguished road
Re: You cannot add or change a record because a related record is...

Quote:
Originally Posted by Pat Hartman View Post
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.

Quote:
Originally Posted by arnelgp View Post
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.
setis is offline   Reply With Quote
Old 10-07-2017, 08:04 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,066
Thanks: 81
Thanked 1,592 Times in 1,478 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: You cannot add or change a record because a related record is...

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
setis (10-08-2017)
Old 10-07-2017, 09:01 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,772
Thanks: 13
Thanked 1,498 Times in 1,424 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: You cannot add or change a record because a related record is...

Here's a picture of the table definition.
Attached Images
File Type: jpg TableDefault.JPG (41.5 KB, 261 views)
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
setis (10-08-2017)
Old 10-08-2017, 11:19 PM   #7
setis
Newly Registered User
 
Join Date: Sep 2017
Posts: 127
Thanks: 106
Thanked 1 Time in 1 Post
setis is on a distinguished road
Re: You cannot add or change a record because a related record is...

Quote:
Originally Posted by Pat Hartman View Post
Here's a picture of the table definition.
I found it and removing the "0" made it work. Thank you so much, sir!

setis is offline   Reply With Quote
Old 10-17-2017, 07:28 AM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,772
Thanks: 13
Thanked 1,498 Times in 1,424 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: You cannot add or change a record because a related record is...

You're welcome
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 07-13-2019, 05:00 AM   #9
ifiaz
Newly Registered User
 
Join Date: Jul 2019
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
ifiaz is on a distinguished road
Re: You cannot add or change a record because a related record is...

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
ifiaz is offline   Reply With Quote
Old 07-22-2019, 10:17 AM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,772
Thanks: 13
Thanked 1,498 Times in 1,424 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: You cannot add or change a record because a related record is...

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error: ou cannot add or change a record because a related record is required in table Zak14 General 10 08-14-2014 10:55 PM
You cannot add or change a record because a related record is required in table kiki88 Tables 12 11-18-2012 04:17 PM
[SOLVED] You cannot add or change a record because a related record is required in table 'MolT dsigano General 2 08-14-2008 11:19 PM
help with form and error msg Add Or Change A Record Because A Related Record Is Requi miken5678 Forms 4 07-28-2008 07:28 AM
"You cannot add or change a record because a related record is required in table.... sclarkin Forms 10 08-16-2006 06:44 PM




All times are GMT -8. The time now is 06:46 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World