Subforms, subforms, subforms…
It is a word that generates fear into the sturdiest Access developer. How do you reference a control on a subform? How do you reference a subform to requery it? How do you reference a subform so you can requery a control on it?
Well, fear no more. I am going to try to explain to you how you can EASILY remember how subforms work and how you need to reference them. It only took me almost ten years to understand this, and I’m hoping that it will only take you ten minutes after I am through explaining it.
WHEN IS A SUBFORM REALLY NOT A SUBFORM?
Most people, when they first get started with Access think, that when they click the subform/subreport button, that their subform is on the main form. And, that is somewhat true. What I mean is that they don’t realize that there is something in which their subform resides.It is a control that sits on the main (or PARENT)form and is basically the interface between the two forms, or you can say it HOUSES the subform.
So, since there is a subform control which is basically the interface between the main form and the subform, there needs to be some way in which you are able to tell the main form which object you wish to refer to when writing your code. Now, a key thing to remember is that the subform control and the subform itself can have the same name. Actually, in this case it does simplify matters to some extent if your subform control and your subform ARE named the same. It simplifies things somewhat in that you don’t need to go try to figure out the control name if you already know the name of the subform. However, the two don’t need to be named the same.
KEY CONCEPT: When referring to subforms on a main form you will refer to the CONTROL NAME (screenshot says “CONTAINER” but it is the subform control)and not the subform name. Remember that, it is important and it is something that trips up many people when they are trying to figure out why their code isn’t working.
How do we refer to a form within the subform container?
Objects haveproperties and methods within Access. There are two things about that. One, when referring to a property or method on the CONTROL, you just refer to the subform CONTROL. But, if you are wanting a property or method on the subform itself, you must tell Access that you want the form, not the subform control. How do you do that? You use the word FORM. Amazing, huh? And, incredibly simple. The times when we need to use the word FORM is when we want a property or method of the subform.
So, if we need to use the word FORM to refer to the actual form within the subform control, how do we refer to a control on the subform? Well, logic would tell you that you would need to tell Access that you were referring to a control on the subFORM itself and not on the control, which doesn’t have controls. So, we would definitely need to usesubform CONTROL name AND the word FORM AND the control name. The example would include: MySubformControlName.Form.MyControl as the reference. But, we’re not quite done. There are a couple of other concepts which we need to explore and then you’ll be ready to attack it.
We need to figure out if we are using our code in the subform or on the main form. If it is on the main form (and if it was in an event on the subform CONTROL, it would count as if it were on the main form), then we can just use the shortcut keyword ME to refer to the main form name. So, Me.MySubformControlName.Form.MyControl would be the reference if we were referring to it in code on the main form. If the code was on the subform then it would just be modified to Me.MyControl because it is referring to items on the form where the code resides.
Now, if you need to refer to another form, you can use this, what I’ll call somewhat confusing, way of doing so:
I say that because it is okay when dealing with one level but suppose you had a subform four levels deep. In other words, what if you had a form with a subform with a subform with a subform with a subform. That can be a nightmare trying to figure that one out. But,I said you canremember how to do this because there is an easier pattern to remember. Instead of Forms! let’s use Forms() to get the form from the forms collection. So, if our main form is named “frmTest” then we would use Forms(“frmTest”). Next, we already know that the subform container is a control. So, let’s use the CONTROLS collection by utilizing Controls(“MyControlName”). We put those two together and we have:
Then, we have to refer to the control on the subform itself. Remember we have to tell Access that it is the form we want to refer to and the control on that form. That means we would use
and then we have to add the control we want to reference and that control is in the subform’s controls collection, so it is just another instance of .Controls(“controlname”) which will get us to our location. So the result is:
Now, what is a thing to remember when referring to a control on a subform this way? If you want to get a property, or method, you need to use it explicitly as there is no default available when using syntax like this. So, if we wanted to get the value of MyControlName then we would need to use:
But you say,.”that is so long compared to the other”, as we shared above:
(as above): Forms!YourMainFormName.YourSubformControlName.Form.YourControlName
Yes, it is a little longer in typing the one with Forms(“frmTest”) … etc., out. However, the ease of remembering the pattern is what will help you. Because, you can substitute variables for the names of the forms and controls but the syntax is exactly the same. So, if I was referring to a form variable of strForm and a subform of strSubForm and a text box on the subform of strControl then I would refer to it this way:
no special syntax change required. And, in the case of the four levels deep subform it would be easy to do with:
Forms(“MainFormName”). Controls(“MySubformControlName“). Form.Controls(“SubformControl2”). Form.Controls(“SubformControl3”). Form.Controls(“SubformControl4”). Form.Controls(“MyTextBox”).Value
So, hopefully that helps you with understanding how to reference subforms. The concept is just slightly different for referencing subforms within control sources but we will cover that in another tutorial.