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
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 :)