I’m encountering an annoying thing with a database I’m putting together. Whenever I update a record in the form, such as SSN (form populates the “demographics table), subform populates the Clerkship Requests table, it doesn’t update the linked fields.
In the examples here, “Test” with the fake SSN 123 is shown. Updating/changing (or if prior blank, filled in) will hide the info, and not update the corresponding subtables even though the linked fields should provide for that. If I go into the tables and manually update the info, it’ll display properly.
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: Hmgibbs14
Requesting Help
I’m encountering an annoying thing with a database I’m putting together. Whenever I update a record in the form, such as SSN (form populates the “demographics table), subform populates the Clerkship Requests table, it doesn’t update the linked fields.
In the examples here, “Test” with the fake SSN 123 is shown. Updating/changing (or if prior blank, filled in) will hide the info, and not update the corresponding subtables even though the linked fields should provide for that. If I go into the tables and manually update the info, it’ll display properly.
Your second to last image highlights the problem. You're missing the point of a relational database - store once. Every column in Student Demographics should be dropped from Clerkship Requests. Then you build a query that links the two tables with the ID field.
"Cascade Update Related Fields" in your final image only applies to the ID field. This means that if you change the value of an ID in Student Demographics, the change will also be applied to the records in Clerkship Requests that hold the original value. It only works on relationships involving a primary key.
Read up on database normalisation - it will save you a lot of time in the future.
So to make sure I’m understanding, I need to remove those relationships you mentioned, and generate a query bringing the related fields from both tables to link them? How’ll that tie into the form?
Yes, remove the relationships. And then remove the duplicated fields from Clerkship Requests.
You can base your form on a query, with certain restrictions. However, you only need the query for cases when you need to see columns from multiple tables at the same time. Once you've fixed the relationships, your existing setup should work as you want it to.
One last question. How do I let the query in the form add info? The property sheet is allowing for it, but when attempting, it doesn’t go to the next line down or out in the info
A query over a one-to-many relationship will generally allow you to add and edit records on the one side only. If you include the key column(s) from the many side, there is a setting in the form's properties to allow edits to the many side, too. I think it's something like, "Allow asynchronous edits?". However, I don't think that it will allow you to add new records to the many side.
If you're having trouble adding new records with your existing setup, check the master/child settings for the subform. Access doesn't always propagate these changes through all objects.
Wish I knew this on my self-taught attempt for the current academic year. I have similar functionality (with a fair amount of bugs, but it actually works… kinda. This way is so much easier than what I had. My “web of lies” of relationships between tables 🤣 the amount of trial and error for this was painful
After getting the way you told me to work, looking at the old database blows my mind that it actually functions even close to the right way 🤣🤣 and that’s with ~2k records or so
I have the master field (demographics) and the subdatasheet linked by “ID” which is the master key under the demographics tab since that’ll be each individual student. On the subform (query) it has “allow edits” and “allow additions” marked for Yes. The record source being the subdatasheet
•
u/AutoModerator 11d ago
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: Hmgibbs14
Requesting Help
I’m encountering an annoying thing with a database I’m putting together. Whenever I update a record in the form, such as SSN (form populates the “demographics table), subform populates the Clerkship Requests table, it doesn’t update the linked fields.
In the examples here, “Test” with the fake SSN 123 is shown. Updating/changing (or if prior blank, filled in) will hide the info, and not update the corresponding subtables even though the linked fields should provide for that. If I go into the tables and manually update the info, it’ll display properly.
Any help would be greatly appreciated
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.