Cannot add or change a record because a related record is required in table 'COMPANY'
I think my Contact List database design is correct, but there is an error. Access 2013 is telling me:
You cannot add or change a record because a related record is required in table 'COMPANY'
I'm new to Access and this is for a class called "Computer Concepts II". I've already done my assignment, and followed the instructions meticulously. This course is held online only and I have tried 3 times in 3 different ways but haven't been able to get any feedback from the instructor.
Here is the breakdown of the things I have tried:
- As the error seemed to indicate, I attempted to fix the issue by closing the PERSON table and then opening and entering data in the FAMILY table. I made up 3 families is different states, with my real address and surname entered as the 4th. Saved FAMILY table.
- Opened PERSON table again and began to enter data from left to right (screenshot). Upon using arrow down key or double-clicking to autofit column widths I got the the error at the beginning, referencing the COMPANY table.
Found and read the following seemingly related posts, but none of them offer the help I was looking for:
"You cannot add or change a record because a related record is required", but related record exists?
You cannot add or change a record because a related record is required in table
Business Rules
- A person must have only one family, but a family can have many people.
- A person can work for one or zero companies.
- A company can have zero or many people.
- For MS Access purposes PERSON is the strong table.
MY ERD Diagram
BUILT THE TABLES
- Built PERSON table.
- Built COMPANY table.
- Built FAMILY table.
CREATED RELATIONSHIPS
- Created relationships between tables (see screenshot attached). For optionality, set PERSON as the "strong" table.
- Originally had ID-FAM and LNAME fields in FAMILY table as both required and indexed.
- Originally had ID-PER, ID-FAM_FK, & ID-COM_FK in PERSON table as indexed.
- Originally had ID-COM as the only indexed and required field in COMPANY table.
ENCOUNTERED 'RELATED RECORD' ERROR WHILE ENTERING DATA IN PARENT TABLE
- As indicated in the Using Access 2010 document, I started by entering data in my parent table (PERSON) first. However, when I finished filling all fields in the first row and pressed the "ARROW DOWN" key to advance to the next row, I got an error message saying
"You cannot add or change a record because a related record is required in table 'FAMILY'"
Well, a COMPANY entry is completely optional and not required in any way, so that's a bit confusing. I'm not sure what I'm doing wrong or where to go from here. I did not know how to use Access before this class but I think I have a pretty good handle on it now, and I've followed the instructions meticulously. Thus, I believe the issue is a misconfigured setting somewhere.
Answer
SOLVED!
I discovered this on my own before seeing Sergey S's comment, but as he pointed out, there should be no value in the "Default" property field. After I discovered the solution I posted the following to my class's discussion board:
It turns out that Access was automatically setting a default value for my foreign keys [FK] of zero ("0"). I didn't realize it, but what was happening was even though a person wasn't required to have a company, because "0" was in the company field for my person record that was what was causing the error. The reason? I suspect that the database was looking for a company record with an ID of "0". Since my company records start at 2 the "0" record was never found, causing the error.
SOLUTION
The way I solved this was simply to set go into the default field for my 2 foreign keys in the design view, delete the zeros, and make sure there was no default values. Or, another way to put it is: ensure there are no default values for your foreign keys.
I was able to add 3-4 rows of data for each table and I even ran a basic query to test the database. It seems to be working perfectly.
Thanks to everyone who contributed!