2

I have read numerous thread on the above question, but none of the answers satisfy the problem. My problem is two tables that are linked to each other (in a one to one relationship) using an "ID" field which is also the primary key. When I try to enter a record in the the main table (Don't know if Access knows this is the main table) the I get the following error:

"You cannot add or change a record because a related record is required in table"

Could anyone please help me with this?

Thanks

Michele La Ferla
  • 6,775
  • 11
  • 53
  • 79
Marnu123
  • 77
  • 2
  • 10

2 Answers2

3

A "One-To-One" relationship is really a One-To-Zero_or_One relationship, but the table diagrams on the Relationships page in Access do not make it clear which table is the "parent":

Tables.png

However, if you right-click on the line joining the tables and choose "Edit Relationship..." you will see

EditRelationships.png

In this case [Table1] is the "parent" table and [Table2] is the "child" ("Related") table, so we must insert a row into [Table1] before trying to insert the corresponding row into [Table2].

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

The issue seems to be occuring because you are trying to set the primary key of your table as a foreign table.

What you need to do is create a new column in the 2nd table and link it to the id of the first table, therefore creating a relationship.

Let's take the following simple example since you haven't mentioned the names given to your tables.

In a university environment, you would have the table LECTURER which is linked to the table SUBJECT, so that a lecturer can teach a subject. The relationship between the 2 tables would be so:

LECTURER TABLE

LECTURER_ID | LECTURER_NAME | LECTURER_ADDRESS

SUBJECT TABLE

SUBJECT_ID | SUBJECT_NAME | SUBJECT_DESCRIPTION | SUBJECT_LECTURER_ID

The link between these two tables would be between the SUBJECT_LECTURER_ID from the SUBJECT table and the LECTURER_ID from the LECTURER table. This is a one to one relationship since we are assuming that each subject in this particular university is only taught by one lecturer, and a lecturer can only teach one single subject.

Hope this helps :)

Community
  • 1
  • 1
Michele La Ferla
  • 6,775
  • 11
  • 53
  • 79
  • That works nicely. Thank you. But how do you link these tables so that a record will be created in the SUBJECT_ID field containing default values? I have a database to keep track of users and their log in information. I am trying to add a record to the "LOG" table when a new user registers – Marnu123 Oct 26 '14 at 13:34
  • I think and insert needs to be done here to handle a new user who registers into the system. This is not handled by relationships. Relationship link the existing data in one table to already existing data in a second table. – Michele La Ferla Oct 26 '14 at 13:37
  • Okay thanks. I have tried using insert through code, but I still get the error. I am working with Delphi 7. This is for a school project, thus I must use this version – Marnu123 Oct 26 '14 at 13:40
  • What is the SQL you are using to insert the new record in the LOG table? – Michele La Ferla Oct 26 '14 at 13:41
  • I am not using SQL at the moment. A simple ADOTable.insert method is called with all the information regarding a user inserted after that – Marnu123 Oct 26 '14 at 13:44
  • I never worked with Delphi, but maybe this link can help you: `http://www.delphipages.com/forum/showthread.php?t=176191` – Michele La Ferla Oct 26 '14 at 13:50