|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Nested Subforms By Richard Rost 
6 years ago
How to Create Multiple Nested Continuous Subforms In this video, I'll show you how to create multiple continuous (list) subforms inside of each other. We'll track data for a fictional college and store departments, courses, and classes. So if you want to know in which courses you'll find classes like "Neil DeGrasse Tyson's Favorite Hits" or "Which Captain is Your Favorite, and Why is it Picard?" then you'll be able to quickly locate them with these forms. Elaine from Sante Fe, NM (an Access Expert student) asks: I have someone who wants to enter info for each of the four tables in the database in one giant form. I've created four different forms with continuous subforms. I'm stumped as to how to get them to work together, flowing from the top table selection and then related data showing through the other three tables. How do you set this up? MembersI'll show you how to create another subform that has a list of students enrolled in each class. You will create a student table and subform, a junction table to hold a many-to-many relationship with the class table, and a combo box on that student form to select each student. Plus the Extended Cut shows how all of the initial tables and forms were built from scratch.
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! LinksSubform in a Continuous Form: https://599cd.com/SubformInContForm
IntroIn this video, I will show you how to create nested subforms in Microsoft Access, with an example structure of departments, courses, and classes, all set up as continuous forms. We will walk through how to organize your data using one-to-many relationships by building subforms inside each other, and I'll share tips on linking forms, hiding unnecessary ID fields, and improving how your forms look and function. If you need to work with related tables in a single parent form, this tutorial will help you set up a system that is both organized and easy to use.TranscriptWelcome to another TechHelp video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.In today's video, I'm going to show you how to make nested subforms. That's a subform inside of another subform inside of a parent form. All three of these will be continuous forms. They will be list forms. Here you can see in the example we've got departments, with each department having multiple courses and each course having multiple classes in it. Today's question comes from Elaine from Santa Fe, New Mexico, one of my Access Expert students. She says, I have someone who wants to enter info for each of the four tables in the database in one giant form. I've created four different forms with continuous subforms. I'm stumped as to how to get them to work together, flowing from the top table selection and then related data showing through the other three tables. How do you set this up? Well, Elaine, you could do this by setting up multiple continuous subforms. I'll set it up with three of them for you. We'll do a fictional college. We'll do departments, and each department will have multiple courses, and each course will have multiple classes in it. Let's see how that works. You can see here on the title screen, I've got departments, and then each department has multiple courses, and then each course has multiple classes. To create nested subforms, I need three separate tables. I'm going to do departments, courses, and classes. Here's my list of departments: math, astronomy, physics, and so on. Inside of those, you'll have courses, like the math department will have algebra, trigonometry, calculus, and so on. Then, each course type will have a list of classes, like algebra 101, algebra 102, and you could put additional details in here if you want to. This is simply just for the illustration of the class. Now, for each of these, I've created a continuous form. Here's department. Here's courses. Each course has a department ID. That's how you make that relationship. I've got lots of other videos on making relationships. You can basically see right here. Department 1 is math, and over here there's department 1, all three of these actually. Department 1, and those guys, and they all have their own course ID, foreign key, primary key. Then, inside of courses, we've got classes. There's the classes. Here's the course ID, and this is the course ID. Algebra's got those two. What I want to do is make these nested subforms. Inside the department form, we're going to have the course form. Inside the course form, we'll have the class form, and so on. I actually have another video that talks about putting a form inside of another form if it's a list form, if it's a continuous form. I did a new three of them and I got some extra tricks for the members at the end of this video, so keep watching. You're going to learn something. The first thing we're going to do is take this class form and put it inside the course form. Open up the course form to design view. Open this guy up and we're going to put it right down here in the subform. Grab the class form, click, drag, drop. It says a form of the subform object can't have its default view property set to continuous forms. Sure it can. Hit OK. Delete that label that comes up there. Move this guy up where you want it, maybe right about there, because I want the course ID to line up onto the course ID. Maybe make this a little bit bigger if you want. OK, like so. Then, go up into the property of the form and you'll see it changed to single form. Just change it back. That's all. Like I said in the other video, I don't know if that's a bug or if it's by design, but it will let you get away with it. Save, close, and now open up that course form. There you go. You can see these are linked up. Now, let's put the course form inside the department form. This is the extra step. Design view. Actually, let's close this one. Open up department form. Design view. Most of the same thing again. Drag this guy down like that. Grab the course form, drag and drop. Same error message. OK. Let's delete that label. Slide this over to the left a little bit like that. Do one of these. Do one of those. Change this back to continuous forms. Now open it up. There we go. There's not a lot of room inside of this one, though, so let's see if we can adjust that a little bit. Just make this subform a little bit bigger. There we go. Save it, close it, and now let's open it back up again. There we go. That's perfect. I just didn't make this subform big enough. Now you can see here's my departments, the courses, and the classes. Astronomy, physics, doesn't have anything. Literature. I'm on Star Trek in this one. Who's your favorite captain and why is it Picard? Lord of the Rings, we've got favorite hobbit potato recipes, and so on. If I want to add another Lord of the Rings title, I can come right down here. There's Gandalf Hat, and it automatically fills in the course for me. If you don't care about seeing all these IDs, go ahead and hide them. They're really only for Access to make relationships. We don't need those unless you want to put your own kind of identifier in there. They've got to be present. It's easier to start with the inside one first, design view. They have to be on here to make relationships with, but what I like to do is move them over here in the middle. Click on them like that, right click, properties, visible to no. Then delete that. Delete that. We'll put right here, make this label just "class" or "classes," whatever you want the label to say. OK, slide that over like so, make this smaller now. Let's see what's in there. If you can't resize, that means something's blocking it. Save it, close it. Do the same thing with the courses, design view. Get rid of these. Right click, properties, visible, no. Still want them on here. Don't need to look at them. Slide this guy over a little bit. Leave it indented just a tiny touch, like so. Get rid of this and this. Actually, let's leave the course one. This would be the courses. Save it, close it. Finally, design view. Slide that department ID over here. Slide that over. I forgot to make that invisible. Right click, position, send it back so this guy's in the front. I want him there for design view. Visible is no. This will be "department" or "departments," whatever you want it to be. We'll stay plural since that first one's plural. Save it, close it, open it up. There you go. Now that looks pretty, and you don't have to worry about IDs. They're all in there. We'll put classes in our astrophysics department. "Cosmos," and then "Neil deGrasse Tyson's Favorite Hits." I'm a big fan, so I have to put him in there. Got to get even one of my classes. Now you can come in here, and you can put all the supporting data in here as well. I just built that simple form for class. You can put in your courses here. You can put other information, like who the instructor is and all that. For the classes, you can put individual classes in there, like their schedule, what room they are in, and so on. In fact, that could be a fourth level, sessions. Each class might have multiple sessions. You've got Access for Beginners. Session A might be Monday, Wednesday, and Friday, 6 p.m. in Bell Hall, room 101, or whatever. This gives you a blueprint of how to set something like this up, and it's easier to work with it like this instead of having multiple extra forms that pop up. Having subforms like this is the easiest way to organize your data, and each one should show you the layers below it. Want to learn more about nested subforms in the extended cut? Not only will I show you how I built all of this stuff - all the tables, all the forms from scratch from the beginning - but I will also show you how to set up a students table and student subform. We'll create a junction table with a many to many relationship, so many students can belong to each class, and each class can have many students associated with it. We'll build a combo box, and then we'll put that inside the classes subform. Now you can pick your department, the course, the class inside each course, and then pick the students that are inside each one of these classes. That's another level. This is a many to many relationship. These are all one to many relationships. Once again, this is the members only extended cut edition of the nested subforms video. How do you become a member? Click on the join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the join button, you'll see a list of all the different perks that are available: silver, gold, platinum, and diamond. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making them, and they'll always be free. If you enjoyed this video, please give me a like and share. Click on the subscribe button to subscribe to my channel and be notified of any new releases. Check for additional resources down below the video. Click the show more button, and you'll see a list of other links to other videos, downloads, resources, lessons, and lots more. If you have not yet tried my free Access Level One course, it's three hours long. You can find it on my website or on my YouTube channel. If you like Level One, Level Two is just $1, and that's free for my members. Want to have your question answered in a video just like this one? Visit my TechHelp page, and you can post your question there. Also, be sure to stop by my Access forum on my website. Also look for me on Facebook, Twitter, and of course, YouTube. Once again, my name is Richard Rost with accesslearningzone.com. Thanks for learning with me, and I'll see you next time. QuizQ1. What is a nested subform in Microsoft Access?A. A subform inside another subform within a parent form B. A form with multiple unrelated tables C. A split form with details and datasheet views D. A form that opens in a separate window Q2. In the example shown, which entities are demonstrated as having a one-to-many relationship with each other? A. Students to departments to schedules B. Departments to courses to classes C. Instructors to rooms to students D. Classes to sessions to instructors Q3. Why are primary and foreign keys important in setting up nested subforms? A. They determine the color scheme of forms B. They allow users to search records more quickly C. They establish relationships between tables so data can be linked in the subforms D. They are used to generate reports Q4. When embedding a continuous subform inside another form, what common issue does Access display? A. A warning that continuous forms cannot be subforms B. A message about missing table relationships C. An error that the form cannot be displayed as a datasheet D. A prompt to convert the form to single form view Q5. What should you do if Access changes your subform's default view to single form when embedding continuous subforms? A. Redesign the form layout B. Ignore the change and proceed C. Change it back to continuous forms in the property sheet D. Delete and rebuild the entire form Q6. What is one way to hide ID fields in your forms for a cleaner look while maintaining necessary relationships? A. Delete the fields from the table B. Set the field's Visible property to No in form design view C. Rename the ID fields D. Lock the fields Q7. Why is it beneficial to organize your data with nested subforms? A. It requires less storage space B. It allows data from unrelated tables to be seen together C. It visually organizes related records in a structured, layered manner D. It limits user access to certain tables Q8. What can be added as a fourth level to the nested subforms example described in the video? A. Sessions table for individual class schedules B. Instructors table for each department C. Exams table for each student D. Grades table for each course Q9. In the context of this video, what type of relationships are represented among departments, courses, and classes? A. Many to many B. One to one C. One to many D. Self-referencing Q10. What does the extended cut (members only) version of the video promise to cover? A. Advanced macros and VBA coding only B. Setting up students table, many to many relationships, and a student selection subform C. Creating mail merge documents D. Building Excel charts Q11. What is the purpose of using a junction table as described in the extended cut? A. To combine department and instructor data B. To display forms as datasheets C. To establish a many to many relationship between students and classes D. To limit the number of available classes Answers: 1-A; 2-B; 3-C; 4-A; 5-C; 6-B; 7-C; 8-A; 9-C; 10-B; 11-C 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. SummaryToday's video from Access Learning Zone covers how to build nested subforms in Microsoft Access. Specifically, I'll walk you through setting up a form structure where you have a subform inside another subform, all within a main parent form. Each of these forms will display as a continuous, or list, form.For our demonstration, imagine we are managing information for a fictional college. The data structure involves departments, each of which offers multiple courses, and each course consists of several classes. This video is inspired by a question from a student who wanted to enter data for four related tables in a single, comprehensive form. She had already created multiple forms and subforms but was unsure how to connect them so that data flowed smoothly from one table to the related entries in the others. To set this up in Access, you will want to create three separate tables: Departments, Courses, and Classes. The Departments table might include entries like Math, Astronomy, and Physics. The Courses table will store individual courses, with each linked to a department using a department ID. For example, Math might include courses like Algebra, Trigonometry, and Calculus, each with the appropriate department ID as a foreign key. Moving one level deeper, the Classes table links back to courses with a course ID. These would represent specific class instances like Algebra 101 or Algebra 102. The next step is to create a continuous form for each of these tables. The Department form lists departments. The Course form lists courses, each associated with a department. The Class form lists classes, each connected to its respective course via the course ID. You should ensure that each foreign key is set up properly to maintain these relationships. Now, to build the nested subform arrangement, you start by embedding the Class form into the Course form. In design view, insert the Class form as a subform into the Course form. You may encounter a warning indicating that continuous forms cannot be used as subforms, but you can safely proceed and simply reset the default view of the subform back to continuous forms if it is changed. Once you have the Class form inside the Course form, save and close it. Then, open the Department form in design view and insert the Course form as a subform. Again, if you get the same error message, just reset the subform's default view to continuous forms. Adjust the size of the subform areas as needed so everything is visible and organized. Once you have embedded the forms, you will be able to view all departments, with each department displaying its related courses and each course showing its associated classes, all in a single, scrollable view. Access will automatically keep the records properly linked as you add or modify data. If you find the display cluttered with ID fields and you do not need users to see those, you can hide them. These IDs are necessary for establishing relationships, but you can set the fields' visibility to "no" in properties so they do not appear on the form, keeping the user interface cleaner. Adjust label names and layout for clarity and usability. You can further customize these forms by adding additional fields. For example, you might want to assign instructors or specify classroom locations and schedules for each class. This structure can be expanded by adding yet another subform level, such as sessions for individual class meetings, demonstrating how flexible nested subforms can be. For those interested in more advanced techniques, the extended cut of this lesson goes into additional detail. There, I show not only how to build the tables and forms from scratch, but also how to add a students table and student subform. This includes the creation of a junction table to handle a many-to-many relationship, where many students can belong to many classes. You will learn how to incorporate a combo box within the Classes subform so you can select students within each class, allowing for comprehensive data management. The extended cut is available to members, along with many other resources, including live video and chat sessions. To learn more about becoming a member and the different membership levels and perks, you can check out my website. Remember, all the TechHelp videos are always free, so you can continue to access these lessons and send in questions you would like answered in future tutorials. I also encourage you to explore my free three-hour Access Level One course, which you can find on my website or YouTube channel. If you enjoy it, you can advance to Level Two for just one dollar, or get access as a member. If you have questions or need further help, visit my TechHelp page to submit your questions. You can also participate in the Access forum on my website or connect with me through Facebook, Twitter, and YouTube for updates and community support. 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 ListCreating nested subforms in Microsoft AccessDesigning three related tables: departments, courses, classes Creating continuous forms for each table Setting up foreign key relationships between tables Embedding a class subform into the course form Handling continuous forms as subforms and error messages Embedding the course subform into the department form Adjusting form and subform sizes for better display Hiding key fields from users for cleaner interface Setting form control properties to make IDs invisible Customizing form labels for clarity Adding data to nested subforms at different levels Discussing potential for additional subform levels (e.g., sessions) Using nested subforms for effective data organization |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access Multiple Nested Continuous Subforms microsoft access subform ms access subform ms access multiple subforms example ms access filter subform ms access create subform ms access continuous form with subform PermaLink Nested Continuous Subforms in Microsoft Access |
