Control Name in Nested Subform
By Richard Rost
6 months ago
Reference a Control in a Nested Access Subform In this Microsoft Access tutorial I will show you how to properly reference a control on a nested subform, meaning a subform inside of another subform, and explain the correct syntax and naming conventions required to access values in these scenarios. You will also learn about common issues related to subform naming, why they happen, and how to fix them when embedding multiple forms within each other. Miles from Raleigh, North Carolina (a Platinum Member) asks: I'm trying to follow your naming conventions, but I'm stuck trying to reference a control on a subform that's inside another subform. It's not working the way I expected. Can you show me how to properly reference a control on a nested subform? MembersThere is no extended cut, but here is the file 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!
PrerequisitesRecommended CoursesTips- Double-check your Subform's Name (it gets the Caption property)
- Syntax: Forms!CustomerF!OrderF.Form!OrderDetailF.Form!SumExtPrice
Keywords TechHelp Access, nested subforms, reference nested subform control, subform naming conventions, form control referencing, subform object names, getting value from nested subform, main form subform relationship, debug compile vba form, access subform property, resolving subform name errors, nested forms best practices
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
I have shown you how to get the value from a form. I have shown you how to get the value from a subform. But I have not shown you how to get a value from a subform's subform. In other words, a nested subform.
So we have got a form out here, and then that has a form inside of it, and that has a form inside of it, and I want to get that value right there. How do we do that? That is the focus of today's video.
If you want it just to, some people are like, just get to it. Well, there it is. I am going to explain it.
Here we go. I am not only going to explain it, but I am going to show you a problem that Miles was having and how to fix it, and it comes up a lot. Miles from Raleigh, North Carolina, one of my Platinum members, says: I am trying to follow your naming conventions, but I am stuck trying to reference a control on a subform that is inside another subform. It is not working the way I expected. Can you show me how to properly reference a control on a nested subform?
Yes, I certainly can. But first, a couple of prerequisites. This is an expert level video, which means it is beyond the basics, but we do not need any programming for it. Experts are kind of like the meat in the sandwich of beginner and developer.
First, make sure you watch this video so you understand how to get the value from an open form. And we talk about actual forms and fields in a subform right there. That is how you get the field value from just a standard subform.
If you do not know what subforms are, watch this video. If you do not know what nested subforms are, watch this video. It is a form inside a form inside a form. And yes, you can do this.
Here I am in my TechHelp free template. This is a free database you can grab off my website if you want to. Now in here, I have got customers, a basic single customer form. I have a button here to open up an order form.
The order form is a main order form that has details about the entire order, and a subform inside of it that has all the line items, the details form. If you look at these, it is form-subform.
Now, what if you want to take that whole thing and put it inside the customer form? You can do that.
Go to design view, make this a little bit bigger, and make some room for it down here. Grab the order form from over here, click, drag and drop. Then it will drop it as a subform object right inside your customer form. I am going to delete the little label that comes with it because we do not need that, and then I am going to slide this over here and make sure you have got room down here in the bottom. Everything looks good. Save it, close it, and open it back up again.
There we go. We have got each customer and all their orders are down here. My screen is not big enough. Let me make this smaller inside here so we can see a little better. I am just going to make the subform a little bit smaller so everything fits on the screen.
These are tricky, by the way. If you want to click on it, you have got to click on it to get to this subform. If you want to get to the parent, you have got to click off of it and then click on that guy. See how these objects work? It is a little tricky, but it just takes some practice.
Make sure everybody fits nice. All right, beautiful, perfect, beautiful.
So now I want to get that value right there. Let us say I want to get it from the main menu. I want to click this button, and I want to status it in here.
The way that it works is, I am going to just bring up Notepad just to show you. From the main menu, I would say I want... so I am going to just say message box.
It is going to be Forms!CustomerF, which is the main form. Then inside CustomerF, what is the object name of the first subform? That is my order form, so that should be OrderF. Now I want to get inside of that form's properties, so that is where you say .Form. Now, what control inside of this form am I looking for? Now I am looking for the subform control inside there. That is my OrderDetailF.
So inside the order form, now I want OrderDetailF, and then inside that guy, now I need its form property, and then the field that you want. In this case, it is some ExtendedPrice. That is the name of that guy right there.
Now, you think this is all going to work perfectly fine because those are the names of my forms, right? OrderF, OrderDetailF.
All right, let us plug this into a button. Come back out here. I am going to close this. Let us put that inside the Hello World button. My VBA editor is on the other screen, let me bring it up here and resize it.
So instead of status Hello World, I am going to put that in there.
Debug compile, it compiles fine, everything looks great. Let me close it, save it, open it. Let me open up a customer form. So it is here, it exists, it will not work if it is not open. Let me hit the button and, uh-oh, TechHelp free template, cannot find the field OrderF referred to in your expression.
Why not? OrderF is... I mean, that is the thing.
Now, this is the problem that Miles was having. He had everything correct with this formula. Everything was fine, except look at the name of this subform. Oops, double click. It is Orders instead of OrderF. Why is that?
Now, this is one of those pet peeves I have with Access. I do not like the way this behaves like this. If I could get this to the Access team, I would tell them you guys should change this because look, if I go into OrderF, go to design view, go to its properties, I set Orders as the caption. That shows up up here. That is what Access will use to name the subform if you drop it in a form.
I hate that. I do not like that, because you could put in here "This is the order form" as your caption, and I want that caption to appear when I open up the order form for the user. "This is the order form," but watch this now. I go to the customer form. Let me delete this. If I drop it in now, see, "This is the order form." There is the label, and look at its caption. "This is the order form." So I hate that. I cannot stand it. This has happened to me before.
Basically, what you want to do is, as soon as you drop that subform object in there, rename it to whatever you expect it to be, OrderF in this case. And this guy should be fine because I do not think I put a caption on it. Yep, OrderDetail. Now that it is named correctly, save it, close it, open it.
Now I can get its value. See, that was the problem Miles was having. I am sure that is going to happen to at least one of you. It has happened to me before. That is the naming convention right there.
Let me bring up the master slide again. Move it down here. Love my PowerPoint.
This is it. Just remember that syntax: Forms!CustomerF!OrderF is the first subform. Then once you go into a form, you have to go .Form. Then the name of the control on there, which is a subform, .Form, and you can keep going.
There is a limit. I do not remember offhand what it is. You could probably ask ChatGPT, or Google it if you still do that.
Of course, I hate not knowing something. I had to ask ChatGPT. It says seven, but only three levels are fully supported. I have had forms with probably five or six nested. In fact, I just did a video a little while ago where we did the cascading combo boxes with like five levels, and I had country, and then inside of that was state, then county, then town. So I know I built at least five myself. I guess seven is the official one, but use them sparingly.
There you go.
If you like this kind of stuff, this is the kind of stuff I teach in my Access Expert level courses. The beginner stuff for all, you know, the beginner stuff, the basic stuff, the developer stuff. So if you want to get into programming and coding, that is really cool. But the expert stuff will go through all the nitty gritty of the way that controls work and stuff like this. Check them out.
That is going to do it. Folks, that is your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I will see you next time.
TOPICS:
Referencing a control on a nested subform in Access
Proper syntax for nested subform references
Adding an order subform into a customer form
Identifying the source of subform naming errors
Renaming subform controls to match expected references
Understanding how form captions affect subform names
Accessing control values on deeply nested subforms
Troubleshooting "cannot find the field" errors for subforms
Demonstration of nesting forms within forms in Design View
Limitations on the number of nested subform levels in Access
COMMERCIAL:
In today's video, we're learning about how to properly reference a value inside a subform's subform in Microsoft Access. I will show you the right syntax for getting values from nested subforms, explain the common mistakes that can trip you up, and cover how to make sure your control names match up to avoid errors. You'll pick up tips on working with subform controls, naming conventions, and get a clear step-by-step demonstration to avoid the problems that happen if you do things the wrong way. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is a "nested subform" in Microsoft Access?
A. A set of forms that use the same data source
B. A subform placed inside another subform within a parent form
C. A form that contains multiple unrelated subforms
D. A subform that uses a different database
Q2. What was the main problem Miles was having with referencing a control on a subform's subform?
A. The database file was corrupted
B. The name of the subform control did not match expectations due to automatic naming from the caption
C. He was trying to use VBA code in a macro
D. He forgot to set the Record Source property
Q3. In the context of referencing a control, what is the correct syntax to access a value in a nested subform?
A. Forms!MainForm!FirstSubform!SecondSubform!FieldName
B. Forms.MainForm.Subform.Form.FieldName
C. Forms!MainForm!FirstSubform.Form!SecondSubform.Form!FieldName
D. Forms.MainForm.Form!FirstSubform.Form!FieldName
Q4. Why did the reference Forms!CustomerF!OrderF.Form!OrderDetailF.Form!ExtendedPrice fail initially in the example?
A. The ExtendedPrice field did not exist
B. The subform control was not open
C. The actual subform control name was different from what was used in the reference
D. VBA does not support referencing nested subforms
Q5. How does Access initially name a subform control when it is dragged onto a form?
A. It always uses the form object name
B. It uses the caption property of the form
C. It uses a random alphanumeric string
D. It matches the table name used as the record source
Q6. What should you do immediately after adding a subform to a form to ensure correct referencing?
A. Convert the form to a report
B. Remove all field controls
C. Rename the subform control to the desired name
D. Add a default value to all fields
Q7. What is the reported official limit to the number of nested subforms in Access?
A. Three
B. Five
C. Seven
D. Ten
Q8. According to the video, how many levels of nested subforms are fully supported in Access?
A. One
B. Three
C. Five
D. Seven
Q9. After correcting the naming issue with the subform, what was the result when referencing the nested control value?
A. An error still occurred due to unsupported nesting
B. The value was successfully retrieved
C. The application crashed
D. The field returned a NULL value
Q10. Why is it important to follow naming conventions for subform controls in Access?
A. It ensures forms open faster
B. It allows Access to automatically fill in related fields
C. It allows your code to correctly reference controls and fields within subforms, especially when nesting
D. It reduces database file size
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-C; 7-C; 8-B; 9-B; 10-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.
Summary
Today's TechHelp tutorial from Access Learning Zone covers how to properly reference a value in a nested subform within Microsoft Access. Many people have already seen how to access a value from a main form or a single subform, but it can get confusing when you need to reference a control in a subform that's inside another subform. This situation comes up more often than you might think, and it can trip up even experienced users.
To start, let me set the stage so everyone understands what I'm describing. Imagine you have a form. Inside that form, there is a subform. Inside that subform, there's another subform. The goal here is to retrieve a value from that deepest, innermost form.
Before I walk you through this process, I should mention this lesson is intended for Access users who are at the Expert level. You do not need to write any code for this, but you should be comfortable working with forms and understand how subforms are embedded inside other forms. If you are not yet familiar with subforms or nested subforms, I recommend reviewing those foundational topics first.
Now, let me give you a real-world scenario that one of my students, Miles, encountered. He wanted to follow proper naming conventions but ran into issues when referencing a control in a subform nested within another subform. What he expected to work simply wasn't functioning as planned. This is a very common source of headaches, particularly if you aren't aware of how Access assigns names to subform objects when you add them to a parent form.
To illustrate the process, I will use my TechHelp free template as an example. This is a simple database available on my website. In this template, we have a Customers form. Each customer can have multiple orders, and there is a button to open an Order form. The Order form itself contains an order details subform that lists all of the line items for the order. So, there is a form with a subform inside of it - the classic form-subform pattern.
But what if you want to nest this entire setup even further and place it all within the Customers form? You can do this by going into design view, making some space, and then dragging the Order form directly onto the Customers form. When you do this, Access creates the Order form as a subform object within Customers.
At this point, you can see each customer along with all their related orders displayed in the subform. Each order, in turn, has its details shown in another nested subform.
Navigating nested subforms in design view can take a little practice because you have to be careful about which object you are selecting. Sometimes you need to click off one object and back onto another, but with a bit of experience, it becomes second nature.
Let's turn our attention to retrieving a specific value from that deepest subform. Suppose you want to trigger an action using a button on your main menu, and that button needs to access a value from the nested detail subform. You would typically reference it using the syntax:
Forms!CustomerF!OrderF.Form!OrderDetailF.Form!FieldName
Let me break this down into logical steps for clarity. You start with the main form object, CustomerF. Inside that, you reference the first subform control, which is often named OrderF. Once you move into the subform, you append .Form to access its forms collection. Then you reference the control representing the next subform, OrderDetailF. Again, you add .Form to move into this inner form, and finally, you specify the actual field or control you want, such as ExtendedPrice.
At this stage, it might appear that everything is lined up correctly, but this syntax only works as long as the names of your subform objects match what you expect. Access can trip you up here because when you drag a form onto another as a subform and if the form has a caption set in its properties, that caption might become the default name for the subform control within the parent form. This means you might think your subform is called OrderF, but Access may have actually named it Orders or even used the form's caption text as its object name. If you are not paying attention, your references will not work.
This is exactly what happened to Miles. He was referencing OrderF because that was the source form's name, but Access had actually given it the name Orders in the parent form's list of controls because of the caption. This is one of those quirks in Access that can be very frustrating. I always recommend renaming the subform control in the parent form to match your intended naming convention right away, so you don't run into this problem. That way, your references in VBA or macros will always point to the right control.
Once the subforms are named correctly, referencing the value works just as expected. Remember to always check the names of your subform controls in the parent form's design view, not just the names of the underlying form objects.
One more point to keep in mind: Access supports a certain depth for nesting subforms. While ChatGPT might say up to seven levels are possible, realistically, only three levels are fully supported. In my own experience, I have managed to go five or six levels deep, such as when building cascading combo boxes with country, state, county, town, and so on. However, I suggest keeping your form structures as simple as possible since complexity grows quickly as you nest deeper.
If you enjoy learning about these advanced Access topics, you may want to explore my Expert and Developer level courses. These go into more detail on subjects like control referencing and customizing forms.
That wraps up today's lesson. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.
Topic List
Referencing a control on a nested subform in Access
Proper syntax for nested subform references
Adding an order subform into a customer form
Identifying the source of subform naming errors
Renaming subform controls to match expected references
Understanding how form captions affect subform names
Accessing control values on deeply nested subforms
Troubleshooting "cannot find the field" errors for subforms
Demonstration of nesting forms within forms in Design View
Limitations on the number of nested subform levels in Access
|
