Sub-Forms

Sub-Forms

Subforms can easily display parent/child related data. This is the most common and useful feature. They can also show standalone data, often filtered with combobox(s) and other controls. But wait a minute actually we are not talking about a subform here, there’s no such thing as a subform, what you’ve actually got is a strangely named control a “subform/subreport control” The best way to describe this control is to call it a window on your form which looks into another form. I find it’s best to refer to it as a subform window! Now we got that out of the way we can see the next use you can put the subform to, you can swap forms in and out of it. This window is capable of displaying different forms.

Subforms are the best and most useful feature of MS Access. Why? Because they allow you to show related data easily. I think subforms are so important and useful that I wrote a blog about them here:-

Excellent Blog by Microsoft

Over the years I have tried in various blogs and YouTube videos to explain subforms. I have never been entirely happy with my various attempts because they always seem to be lacking in something.

For sometime I have been planning to do a comprehensive Blog on Sub-Forms to try and correct this.

Since discovering this excellent blog by Microsoft:-  “Create a form that contains a subform (a one-to-many form)”  Found in this post:-  “Attempting to set subform data from on Current event on Main Form”  on  (AWF) Access World Forums.   I can see there is no longer a need for me to write a blog, because this Blog just about says it all.

My efforts will be far better placed in helping interpret this Microsoft blog, commenting on it, add extra info & code and enhance it with some YouTube videos. My first video “Subform Intro” is designed to whet your appetite.

Further Videos from my YouTube Channel will be added to this Blog page, with sections applicable to the Microsoft Blog highlighted…

… …

Subform - Intro

Subform - Intro

Video 1 (3:50)

This is the first video in my Blog on Subforms. I just want to whet your appetite and give you some idea of what can be done with Sub-forms! In this video I demonstrate how you can modify the SQL statement of a single subform displayed “FOUR TIMES” on a main form! This means you only have two forms, but it looks like you have five! Look at the advantages of approaching things this way. If you’d gone the route of having four separate “IDENTICAL”Forms displayed on your main form, and you wanted to make a subtle alteration you may find yourself editing every single form in your set of duplicates. However by providing different functionality to each instance of a SINGLE FORM with VBA code, you only need maintain that one form.

Video 1 (3:50)

Nifty Access YouTube Thumb Nail
Play Video

… …

Adding Hobbies to a Student Database

Master & Sub-Form

Video 2  (4:35)

In 5 Minutes, I demonstrate a “Student and Hobby” relationship.  In particular you will see how to set up a Main Form & Subform arrangement for handling and modifying, adding and editing the Data..

This video is also in my Many to Many Blog where the Video demonstrates one side of a Many to Many Relationship.

Video 2  (4:35)

Nifty Access YouTube Thumb Nail
Play Video

More Info on Hobbies/Student :-

… …

Lock - Unlock a SubForm

Lock - Unlock a SubForm

Video 3 (04:20)

Occasionally the database user might start editing data in the subform before entering data in the main form. This can result in the loss of information, confusion for the user – just not a very good thing to happen! You could lock the actual subform, however the subform resides inside a subform/subreport Control, and you can actually enable or disable this control. I demonstrate this in this YouTube video “Lock Unlock a SubForm – Nifty Access”

Video 3 (04:20)

Nifty Access YouTube Thumb Nail
Play Video

… …

The best way to think about it is that the subform isn’t actually a subform, it’s a normal form housed within a special control called a subform/subreport control. Not many people know about this control because MS Access automatically creates it when you place the “subform” on the main form.

I like to think of this subform/subreport control as a “window” displaying another form. The other unfortunate thing is by helping you in creating this subform/subreport control, Microsoft gives this intermediary control the same name as the form you are hosting within it, totally confusing you! To gain access to it with code you call on this subform/subreport control and then you call on its Form property which gives you access to the form contained within it.

Logically, you would think your code should be something like this:- frmMain.subFormInMainForm.MyTextBox But that doesn’t work because in that particular case the code is referring to a subform/subreport control, with the same name as your subform. You need to add an extra piece to the code to tell the code to look at the form within the subform/subreport control:- frmMain.subFormInMainForm.Form.MyTextBox

Although I criticize Microsoft for it’s unfortunate choice of name for the subform/subreport control, this approach actually makes sense, as structuring it in this way, a Form with a subform/subreport control, containing another form.

A form and Subform Are not just similar items, they are actually identical! The only difference being the intermediary control the “Subform/Subreport Control”. As you can see, there is nothing new to learn about a subform, it’s just a Form! Everything you learn to do with a form works with a subform. The only difference is that the subform is contained within a subform/subreport control which affects the way you think about accessing the “Subform”.

Once you understand this difference, once you acknowledge and understand the existence of the Subform/Subreport Control, then things fall into place. Microsoft has been very clever in adopting this structure as when you start writing more advanced VBA, you will see that it lends itself to simplifying your VBA code, because you don’t need to provide the name of the form within the subform/subreport control.

… …

More Useful Stuff HERE:-

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.