ajetrumpet
Banned
- Local time
- Today, 01:06
- Joined
- Jun 22, 2007
- Messages
- 5,638
Subform Techniques & Referencing
I have attached a database file that shows various Subform Techniques, and syntax that can be used for them. Bear in mind the syntax blocks I have used to accomplish these techniques are not the only ones that can be used. Subforms are incredibly complex creatures, and the syntax variations to use when trying to perform these tasks are ENDLESS. I have simply used the most consolidated version of Visual Basic coding lines that I could think of. The techniques that are shown in this file include the following:
1) Filter Subform Records Based on a Combo Box Selection.
2) Set Focus to a Subform Control from a Main Form.
3) Set Focus to a Subform of a Subform Control (Nested Subform Control) from a Main Form. <--- This is more complicated than you think!
4) Go To (and Set Focus To) a New Subform Record from a Main Form
5) Populate a Main Form Control Value From a Nested Subform Control.
6) Populate a Main Form Control with a Nested Subform Control Value.
7) Go To (and Set Focus To) a New Subform Record from a Nested Subform (Subform of a Subform).
If all is Clear as Mud now
)), the file illustrates all of the above techniques. I tried to make it as simple as possible, and there are notes above every line of code. This was all done with Access 2003, and I am not sure of any code changes that would have to be made with Access 2007, although I'm sure there is not much, if any.
Another good thing about working with subforms is that the coding that can be used, as I mentioned above, is ENDLESS. There are literally countless ways to use the reference syntax when doing this stuff, so experimentation on your part would be your best bet if you can't get a subform to reference correctly the first time. A good example of interchangeable syntax is the following few lines of code, which for purposes of operation within the database, perform exactly the same way:
Each of the above code lines populates a Main Form Control with a Nested Subform's Control, with the focus being on the Nested Subform at the time of execution. In this case, the general subform referencing syntax was used, but in other subform techniques, it is not so simple. But rest assured, whatever you are doing with subforms, there is ALWAYS more than one way to code it and reference it. So if you can't get it with one piece of syntax, TRY ANOTHER. It eventually works.
Hopefully this will give more insight into the basic idea of what it is like to work with subforms and their hidden properties that seem to always be missed!
EXPLANATION OF SUBFORM CONTROLS and CONTAINERS
In short - The control IS the container. The container is the control. CONTAINER = CONTROL
In order to put a subform on a form object, it has to become a control, just like a text box or combo box. As a matter of fact, weather or not you drag and drop your subform from the database window onto your form, or just add one with the toolbox, it is added as a form control initially. Subforms also have two names associated with them - the Source Object and the Name. The name property is the name of the control, and the source object is the name of the subform that you will find in the database window under the "forms" section. These are very different. It is important to understand this. A subform is nothing more than a control just like a text box or combo box is. The ONLY difference is that a subform IS a form object, and because of that, you have to include the .Form portion in your referencing code associated with it. Quick Example:
The purpose of that code is to tell access that the control you want is ON (or inside) the form object. If you do not understand this, just memorize it as a rule. This is the heart of the building blocks (structure) of the Access program as a whole. It's almost like building a pyramid. Is a text box an object? NO (but it is a property, which is irrelevant to this thread). Do you ever see a text box inside of a text box? Or a button inside of combo box? Of course not! I hope this clears up a bit of confusion you may had about this subform subject too. If not, at least you have more knowledge, even if you still don't understand it. Where to find the Source Object property:
USEFUL LINKS
http://support.microsoft.com/kb/209099
http://support.microsoft.com/?kbid=294212
http://support.microsoft.com/kb/113352
http://allenbrowne.com/casu-04.html
I have attached a database file that shows various Subform Techniques, and syntax that can be used for them. Bear in mind the syntax blocks I have used to accomplish these techniques are not the only ones that can be used. Subforms are incredibly complex creatures, and the syntax variations to use when trying to perform these tasks are ENDLESS. I have simply used the most consolidated version of Visual Basic coding lines that I could think of. The techniques that are shown in this file include the following:
1) Filter Subform Records Based on a Combo Box Selection.
2) Set Focus to a Subform Control from a Main Form.
3) Set Focus to a Subform of a Subform Control (Nested Subform Control) from a Main Form. <--- This is more complicated than you think!
4) Go To (and Set Focus To) a New Subform Record from a Main Form
5) Populate a Main Form Control Value From a Nested Subform Control.
6) Populate a Main Form Control with a Nested Subform Control Value.
7) Go To (and Set Focus To) a New Subform Record from a Nested Subform (Subform of a Subform).
If all is Clear as Mud now
)), the file illustrates all of the above techniques. I tried to make it as simple as possible, and there are notes above every line of code. This was all done with Access 2003, and I am not sure of any code changes that would have to be made with Access 2007, although I'm sure there is not much, if any.Another good thing about working with subforms is that the coding that can be used, as I mentioned above, is ENDLESS. There are literally countless ways to use the reference syntax when doing this stuff, so experimentation on your part would be your best bet if you can't get a subform to reference correctly the first time. A good example of interchangeable syntax is the following few lines of code, which for purposes of operation within the database, perform exactly the same way:
Code:
1) Forms("MainForm").Control = Me.Control
2) Forms!MainForm!Control = Me.Control
3) Forms(Me.Parent.Parent.Name).Control = Me.ControlHopefully this will give more insight into the basic idea of what it is like to work with subforms and their hidden properties that seem to always be missed!
EXPLANATION OF SUBFORM CONTROLS and CONTAINERS
In short - The control IS the container. The container is the control. CONTAINER = CONTROL
In order to put a subform on a form object, it has to become a control, just like a text box or combo box. As a matter of fact, weather or not you drag and drop your subform from the database window onto your form, or just add one with the toolbox, it is added as a form control initially. Subforms also have two names associated with them - the Source Object and the Name. The name property is the name of the control, and the source object is the name of the subform that you will find in the database window under the "forms" section. These are very different. It is important to understand this. A subform is nothing more than a control just like a text box or combo box is. The ONLY difference is that a subform IS a form object, and because of that, you have to include the .Form portion in your referencing code associated with it. Quick Example:
Code:
me.subform1.FORM!textbox1 = VALUEUSEFUL LINKS
http://support.microsoft.com/kb/209099
http://support.microsoft.com/?kbid=294212
http://support.microsoft.com/kb/113352
http://allenbrowne.com/casu-04.html
Last edited:
