Decision Tree 3
By Richard Rost
3 years ago
Access Decision Tree Database Part 3: Editor Form
In this series of Microsoft Access tutorials, we are going to build a decision tree database that can be used for troubleshooting, logical decision-making, questionnaires, tech support, game development, and lots more.
In today's video, we are going to begin creating the question editor form, which is what the person who is creating or editing the decision tree will use to modify the questions, answers, and other information. We will create a single form that is the parent and a continuous form that will list the children of that parent, and then we will bring them together using a form object with a unique relationship.
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Question Editor Form, Child Editor Form, Link Master Fields, Link Child Fields, Linked Fields are Different for Subform
Intro In this video, we continue building our Microsoft Access decision tree database by designing the editor form for managing your tree structure. I will show you how to create a single form for the parent node and a continuous subform for displaying and editing child nodes, set up relationships between them, and use combo boxes to make navigation and editing easier. You will learn how to configure the form properties so each child record connects to its correct parent, and see practical formatting tips for organizing data on your forms. This is part 3.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
Today we are continuing with our decision tree database. This is part three. We are going to start building the editor form so you - the decision tree editor or creator - can manage your decision tree. And of course, since this is part three, make sure you watch parts one and two first before you start here.
In the last video, we set up our question table. Now we are going to take this and put it into a nice, pretty interface that our users can work with.
Since everything is in one table, what we are going to do is put the parent record - the parent node, whatever you want to call it - on the top of the form as part of a single form, and then we will make a continuous form underneath it to show each of its children.
In the next class, what we will do is allow you to double click on this and jump to that. So this will now become the parent node, and then the children will be underneath it.
Let's start off with the parent node, which is going to be a single form. That is why I keep these little single and continuous form templates right here. They are just templates so we can just copy and paste - control C, control V.
We are going to call this the question editor f - question editor f - because we are going to make an editor and then a viewer, and the viewer will be what the end users use to walk through the decision tree. The editor is going to be for you or whoever is designing the decision tree.
Open this form up. Go to design view. Set the record source equal to the question T. That is pretty much the only thing in this database that has data in it.
Now that we have our record source set, we can go to Add Existing Fields. Just bring everybody in. Welcome to the party. The reason why I keep these around is for format painting. Click on this one, format paint the ID, because I like to make my IDs gray so the user knows they cannot change them. Then, double click on the format painter and click, click, click - that just sets all their formatting.
Now I can delete these. It is easier to use them just to keep around for formatting. You can pretty up your labels if you want to, put Question ID. Normally I do not leave IDs behind for end users to see, but sometimes for you, the editor, it is handy to see that, especially when you are making relationships. That said, you might not need them soon.
We are going to give everybody a quick left-align - format left. I like to make my notes, my notes fields, my long text fields, a mellow color - not that big bright yellow though. If you have watched my TechHelp stuff, you have seen how I do this. Go to More Colors, and slide it up to maybe about there - that is good. You can add shadow and other effects if you want. This one's good for the parent.
Let's replace the parent field with a combo box that will show the actual name of the parent instead of just the ID. Delete the parent field, and this is where relational combo boxes come in handy. Pick a combo box, drop it there.
Set it to get the values from a table or query - of course, it is in question t. What fields do we need from the question t? I only need the question ID and the description. I do not need parent ID here, because in this box, I do not care who the parent of that record is. But I need its question ID so I can store that question ID in the parent ID of this form.
Next, sort them by description. One of my options for later is - and if we get to it, I know a lot of these are duplicated because they are children under other nodes. So, one thing we might add in the future is to only show the items in here that are above this node on the tree. If you want to see that, put a comment down below.
For now, I am just going to show everybody, and I am mostly interested in that name. Usually you are not going to actually pick it from here, but you can.
Now, once I have picked a question ID, I am going to store that in the parent ID field of this record. We are displaying the parent of whatever is in this record.
Next, for the label, you can use Parent or Parent ID - either one is fine. There we go. Again, use the format painter to make sure the color gets set, and put it there. Everything looks good. These two fields could probably be bigger since they will have longer descriptions.
Good enough for now. Save it, close it, take a peek at it, make sure it looks good. It looks good. I will make this bigger later, but for now, let's get the subform built.
Close this. Now we will make the subform that is going to go underneath it to show its children. Take our continuous form, copy, paste.
What are we going to call this? Let's call it the child editor. Not children - I try to keep everything singular if possible. Notes is the only exception, and that is just a bad habit. If you are starting off new, go ahead and call yours note.
Question editor f - whoops, wrong one - child editor f. This is my continuous form. Set its record source also to question t, because it is getting its data from the same table. These are all still questions.
What do I bring in here now? Add existing fields. I do not really need the question ID in here because I do not care to see it. I do not need the parent ID here, although I do need it to make a relationship to the master form, the parent form. But that relationship can be set in the properties of the subform object, so I do not need that here either.
All I really need is description and notes. You do not even really need notes here because you are going to open up the child and it will open up in the full form, so you will not need the notes. But I am going to include them here because it is nice to visually see which ones you do not have notes for yet. Just bring those two over - description and notes.
Delete these labels, delete these extra fields, slide description up here, maybe make it about yay big, and notes - I just want a small preview of the notes here, since we are probably not going to edit them in this spot.
While I have still got that format color set, apply it to the notes field. The nice thing is the last color you use stays in the paint can. Put the notes field here, description here - call them whatever you want.
Slide that up. I do not think we need a form footer for this one, so we will get rid of it. Slide it up, slide that to the left. Save it, close it, take a peek at it. Looks good.
If you scroll down here, you can see all the items where you have added an option but you have not fleshed it out yet or continued that path of the decision tree. That is handy.
Close it. Now we have the parent form and the child form. That is going to be our subform.
We have got the chocolate and the peanut butter - your chocolate ended up in my peanut butter; you got peanut butter in my chocolate. Those taste great together.
Open up the question editor. Go to design view. For a little trick - grab the child editor form, click, drag, drop, and there is our subform. That is how easy it is to make a subform.
Delete the label that comes in with it. Make a little more space here. In the other video, I put it over to the side, but for now I want to keep it underneath so you can see the relationship better. We will be moving things around in a minute.
Make it a little bit taller. Ready, save it, close it, open it. Looks good. Go to the next record.
Wait a minute, I am seeing the same record down here as I am seeing up here. Why is that? Can you figure it out? Pause the video and see if you can figure it out.
The reason is because of the relationship between the parent and the child, which is set in the subform object. Here is what you want to do:
Click on the subform object - not inside the subform with those inner fields. Click the border of the subform itself so the border is highlighted. Double click or right click to bring up the properties.
Here is the property sheet for the subform/subreport. Link Master Fields, Link Child Fields. The question ID in the master (parent) field is going to be the same as the parent ID in the child field. You can click the little button here to change it if you want, or you can just type it in.
We are saying that the question ID here matches the parent of this record, which is what we want. Each one of these children has a parent ID equal to this guy. That is what forms the relationship. That is the trick. Save it, close it, open it, and now you are seeing the parent and its children down below as you move through the records.
You can see the dungeon crawl. If we have not done anything for this one yet, we did not do anything with the Access Database Help. There is your dungeon crawl. There is your PC Turns On, yes, it boots, and so on. That works.
Now that this relationship is set up, you can actually come into the subform, add items here, and since you have the relationship set between parent and child, it is going to automatically fill in the necessary parent ID fields for you.
"PC troubleshooting as a PC turn on? Not sure, cannot tell," etc. If you leave the form and come back, it is still there. Why? If you look in the question T table and scroll down to the bottom where that new record is, look - it filled in the parent ID because of the relationship.
You do not have to set up a default value in a hidden field, which you used to have to do in older versions of Access. When I learned Access in the 1990s, you had to put a hidden field in the subform and set its default value equal to parent question ID. You do not have to do that anymore, which is nice.
One thing I like to do, and I have found helpful when working on your decision tree, is giving more room for the notes. Slide this over. You might want to delete the extra field and make the notes area bigger, especially if you are building a dungeon crawl or need lots of information.
You can make this area bigger, and we will put more stuff on here eventually. This is much better to work with. Now, your children are to the right instead of below - do not get confused by that.
Now you can go through your items, look, and see which ones you have and have not finished yet. This guy does not have any options. "Windows boots - are you able to run applications? Yes. Basic applications start." Then enter "Nothing runs," etc. Those options are now in the system, but you have not fleshed them out yet; you just put the items in.
Later on, we will make a query that lets you see which branches of the tree are not finished. One thing I was thinking of (and I am not sure if we will get to it) is that, when we turn this into a multi-user application, if people get to a point in the story that is not finished yet, you can let users add their own options and then come back and fill them in later.
Even with the troubleshooting checklist, if users get to a point where they need a new option, they can add the option. If you want to see that feature, put a comment below. The more comments I get, the longer the series will be.
I will only keep making videos if people are watching, not spending time if a video only gets four viewers. That is just how YouTube works - got to do it for the views.
Back to my prototype database for a minute. We have gotten this far; now the editor is working. Mine is a little different, but it is the same idea. See how these items are blue?
What we are going to work on in the next lesson is making it so that if you are working on something and want to add to a specific node (like Paladin), you can double click on Paladin, and it puts Paladin as the parent, and now you can work on its children. To go back up to the parent, double click there again. Or if you are in the PC troubleshooting editor, you can work on that branch and continue drilling into your nodes.
That will be covered in the next video. Tune in to the "same bat time, same bat channel." It should be tomorrow because this is Monday's video, and tomorrow will be Tuesday's. Check the website, check my YouTube channel.
There is the link right there for the first one if you have not watched the first one yet. I'll post a list of all of them there. When I am done with the series, I will put links to the next video in this. If you are watching this in the future, you will be able to click the link to the next one in the description down below.
As a brief advertisement, if you like this kind of stuff and learning with me, check out my developer lessons on my website. There is the link down there, and you can scan the little QR code. I have lots more lessons just like this covering many topics, so check it out.
There you go. There is Decision Tree Part Three. That is your TechHelp video for today. I hope you learned something. Post down below, give me some comments - what features would you like to see added to this?
Live long and prosper, my friends. I will see you next time.Quiz Q1. What is the primary purpose of the form being created in this lesson?
A. To allow users to walk through a decision tree step by step
B. To let editors manage and build the decision tree structure
C. To generate printable reports of decision tree outcomes
D. To create user authentication for the database
Q2. How does the interface display the parent and children of a decision tree node?
A. Each child appears in a new window when the parent is selected
B. Both parent and children are shown in a single continuous form
C. Parent details are in a single form at the top, with children in a continuous subform below
D. Only the children are displayed; the parent is hidden
Q3. What is the main function of the combo box added to the parent form?
A. To filter the displayed children by their status
B. To display the name and allow selection of the parent node instead of just its ID
C. To allow the user to add a new question to the database
D. To switch between different tables in the database
Q4. In setting up the subform, which fields are included by default for each child record?
A. Question ID and Parent ID
B. Description and Notes
C. Parent ID and Description
D. Notes and Date Added
Q5. Why is it important to set the Link Master Fields and Link Child Fields properties in the subform?
A. To synchronize formatting between parent and child forms
B. To ensure that children are filtered to show only those belonging to the selected parent
C. To allow sorting of children by their IDs
D. To enable multi-user editing at the same time
Q6. What effect does setting the parent-child relationship in the subform properties have when you add a new child record?
A. It automatically sets the child's parent ID to the selected parent
B. It prevents data entry in the subform until the parent is saved
C. It copies all parent fields into the child record
D. It disables editing the child description field
Q7. Why might the editor form include the Question ID field for the parent node, even if not shown to end users?
A. To make data entry faster
B. To improve the graphical appearance of the form
C. To help the designer manage relationships during editing
D. To hide records from users
Q8. What is a potential future enhancement discussed in the video for the parent selection combo box?
A. To allow sorting by date created
B. To only display items that are above the current node in the decision tree
C. To automatically color code parent nodes
D. To require user authentication before selection
Q9. Why does the instructor sometimes leave the notes field with a distinctive background color?
A. To hide important notes from standard users
B. To help editors quickly spot which items need additional information
C. To make the database look more professional
D. To separate notes from questions for printouts
Q10. What database design convention did the presenter mention regarding naming conventions?
A. Always use plural names for tables and forms
B. Use singular names for forms and fields, except for 'notes'
C. Never include IDs in form designs
D. Prefix every object with 'tbl' or 'frm' for clarity
Q11. According to the video, what is an advantage of modern Access versions when dealing with subforms?
A. You no longer need hidden fields to set default parent IDs for new child records
B. You can automatically generate subforms for any table
C. Only system administrators can edit forms
D. Subforms always require macro actions to function
Q12. What is the presenter's plan for navigating deeper into the tree in future videos?
A. Pressing a button labeled 'Next' to go deeper
B. Double-clicking a child node to make it the new parent and show its children
C. Dragging and dropping nodes into different locations
D. Using filter checkboxes at the top of the form
Q13. What type of query did the instructor suggest might be useful to add in a later lesson?
A. A query to find which branches of the tree are not yet finished
B. A query to list all users of the database
C. A query to export the tree to Excel
D. A query to import new questions from another table
Q14. What general feature does the presenter suggest could be added to support multi-user scenarios?
A. Allow users to add options if they encounter an unfinished branch
B. Restrict data entry to one user at a time
C. Automatically backup the database every hour
D. Limit the number of child nodes per parent
Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-A; 7-C; 8-B; 9-B; 10-B; 11-A; 12-B; 13-A; 14-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on continuing development of a decision tree database using Microsoft Access. This is part three in the series, so I strongly recommend watching parts one and two before proceeding here to make sure you are up to speed.
Previously, we set up a table to store all our decision tree questions. Today, we'll begin building the editor form that allows the decision tree designer, or editor, to manage the tree's structure. Our approach takes advantage of the fact that all records exist in one table. We'll display the parent node at the top of the form in a single view, and just beneath it, we'll use a continuous form to list all that node's children.
In a future lesson, I plan to add functionality so that you can double click on a child node to make it the new parent node, displaying its children beneath it. This will make navigation and editing more intuitive.
Let's start by building the parent node section of the form. For convenience, I like to keep templates for single forms and continuous forms ready, so I can quickly copy and paste one to get started. I'll call this new form "Question Editor F" to distinguish it as the editor (for creating and organizing the tree) rather than the user-facing viewer.
After setting the form's record source to our questions table, I use the "Add Existing Fields" option to bring all the fields onto the form for easy access. I prefer to format the ID fields in a gray color to signal that users should not change them. Using the format painter tool helps maintain a consistent look. While I typically hide IDs from end users, they're handy for editors as you build relationships.
For the notes field (a long text field), I apply a more subdued background color for easy reading, steering clear of the default bright yellow. These small format tweaks improve usability for editors who will spend a lot of time with the form.
Next, instead of displaying the Parent ID as a raw number, it's more helpful to use a combo box to display the parent's name. That way, rather than seeing just another ID, you see which question is the parent. The combo box is set to show the question ID and description from the questions table, sorted by description. In the future, it might be useful to limit this list to show only valid parents – for example, those higher up in the tree – but for now, it shows all questions.
The combo box stores the selected question's ID in the Parent ID field, reflecting the actual parent relationship. As for the label, you can use either "Parent" or "Parent ID."
Once satisfied with the appearance and function of the form, I save and close it. Now, it's time to build the subform that will list each parent node's children.
I start by copying my continuous form template and naming it "Child Editor F." Its record source is set to the same questions table. On this subform, I only need to display the Description and Notes fields. The Question ID and Parent ID fields are not required here, as the relationship to the parent form will be established through the subform's property settings. Including notes can be helpful to spot at a glance which child nodes still need detailed information.
After adjusting the fields and deleting unnecessary labels, I tweak the sizes of the fields for better readability and remove the form footer to free up space. Saving and closing the subform lets me quickly review how it looks, ensuring all child nodes are displayed as needed.
Now, with both forms built, I integrate them. On the Question Editor form, I insert the Child Editor subform by dragging and dropping it into position. I adjust the layout so that the subform sits below the parent node, making the hierarchy clear at a glance.
Upon reviewing the form, if you notice the same record appears both in the parent and child sections, the issue lies in the subform's data link properties. To correct this, make sure that in the subform control's property sheet, you set the "Link Master Fields" to Question ID and "Link Child Fields" to Parent ID. This ensures only the child records of the current parent are shown below their parent node.
With this setup, you can add child items directly on the subform. Access automatically fills in the Parent ID, tying each new child record to the selected parent. This streamlines editing, as you no longer need hidden fields or default value tricks from older versions of Access.
You might decide to make the notes field larger if you need more room, for example if your decision tree includes detailed explanations or a dungeon crawl scenario. The form can be laid out so that child nodes display on the right if you prefer. As you build out your tree, you can clearly see which parent nodes still need children, making it easy to track your progress.
Looking ahead, the plan is to add features such as a query to quickly spot unfinished branches in your tree. If developed as a multi-user application, you could even allow end users to add their own options and fill in branches that aren't complete yet.
As the editor takes shape, the navigation and editing capabilities will be improved further. In the next lesson, I'll implement functionality to let you double click a node to make it the new parent, helping you drill down or move back up the tree as you manage nodes and options.
You can follow the series by checking for new tutorials on my website or YouTube channel. If you missed any earlier parts, they are linked on my website as well.
If you enjoy these tutorials and want to learn more, be sure to check out my other developer lessons on my website. I cover many topics similar to what we discussed today.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating a parent and child editor form for a decision tree
Setting the record source for forms to decision tree table
Using single form layout for the parent node
Using continuous form layout for child nodes
Copying and pasting form templates for rapid form creation
Applying format painter for consistent field formatting
Replacing parent ID field with a combo box for parent selection
Configuring combo box to display parent question descriptions
Sorting combo box items by description
Saving parent question selection in the parent ID field
Designing subform to show child questions
Selecting specific fields for subform display
Previewing notes field in child editor subform
Applying custom formatting to notes field
Inserting subform into the main parent editor form
Configuring master and child field links for subform relationship
Ensuring correct display of parent and child records
Automatically filling parent ID using form relationships
Expanding notes field for better usability
Identifying unfinished branches in the decision tree via the form |
