Why is the information from my foreign key not being updated?
I have a table that lists a students information.
In the table only the student's name is local, the rest of the columns refer to foreign keys from other tables that have information regarding addresses and legal guardians.
The problem I have is when updating information in the address and guardian table the foreign key does not update in the student table.
Why is this?
This is how the table should update:
This is how the table updates when entering new data into each table:
ms-access access-vba
edited Nov 26 '18 at 17:29
Lee Mac
4,35431541
add a comment |
I have a table that lists a students information.
In the table only the student's name is local, the rest of the columns refer to foreign keys from other tables that have information regarding addresses and legal guardians.
The problem I have is when updating information in the address and guardian table the foreign key does not update in the student table.
Why is this?
This is how the table should update:
This is how the table updates when entering new data into each table:
ms-access access-vba
edited Nov 26 '18 at 17:29
Lee Mac
4,35431541
add a comment |
I have a table that lists a students information.
In the table only the student's name is local, the rest of the columns refer to foreign keys from other tables that have information regarding addresses and legal guardians.
The problem I have is when updating information in the address and guardian table the foreign key does not update in the student table.
Why is this?
This is how the table should update:
This is how the table updates when entering new data into each table:
ms-access access-vba
edited Nov 26 '18 at 17:29
Lee Mac
4,35431541
I have a table that lists a students information.
In the table only the student's name is local, the rest of the columns refer to foreign keys from other tables that have information regarding addresses and legal guardians.
The problem I have is when updating information in the address and guardian table the foreign key does not update in the student table.
Why is this?
This is how the table should update:
This is how the table updates when entering new data into each table:
ms-access access-vba
ms-access access-vba
edited Nov 26 '18 at 17:29
Lee Mac
4,35431541
edited Nov 26 '18 at 17:29
Lee Mac
4,35431541
edited Nov 26 '18 at 17:29
Lee Mac
4,35431541
edited Nov 26 '18 at 17:29
Lee Mac
4,35431541
edited Nov 26 '18 at 17:29
Lee Mac
4,35431541
4,35431541
asked Nov 26 '18 at 16:18
user10707314
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The foreign keys in your students table are used to uniquely identify a record using the primary keys in your address & guardian tables respectively.
Therefore, modifying the content of such records in your address & guardian tables will not change the value of the foreign key in the students table, as the same record is being identified.
Furthermore, adding new records to the address & guardian child tables will not automatically populate the foreign key of the students parent table, as there is no way to determine which record from the child tables should be selected.
To populate such fields, you should create a form whose record source is set to your students table, with appropriate subforms whose record sources point to each of the address & guardian child tables, and which are linked to the main form by the foreign keys in the parent table and primary keys in the child tables.
Then, when you create a new record through the subform, you can associate it with the active record displayed by the main form and the foreign key should be automatically populated by virtue of the fact that it is linked to the subform (providing you have referential integrity enabled).
answered Nov 26 '18 at 17:35
Lee MacLee Mac
4,35431541
What would recommend I do to have the foreign keys populate in the parent table when new data is added to the child tables?
– user10707314
Nov 26 '18 at 17:45
@JordanBelson I have updated my answer
– Lee Mac
Nov 26 '18 at 18:07
I see, Is there any way to do this without using subforms? Perhaps queries?
– user10707314
Nov 26 '18 at 18:39
@JordanBelson How would the query determine which child record should be assigned to the parent record?
– Lee Mac
Nov 26 '18 at 18:40
They cannot. All of the information entered in the main form is entered and submitted as one. Can we use VBA code to have all of them associate with one another?
– user10707314
Nov 26 '18 at 18:48
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53485168%2fwhy-is-the-information-from-my-foreign-key-not-being-updated%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The foreign keys in your students table are used to uniquely identify a record using the primary keys in your address & guardian tables respectively.
Therefore, modifying the content of such records in your address & guardian tables will not change the value of the foreign key in the students table, as the same record is being identified.
Furthermore, adding new records to the address & guardian child tables will not automatically populate the foreign key of the students parent table, as there is no way to determine which record from the child tables should be selected.
To populate such fields, you should create a form whose record source is set to your students table, with appropriate subforms whose record sources point to each of the address & guardian child tables, and which are linked to the main form by the foreign keys in the parent table and primary keys in the child tables.
Then, when you create a new record through the subform, you can associate it with the active record displayed by the main form and the foreign key should be automatically populated by virtue of the fact that it is linked to the subform (providing you have referential integrity enabled).
answered Nov 26 '18 at 17:35
Lee MacLee Mac
4,35431541
What would recommend I do to have the foreign keys populate in the parent table when new data is added to the child tables?
– user10707314
Nov 26 '18 at 17:45
@JordanBelson I have updated my answer
– Lee Mac
Nov 26 '18 at 18:07
I see, Is there any way to do this without using subforms? Perhaps queries?
– user10707314
Nov 26 '18 at 18:39
@JordanBelson How would the query determine which child record should be assigned to the parent record?
– Lee Mac
Nov 26 '18 at 18:40
They cannot. All of the information entered in the main form is entered and submitted as one. Can we use VBA code to have all of them associate with one another?
– user10707314
Nov 26 '18 at 18:48
|
show 1 more comment
The foreign keys in your students table are used to uniquely identify a record using the primary keys in your address & guardian tables respectively.
Therefore, modifying the content of such records in your address & guardian tables will not change the value of the foreign key in the students table, as the same record is being identified.
Furthermore, adding new records to the address & guardian child tables will not automatically populate the foreign key of the students parent table, as there is no way to determine which record from the child tables should be selected.
To populate such fields, you should create a form whose record source is set to your students table, with appropriate subforms whose record sources point to each of the address & guardian child tables, and which are linked to the main form by the foreign keys in the parent table and primary keys in the child tables.
Then, when you create a new record through the subform, you can associate it with the active record displayed by the main form and the foreign key should be automatically populated by virtue of the fact that it is linked to the subform (providing you have referential integrity enabled).
answered Nov 26 '18 at 17:35
Lee MacLee Mac
4,35431541
What would recommend I do to have the foreign keys populate in the parent table when new data is added to the child tables?
– user10707314
Nov 26 '18 at 17:45
@JordanBelson I have updated my answer
– Lee Mac
Nov 26 '18 at 18:07
I see, Is there any way to do this without using subforms? Perhaps queries?
– user10707314
Nov 26 '18 at 18:39
@JordanBelson How would the query determine which child record should be assigned to the parent record?
– Lee Mac
Nov 26 '18 at 18:40
They cannot. All of the information entered in the main form is entered and submitted as one. Can we use VBA code to have all of them associate with one another?
– user10707314
Nov 26 '18 at 18:48
|
show 1 more comment
The foreign keys in your students table are used to uniquely identify a record using the primary keys in your address & guardian tables respectively.
Therefore, modifying the content of such records in your address & guardian tables will not change the value of the foreign key in the students table, as the same record is being identified.
Furthermore, adding new records to the address & guardian child tables will not automatically populate the foreign key of the students parent table, as there is no way to determine which record from the child tables should be selected.
To populate such fields, you should create a form whose record source is set to your students table, with appropriate subforms whose record sources point to each of the address & guardian child tables, and which are linked to the main form by the foreign keys in the parent table and primary keys in the child tables.
Then, when you create a new record through the subform, you can associate it with the active record displayed by the main form and the foreign key should be automatically populated by virtue of the fact that it is linked to the subform (providing you have referential integrity enabled).
answered Nov 26 '18 at 17:35
Lee MacLee Mac
4,35431541
The foreign keys in your students table are used to uniquely identify a record using the primary keys in your address & guardian tables respectively.
Therefore, modifying the content of such records in your address & guardian tables will not change the value of the foreign key in the students table, as the same record is being identified.
Furthermore, adding new records to the address & guardian child tables will not automatically populate the foreign key of the students parent table, as there is no way to determine which record from the child tables should be selected.
To populate such fields, you should create a form whose record source is set to your students table, with appropriate subforms whose record sources point to each of the address & guardian child tables, and which are linked to the main form by the foreign keys in the parent table and primary keys in the child tables.
Then, when you create a new record through the subform, you can associate it with the active record displayed by the main form and the foreign key should be automatically populated by virtue of the fact that it is linked to the subform (providing you have referential integrity enabled).
answered Nov 26 '18 at 17:35
Lee MacLee Mac
4,35431541
edited Nov 26 '18 at 18:06
answered Nov 26 '18 at 17:35
Lee MacLee Mac
4,35431541
answered Nov 26 '18 at 17:35
Lee MacLee Mac
4,35431541
answered Nov 26 '18 at 17:35
Lee MacLee Mac
4,35431541
4,35431541
What would recommend I do to have the foreign keys populate in the parent table when new data is added to the child tables?
– user10707314
Nov 26 '18 at 17:45
@JordanBelson I have updated my answer
– Lee Mac
Nov 26 '18 at 18:07
I see, Is there any way to do this without using subforms? Perhaps queries?
– user10707314
Nov 26 '18 at 18:39
@JordanBelson How would the query determine which child record should be assigned to the parent record?
– Lee Mac
Nov 26 '18 at 18:40
They cannot. All of the information entered in the main form is entered and submitted as one. Can we use VBA code to have all of them associate with one another?
– user10707314
Nov 26 '18 at 18:48
|
show 1 more comment
What would recommend I do to have the foreign keys populate in the parent table when new data is added to the child tables?
– user10707314
Nov 26 '18 at 17:45
@JordanBelson I have updated my answer
– Lee Mac
Nov 26 '18 at 18:07
I see, Is there any way to do this without using subforms? Perhaps queries?
– user10707314
Nov 26 '18 at 18:39
@JordanBelson How would the query determine which child record should be assigned to the parent record?
– Lee Mac
Nov 26 '18 at 18:40
They cannot. All of the information entered in the main form is entered and submitted as one. Can we use VBA code to have all of them associate with one another?
– user10707314
Nov 26 '18 at 18:48
What would recommend I do to have the foreign keys populate in the parent table when new data is added to the child tables?
– user10707314
Nov 26 '18 at 17:45
What would recommend I do to have the foreign keys populate in the parent table when new data is added to the child tables?
– user10707314
Nov 26 '18 at 17:45
@JordanBelson I have updated my answer
– Lee Mac
Nov 26 '18 at 18:07
@JordanBelson I have updated my answer
– Lee Mac
Nov 26 '18 at 18:07
I see, Is there any way to do this without using subforms? Perhaps queries?
– user10707314
Nov 26 '18 at 18:39
I see, Is there any way to do this without using subforms? Perhaps queries?
– user10707314
Nov 26 '18 at 18:39
@JordanBelson How would the query determine which child record should be assigned to the parent record?
– Lee Mac
Nov 26 '18 at 18:40
@JordanBelson How would the query determine which child record should be assigned to the parent record?
– Lee Mac
Nov 26 '18 at 18:40
They cannot. All of the information entered in the main form is entered and submitted as one. Can we use VBA code to have all of them associate with one another?
– user10707314
Nov 26 '18 at 18:48
They cannot. All of the information entered in the main form is entered and submitted as one. Can we use VBA code to have all of them associate with one another?
– user10707314
Nov 26 '18 at 18:48
|
show 1 more comment
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53485168%2fwhy-is-the-information-from-my-foreign-key-not-being-updated%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
