Decision Tree 4
By Richard Rost
3 years ago
Access Decision Tree Part 4: Editor Navigation
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 create a list box on the main menu to show the parent nodes or the tops of the decision trees. Then we are going to create double click events so you can move up and down inside the decision tree by clicking on a child or the parent, and it'll bring you into that record.
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, Navigate thru decision tree, double-click events, move to child node, move to parent node, list of parent nodes on main menu, list of decision trees
Intro In this video, we continue building the Decision Tree Database in Microsoft Access by focusing on editor navigation. I will show you how to create a main menu that lists root nodes, use a list box to display parent records, open the editor form for navigating between parent and child nodes, and add navigation features using both SQL and your first bit of VBA. You will learn how to move through the decision tree by double-clicking on children and parent records, and customize your interface through form design changes and button functionality. This is part 4.Transcript Today we are continuing on with our Decision Tree Database. This is part four, Editor Navigation. We are going to build ways to get around inside the database by moving from record to record.
We're going to start by building up the main menu. So we've got the list of the root nodes here, the start of each Decision Tree. From there, we're going to open up the Editor form where we can click on the children to launch that record, go back up to the parent, and so on.
Of course, this is part four of a multi-part series. If you haven't watched parts one, two, and three, go watch those first. Don't try to jump in the middle. Just go watch the first, second, and third parts, and then come on back.
Now, everything we've done so far in parts one through three, we haven't needed any VBA whatsoever. I told you way back in part one, we're going to be using some VBA. So that starts today. If you still have not yet watched my intro to VBA video, go watch that. It's free, it's on my YouTube channel. It'll teach you everything you need to know in about 20 minutes. So go watch it, then come on back.
All right, we're back at it. Now, the first thing I'm going to do is take this main menu here and make this not the main menu for the TechHelp database anymore. Let's see if we can get rid of this stuff here. I'm going to change this so it says "Decision Tree."
Now remember my trick: don't change it in here, because if you change it here, if you put "Decision Tree" here, watch what happens. All right, and that changes the labels, apparently the size and height of them. But just undo that, Control Z. What you can do is you can open up the properties and change it over here in the Caption prompt. Right there, Decision Tree. Then your label doesn't reform; it just keeps its same dimensions.
I didn't do this in a screenshot, but let's come in here and we'll call this Decision Tree version 0.04. That's because we're on the fourth lesson and we're not at version 1.0 yet. I probably will forget to update this in future videos, so no one yell at me. When we're all done with it, I'll make it version 1, hopefully.
All right. And since we're doing this kind of stuff, let's change the title here of the application. I'm going to save that form, go to File Options, and then Current Database. Right here, we're going to call this Decision Tree by Access Learning Zone. How's that sound? There we go.
All right. I'm also going to change my color, just a wee bit so it looks different from the other guys. Let's go with dark blue here, and let's go with a lighter blue. I'm all about color. There we go. That looks pretty good.
OK. Next, I want a list box over here that's going to show a list of the parent nodes, the top nodes of the Decision Tree. You can have as many as you want, but the defaults here are all zeroes. I'm going to make it so it's either zero or null. You can be a parent ID because later on when you're editing these things, if you want to get rid of something's parent, we could make a button over here that would set it to zero. But you can't easily set this combo box to zero. If you just blank that out, it turns it into null, so it's an easy way. I'm assuming some people might do that.
We can have two conditions for it being a parent node. Let's create our list box here. Go to Form Design. Where's my list box? It's near right there. That's a list box. I don't use them as much as combo boxes, but they have their purpose. List boxes are great when you want to have a list of options that are right in someone's face. They don't have to open up a combo box.
All right. Get the values of a table or query. QuestionT, that's fine. I'm going to bring in the question ID and the description. Again, we don't need the parent ID for this. Next. Well, we are going to use the parent ID, but we don't need it in the list box. You'll see in just a minute.
All right. What do we want to sort these by? Description's fine. If you want a custom sort order, just make yourself a custom sort order field and sort by that. That's also possible. All right. Next.
This is what it's going to look like. Now don't panic. Yep, all the items are in there. That's OK. Make that about yay-wide. Next. What label do you want? Doesn't matter. We're going to nuke it anyway. All right. And it's right there. I know it's hard to see. Delete. Make this a wee bit bigger, maybe like so. Let's match the size of that guy down there. Like that. OK. And we'll give it a touch of color as well. Maybe, I don't like to use the theme colors, by the way. I like to use these guys. There we go. That's better.
All right. Give it a name. There's one thing I don't like about the wizards, the combo box wizard and the list box wizard. This is a note for the Access team: it calls it list16. You should ask, what do you want to name this thing? Let's call this the question list. That could easily be a step in the wizard, guys.
I was watching an interview with the new program manager for Access. I didn't know that the Access team, the people at Microsoft that actually develop and work on and build Access, there's only six people. I was surprised; I figured it'd be at least 20 or 30 people. I've always said Access is literally the red-headed stepchild of the Microsoft Office family. I have nothing but love for it; I've built my whole career on it. But yeah, six people, wow.
All right. Anyway, let's save this, close it, and open it back up. I've got a button on my toolbar up here that opens it up. There we go.
OK. Now, there's all the nodes, all the questions. We want to limit this to just the guys that are zero parent nodes. So let's go and edit your SQL. I told you in the first lesson too, we're going to use a little SQL on this. Go over here to the Data tab, click on this, then go to Data. Here's the row source right there.
I'm going to zoom in. Shift F2, that's the zoom box. This is the SQL statement that generates the list of records in that box. Since I'm only using one table, I don't need QuestionT. everywhere, so it makes it a little more readable. Let's just get rid of the QuestionT. and the extra brackets we don't need. We'll make this a little easier to read.
That's another thing the Access team could do: if it's based on a single table, you don't need all that QuestionT. everywhere. Makes it harder to read for the noobs.
OK. So here's the list. It's giving me basically the question ID and the description from QuestionT, ordered by description. That's our sort. We're going to add a WHERE condition here. We need to say where the parent ID is either null or zero.
So right in here, we're going to say WHERE ParentID = 0 OR ParentID IS NULL, just like that. Either of those conditions can be true. Do we need parentheses around it? You could; it won't hurt anything, but you don't really need it here.
OK. Hit OK. Save it. Close it. Open it. Boom. There you go. There's your nodes that have a zero or a null value as their ParentID, so they're the top of their respective decision trees. It's one of the many reasons why learning SQL is very important. This stuff here is all over Access, and the more you learn about this and VB, of course... but SQL is the foundational language of databases. Learn this stuff, folks. I'll talk about a seminar I have at the end of the video about my SQL seminar. It's a three-part series.
All right. Now we're going to make a button for the editor, the admin, or whatever you want to call yourself, to open up whatever value the user selects in that box. I'm going to hijack the Hello World button because we don't really need it for anything else. We'll move it up here and I'll put in here, Editor. OK, and we'll call this now the Editor button.
Right-click. Build Event. Let's go into the Code Builder. This is our first look at some VBA. Now, there's a lot of stuff in here from the TechHelp template that we don't need. I'm going to keep my Status function in here. We don't need the Customer Contact button or the Customer Form button. There's our new Editor button stuff, and we don't need... leave the form. DoStartup is just some code that I have for me. It's in this RixMod KeepOut. It just moves the database on my screen where I want it. You can use it if you want to, but it's a program for my machine. I talk about it in another video. We can get rid of this.
Right here, in the Editor button click, what do we want to do? First, we want to make sure the user picked something. If not, we're just going to exit out.
So, If IsNull(QuestionList), then you could put a warning in here: pick something first, whatever. I'm just going to exit sub. Don't do anything if they didn't pick one of those decision trees.
If they did pick a question value from the question list, I want to open up the Question Editor form to that specific record. So we're going to say:
DoCmd.OpenForm "QuestionEditorF", , , "QuestionID=" & QuestionList.Value
So if they picked the one, it's going to open up record one. That's it. That's all you need. Save it. Always good to throw in a Debug Compile from time to time. Back out, close it, open it, hit the button. Nothing happens. You can throw a Beep in there or a MessageBox or whatever you want to do. I don't care. Pick Dungeon Crawl and hit Editor. Boop. That takes you right to Dungeon Crawl.
Close it. Pick the PC Troubleshooting. Boop. It takes you right to the PC Troubleshooting. There you go. That's how you open it up.
Now we're going to do the same thing in here to navigate up and down the decision tree.
Let's start with the children. If I double-click on one of these, I want it to go to that child record, which essentially is just going to reopen this form centered on that node. So where QuestionID equals that guy.
Right-click, Design View. Click in here. Now, that selects the subform. Click on it a second time, and now you've got the description in here. Select it. Bring up its properties, go to Events, and then you want the On Double Click event. Click the ellipsis.
Now we are in the Description_DblClick event of the child editor form. In here, we're going to say:
DoCmd.OpenForm "QuestionEditorF", , , "QuestionID=" & [QuestionID]
That QuestionID here is the value of the QuestionID of the child form. So we're going to open up the parent form centered on that record.
Save it. OK. Now, I like to throw a splash of color. I like to use blue that visually indicates to the user that they can do something on that. You could put a button there or whatever. I like double-click events. Format that and pick a blue. Let's go with, I like that blue. That's good.
All right. Save it. Close it. Open it back up again, Editor.
All right. Let's go to "No, the PC does not turn on." Double click. Boom. Look at that. I'm on "No, the PC does not turn on." See? "Yes, it's plugged in." Double click. Boom.
We're going to make this process a little more streamlined for the end user. This is the best way to do it for you, the admin, the creator of the decision tree. We're going to make a nice, pretty one for the end user. For Joe in accounting, he doesn't know how to use a computer.
Now let's do the same thing to go back up. We'll double-click on the parent to go back up a level.
Click Design View here. Let's put that color on it while we've still got it. There we go. Double-click on you. Go to Event. Oh, it's Combo8. I always look for that.
We didn't give this a name. See, that's again, the wizard should do that. Slide up to the top and let's call this guy ParentCombo. You could call it ParentID, but I like to call it ParentCombo because later on, if I'm programming on it, I know it's a combo box and not just an ID text box.
OK. Events, On Double Click. Click the ellipsis. Here we go, right here.
Now, this is a case where I do want to check to make sure the parent isn't null again.
So, If ParentCombo <> 0 And Not IsNull(ParentCombo), that covers both cases. Then
DoCmd.OpenForm "QuestionEditorF", , , "QuestionID=" & ParentCombo
So open up the form where the QuestionID equals whatever the parent is. Save it. Throw in a Debug Compile for good measure.
We can close this now. We're done with it. Close it. Close it. Open it.
There we go. Let's go to "No, the PC does not turn on." "No, it's not plugged in." OK. Let's go back up to "No, the PC does not turn on." Back up. When you double-click, it will highlight a word sometimes. There are tricks you can do to get around that. If you want to see it, I'll show you in a future video.
Now we know how to move up and down throughout the decision tree.
Here's the top. Go to "Yes, the PC turns on." "Yes, Windows boots." "Yes, basic applications start." Double click there. Now you can add more stuff very easily.
Go back up to "Yes, Windows boots." "Yes, application start." "No, nothing runs." "Can't tell." "My mom won't let me use the computer," whatever. Now I can flesh that out.
Same thing with our Dungeon Crawl. Here's our Dungeon Crawl. We're at the Editor. I think in the other one I added Paladin. That was in my template one that I had before. Now I want to borrow some stuff from, say, Cleric. So go to Cleric. I was borrowing stuff before. Copy. Go back up to Dungeon Crawl. Click on Paladin. Double-click. I could paste that here now.
Preaching, let's say, "warrior of the cloth," whatever you want to call them. You gather your holy symbol, and what is a Paladin? You use a limb and spear, I don't know, whatever, and head to the dungeon, OK? And so on. Then you can copy the other items and stuff.
And sure, you can make a button here that copies the children from another node. There are all kinds of things you can do, folks. That's why you learn how to do this stuff yourself. That's why you learn VB. That's why you learn SQL. That's why you learn how to build this stuff. Because you can put the Legos together any way you want.
And I know this is a silly gaming example, but it's the same thing for your business, too. If you've got custom procedures in your business that you've been following for years, don't try to find other software that fits your business. Build the software to fit your business. You've got a successful business; your procedures work. Don't try to shoehorn into QuickBooks or some other off-the-shelf accounting software when you've got a certain way of doing things. Make your database do what you want it to do. That's why you learn this stuff.
That was most of what I did when I was a consultant for about 20 years. I went into companies, I learned how they did business, whether it was on paper, Excel spreadsheets, whatever. Show me your procedures, I'll build your database around the way you're used to doing business. That's the power of an Access consultant. That's why Access is a great tool for rapid development, especially for small businesses. Then when you outgrow it as a backend, you upgrade SQL Server and you can still keep using your Access database. I can talk about this stuff for hours.
All right, back to my prototype. We're just starting to look very much like the one we're building. Back to the prototype. Now, double-clicking on this will bring up the user form. This is a lot more pristine than just the editors for us. The user form is what we're going to build next. This is going to be designed for the user so they see the question here, and then they can just double-click on the answers down here. It makes a lot more sense than the editor version.
So, yes, "PC turns on," double-click. OK, yes, "PC turns on." That's good. There's "Windows Boot," "No Windows," "does not boot." This is a little cleaner. We're going to build this in the next video, complete with a back button here so you can go back, or close it if you're at the parent node.
Now, I mentioned before the importance of learning SQL. Learn SQL, people. If you're going to be doing anything semi-serious with Access, you want to learn SQL. I have a three-part series. It's separate from my regular courses. In my regular courses, I do teach you enough to be functional and to get around. But if you really want to learn SQL, part one covers all the basics of SELECT statements, WHERE conditions, ORDER BY clauses, that kind of stuff. Part two gets into action queries: modifying the data, INSERT queries, UPDATE queries, DELETE queries, that kind of stuff. Part three is all about more advanced stuff: manipulating the structure of tables. You can add fields, add indexes, drop tables, all kinds of stuff. That's my SQL Seminar series. You can take one, two, or all three parts if you want to. You'll find information on my website. There's the link down there.
Don't forget about my developer courses if you want to learn more about programming in VBA. And that is going to be your TechHelp video for today.
I want you to post down below what features you would like to see added to this decision tree. The more feedback I get, the more features I'll add. What people want to see, squeaky wheel gets the grease.
I hope you learned something today. Folks, live long and prosper. I'll see you next time.Quiz Q1. What is the main goal of this lesson in the Decision Tree Database series?
A. To add new decision trees to the database
B. To build editor navigation for moving between records
C. To design custom reports from decision tree data
D. To create user logins and permissions
Q2. What is recommended before watching this video in the series?
A. Review Access VBA developer documentation
B. Skip to the end for an overview
C. Watch parts one through three of the series
D. Test the database using external data
Q3. What is emphasized as the foundational language of databases in the video?
A. Visual Basic for Applications (VBA)
B. SQL
C. Python
D. Microsoft Excel formulas
Q4. What new feature is added to the main menu in this lesson?
A. A button to export data to Excel
B. A list box showing the root (parentless) nodes of the decision tree
C. A combo box for selecting colors
D. A text field for entering new questions
Q5. How are the root nodes of the decision tree identified in the SQL statement for the list box?
A. Records with a child node count greater than zero
B. Records where ParentID is zero or null
C. Records with the highest QuestionID
D. Records with a specific description value
Q6. What is the best method to change the caption of a form without resizing the label in Access?
A. Edit the label text directly in the form design view
B. Change the value via the Caption property in the form's property sheet
C. Use a macro to update the label dynamically
D. Double-click the label and type the new caption
Q7. What is the purpose of the VBA code added to the Editor button on the main menu?
A. To import questions from another database
B. To open the QuestionEditorF form for the selected record
C. To delete the selected question
D. To save changes to the current record
Q8. What does the code DoCmd.OpenForm "QuestionEditorF", , , "QuestionID=" & QuestionList.Value do?
A. Opens the form filtered to display all questions
B. Opens the form at the record matching the selected QuestionID
C. Creates a new record in the QuestionEditorF form
D. Closes the QuestionEditorF form
Q9. What action is taken when a child node's description is double-clicked in the editor?
A. The application closes
B. A message box shows the question details
C. The editor reopens showing the selected child node
D. The parent node is highlighted
Q10. Which event procedure is used to allow double-clicking the parent combo box to move up the tree?
A. ParentCombo_Click
B. ParentCombo_DblClick
C. ParentCombo_AfterUpdate
D. ParentCombo_BeforeUpdate
Q11. Why is it important to check if ParentCombo is not zero and not null before opening the parent record?
A. To prevent opening the editor if there is no parent
B. To ensure that the child's description is filled in
C. To avoid an error when deleting records
D. To sort the tree nodes correctly
Q12. What advantage do list boxes have over combo boxes as described in the video?
A. They load data faster
B. They take up less space on the form
C. Options are immediately visible without needing to expand a dropdown
D. They allow users to edit items directly
Q13. How does learning VBA and SQL benefit the user according to the video?
A. It allows integration with other Office products by default
B. It enables users to build customized solutions tailored to their specific business needs
C. It makes exporting reports to PDF easier
D. It eliminates the need to use Access forms
Q14. What is a recommended practice after adding VBA code to your database, as mentioned in the lesson?
A. Compact and repair your database immediately
B. Run Debug Compile to check for coding errors
C. Share your file with other users for testing
D. Rename all controls on the form
Q15. What is the video creator's suggestion if you outgrow Access as a backend?
A. Switch to using only Excel
B. Migrate the backend to SQL Server but keep using Access as a frontend
C. Move all data to text files
D. Delete historical data to keep the database small
Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-C; 10-B; 11-A; 12-C; 13-B; 14-B; 15-B
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 part four of our Decision Tree Database series. In this segment, I'll show you how to build effective navigation throughout your decision tree by enabling movement from record to record within Microsoft Access. If you missed the first three installments of this series, I strongly recommend starting there, as everything builds upon those earlier lessons.
Up until now, we have not needed to use any VBA code. However, as promised from the start, I will begin bringing VBA into the process today. If you haven't already seen my introductory video on VBA, please watch that first. It is free, quick, and will give you all the background you need before proceeding.
The first improvement we'll make is updating the main menu of the database. Instead of displaying the generic TechHelp database interface, I'll tailor it for the Decision Tree project. For example, I'll update the caption to read "Decision Tree" rather than making the change directly on the label, which can lead to unwanted resizing. Always handle label modifications through the property sheet's Caption field. I'm also including a version label, marking this as version 0.04 to reflect our progress. While I may not update this for every lesson, I'll make sure it has the correct version number when the project is complete.
I'll also configure the database to display our project's name in the application title by updating the settings in File, Options, and then Current Database. A quick color change to the form will help visually differentiate the Decision Tree database from other projects, choosing a distinct set of blues for clarity.
Next, it's time to implement a list box that displays only the top-level (root) nodes in each decision tree. The list box, unlike a combo box, is useful when you want all options visible at all times. Using Microsoft Access's form design tools, I'll create a list box bound to our question table. It will display the QuestionID and Description fields. While the ParentID is fundamental for determining the hierarchy, we do not need to show it directly to the user here.
I'll sort the options by description for ease of use. If a custom sort order is required, it's straightforward to add a field for that. One thing to note about the wizard-generated controls is that they often create generic names like "List16." I always recommend renaming your controls to something more meaningful, such as "QuestionList," for easier management later.
A quick aside about Microsoft Access: the development team is surprisingly small, just six people according to a recent interview I saw. This always reinforces how Access has a unique place within the Microsoft Office suite. Despite its small team, it remains an incredibly powerful rapid development tool, especially for businesses needing custom solutions.
With the list box in place, I'll adjust the SQL row source so it only shows root nodes, which are defined as those records where ParentID is either zero or null. Editing the SQL statement helps streamline the visual side and ensures only the appropriate records are shown.
After updating the row source and testing it, you'll see that only root nodes of the decision trees are provided as options in the list box. This is an excellent example of why a solid understanding of SQL is essential for anyone working with Access databases. SQL underpins much of what happens in Access, so if you want to develop more advanced solutions, continue learning SQL alongside VBA.
To aid navigation, I'll repurpose an existing button on the form, relabeling it as "Editor." This button will open the Question Editor form for whatever node is selected in our new list box. The VBA behind this is straightforward: it checks to ensure the user has selected an item and then opens the editor form filtered to the selected question. A quick Debug Compile in the VBA editor helps ensure the code is error-free.
Once implemented, selecting a root node and pressing the Editor button will open the relevant record in the question editor form.
Within the editor itself, we need an efficient way to move throughout the decision tree, both down to child nodes and back up to parent nodes. To navigate downward, I'll configure the double-click event on child records: double-clicking a child question will reopen the editor form centered on that question. I add a blue highlight to these records so users recognize they can interact with them.
Navigating back up the tree relies on a similar mechanism. Double-clicking the parent record (which I rename as "ParentCombo" for clarity in the code) reopens the editor form to display that parent question. The VBA code checks to ensure a valid parent exists before proceeding.
These navigation mechanisms allow you to move freely up and down the decision tree in both the admin/editor interface and, soon, in a user-facing format. Testing these features shows how easy it is to expand the tree and create a fluid editing experience. Whether you're using it for troubleshooting guides, games like a dungeon crawl, or even business decision flows, the principles remain the same.
The flexibility I'm demonstrating is one of the key advantages of building your own database tools. If you have business procedures that are unique and well-established, you are much better off creating a database that supports them rather than trying to adapt your processes to fit someone else's off-the-shelf software. As a consultant, I would often build solutions entirely customized to how the business already operated, taking advantage of both the rapid development capabilities of Access and its extensibility with SQL Server if you need a more robust backend in the future.
Looking ahead, the next step will be to design a user form with a cleaner interface, showing the question and its possible answers in a way that's straightforward for end-users. This form will include features like a back button and logic for when you reach a tree's root node.
Let me stress again the value of mastering SQL if you plan to do anything in Access beyond simple projects. I offer a three-part SQL Seminar series. Part one covers the basics, such as SELECT statements and WHERE conditions. Part two dives into action queries that let you modify your data. Part three handles advanced techniques, including managing tables and indexes. You can find more information about these seminars on my website.
If you're serious about building database solutions, be sure to check out my developer-level courses on VBA programming as well.
I encourage you to suggest any features you'd like to see in this decision tree. The more feedback I get, the more enhancements I can make. Squeaky wheel gets the grease.
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 Building a main menu for the Decision Tree Database
Changing form and application titles in Access
Customizing form colors for differentiation
Creating a list box to display root decision tree nodes
Configuring list box row source with SQL for parent nodes
Editing SQL to filter nodes with WHERE ParentID = 0 OR ParentID IS NULL
Naming and organizing form controls for easier development
Creating a button to open the editor form for a selected node
Writing VBA code to open the editor form to a specific record
Using VBA to check for valid list box selection before opening forms
Using DoCmd.OpenForm to navigate to specific records in Access
Setting up double-click events to open child nodes in editor
Applying color formatting to controls to indicate interactivity
Setting up double-click on combo box to navigate to parent node
Using VBA to move up the decision tree via parent node navigation
Testing user navigation by traversing up and down the decision tree |
