MS Access your Data with Sub-Forms

MS Access is such a brilliant tool, so easy to use, so powerful that I sometimes think people miss its obvious simplicity, the simple ability that makes it so powerful.

I’m not talking about Relationships

Many people get bogged down in the relationship issue, working out the relationships early on and imposing them on the system.  My advice is don’t; don’t use relationships, only use then when you can see the benefit, and if you do use them use them right at the end when you know how your database fits together.  Incorrectly formed relationships cause problems that are difficult to locate and can cause you headaches in the development process. And they’re not necessary; your database will function quite happily without relationships.

I’m talking about Sub Forms

Yes that’s right; I said “sub forms” Subforms are what makes MS Access so powerful and so easy to use.  Now you might be saying “how’s that then”?  Well, in my last article “Excel in Access” I wrote about a fundamental misunderstanding of how to structure your data in MS Access, once you understand the difference between “flat file” and “relational” see note (a) you will see that sub-forms used correctly offer a simple and easy way to display information from the extra tables you have created.  If you haven’t found it necessary to use Subforms then you probably have a very simple data structure or you have a problem!

Sub-Form Windows

When I said “sub forms are what makes MS Access so powerful” that’s not strictly true, I should have said it’s “sub-form windows” are what makes MS Access so powerful”,  I know it’s a moot point because they are one in the same, you can’t have one without the other.  When you drag a sub-form on to a main form a “sub-form window” is automatically created, what you actually see is a sub-form position on the main form, what you don’t see is the sub-form window surrounding the form; the subform window is the container containing the sub-form within the main form.  It may seem like I’m being pedantic about this minor difference, however it’s this intermediary control, this sub-form window that has all of the power.

Link Sub & Main

The sub-form window has some interesting properties that you can set, one of the most interesting properties is the ability to match the field on the sub-form within it to the matching field on the main form, by identifying these two fields, you in effect link them, causing the field on the contained sub-form to update automatically when a new record is added to the main form.  (This alludes to another frequent problem would be programmers have with MS Access, see note (b))

Fragment your Table

To use MS Access effectively (as covered in my article “Excel in Access”) particularly if you start off with data in a flat file format, then you need to modify this data structure by counter intuitively dividing it up into several smaller tables.  The problem becomes “how do you display this fractured structure? Before, you had a very nice flat file in which it was easy to see how the data related together.  Now you have all these extra tables which have to be combined to re-form the original data.  That’s where sub-forms come in, they very neatly and easily offer you a way to display the recombined data.

Why MS Access is so Powerful

Most people start off with data in a “flat file format” they then find themselves having to fragment this nice easy to understand flat file structure into several apparently disjointed tables.  This fragmentation creates the next problem, how to assemble this data into a presentable user interface?  The answer “sub-forms” it is this simple ability offered by sub-forms (sub-form windows) that makes MS Access so powerful.

Note (a)

I had, and still have the misconception that “Relational” refers to relating data from different Tables via a key value. The following paragraph exposes this misconception of mine… (It’s a useful misconception so I will keep it!)


The relational model is thus named, not because you can relate tables to one another (a popular misconception), but as a reference to the relationships between the columns in a table. These are the relationships that give the model its name; in other words, relational means that if several values belong to the same row in a table, they are related. The way columns are related to each other defines a relation, and a relation is a table (more exactly, a table represents one relation).


The Art of SQL — Authors: Stephane Faroult & Peter Robson

1.1.  The Relational View of Data

Note (b)

I note from the forum where I answered questions(UKAWF) that a common problem people have is wanting to enter data into a sub-form before data is entered on the master form.  This won’t work because the subform window uses the unique reference number of the main form to give the subform record the linking information.

I’m not sure but when I get the time I’m going to explore this issue and see if there is a way round it.  My suspicion is it will be possible but difficult to implement in a “safe” way.  I’m pretty sure there’s something to be learnt about the fundamental operation of this subform master form relationship from attempting this “backward” operation and that’s a good enough incentive for me to undertake what will probably be a fruitless task.