HCS Consulting Group.
We make Complex Systems simple

Sub forms in Ms-Access

By Albert D. Kallal
Thursday, January 03, 2002

A hot knife through butter

Over the years I have been exposed to a good many development systems. These systems have always been database products in one way or another. Data systems are the life blood of any company, and thus tend to have the most value to a company, and also usually the largest investment in money and time.

Along the way, each system has tended to have certain features or benefits that stand out. Time changes, and many of the products I have used either change, or go by the way side.

However, those really great features from each product stick in my mind.

Just what do those great features in those products have in common? Just what makes a great software tool?

Great features in products are those that enable a developer to *model* real world solutions with ease.

 We can start taking about normalizing data,. We can start talking about creating class objects to model real world situations. It is very important to understand how a normalized database enables one to model the real world. It is also very important to understand how to create a class object that allows one to model the real world. However, great products will allow you model things with little, or no knowledge on your part. In other words, you are either creating some type of object, or setting up some type of data relation...with little or no effort on your part.

You can use Quicken, and not know a thing about double entry bookkeeping. Taking a task and making it simple can make you a millionaire.

Take the Pick operating system. This system has been around for 30 years. It used to run as it's own native OS, but now runs on either NT, or on Linux. The main reason that this database system still survives today is that it enables people of limited skill to model real world situations. The key to this system is the way it models data. It does not use a flat, or SQL model to store data, but uses what is called a post-relational, or so called multi-valued database. It also becomes an amazing tool in the hands of a knowledgeable developer.

Lets save the mV stuff for another day (my comments on why mV systems are great can be read here)

So, just what is great about a sub form in Access? Well, first let me explain what a sub form is. In ms-Access there is a concept of a sub form. All this means is that Access provides a mechanism to place more than ONE form into a existing form. In Other words I can create 3 forms. I then can create a 4th form, and drop those 3 forms into the 4th form. I can also now take these 4 form, and drop it into a 5 form. By cobbling forms together in this fashion, one can create a some very complex objects with little skill. VB forms are missing what is perhaps the most useful feature ever created in Access.

Sub forms accomplish some major, and common tasks for a developer.

  1. Allows one to build lookups into other tables..with no code, or strange syntax. Thus several tables of data can be inserted into a form *with* a corresponding set of text boxes (fields). 
  2. Allows you to build re-useable components for a application. These re-usable components are simply screens that are dropped into existing screens as a sub form. This is a far superior model to cut and paste of simple controls due to the fact that one change in the sub form will be reflected throughout the application. When users do this, they are creating re-useable objects...yet no programming is required to accomplish this high level of re-usability. You can accomplish the same thing in VB, but you have to create a ActiveX component. This is not *too* hard in vb...but it is extra work, and requires much more careful design and planning.
  3. Manages the difficult task of a parent to child relationship. (it inserts the correct key value into the child records with NO need for code on your part). Thus, one can build any classic type of parent to child relation. Order forms, Invoices...etc can built with NO code to maintain the relation. In addition, thus the correct retrieval and display of these child records is also done for you. This whole process can be done via drag and drop.

Lets look at how we use a sub form to lookup data from another file. Lets assume that we have a invoice system. We will also assume that the invoice has a field called customer id, and that Id is the customer id.

When adding a new invoice, we want the customer information to show up in our invoice. A good design means that the customer name, address phone etc. is contained in the customer table. We need to show this information in our invoice. How? Many people will suggest using a dlookups to do this. Absolutely horrible those dlookups are. The syntax, and amount of typing for each dlookup is really very messy. It also requires that the user know how to build a where clause of SQL. Not only that, but each dlookup is actually a separate request to the file. Hence, to display a few fields of information using dlookup is a horrible hit on performance.

The next option usually put forward is to write some code that loads up some controls on the screen. Actually that is not too bad, and what is done in vb. I really do think that if you have to write code to show an address in the invoice...you are already using the wrong tool.

I don't have the space here, but I would love to spend some time explaining how other tools solve this problem.

Not only does a sub form solve this problem nicely, the whole process is done via drag and drop. First we create a small tiny customer form.

We now drop the above  little customer form into our invoice form. We get the following:

We are done!. If you use a wizard to insert the form, then the lookup via the customer id is also done for you. In other words we just placed 5 fields from the customer file into our invoice form. This was easy, and the whole process was done with the mouse. Anyone can do this. Just how do you setup a lookup to another database to display a few fields?. If you are using a product that is as the drag and drop process as above....please e-mail me. It is amazing, but in general the average Access developer does not even use the above approach.

Hum......lets also assume that we have a purchase order. A purchase order is really just like a invoice. Again, our tiny customer form can be dropped into the PO. My point here is that we have a very simple form and it can be re-used throughout the application when ever we need to display the customer info. Nice eh? Just how do you develop and get that kind of re-usability?

The next feature and most common use of a sub form is to manage the classic parent to child relationship. In our invoice example we need some lines of detail. This of course would involve another data table for the invoice detail. We simply create another form based on the invoice detail. Once we create this form, then we insert it into our invoice form. Unlike the simple lookup to the customer name, this form must correctly pull in the correct lines of detail. In addition, if we *add* a new line of detail the correct invoice id must be inserted into each line of detail to maintain the relation. This process is automated in Access. Again, this whole process is done via drag and drop, and NO CODE is required. Can your development tools build parent to child screens and maintain the relation with no code? This task is so common in database development that any tools that don't allow for this setup is like leaving out the option to center text in a word processor.

The other use of sub-forms is to create re-useable components. For example, lets assume we need to repeatedly ask for a start and end date. In most cases we would simply create a form, drop in two ActiveX calendars. We would then simply wrap the form in a nice function that we call when we need a start and end date. However, for a good many forms (especially for report prompts) we need a start and end date and DO NOT what to launch a separate dialog box to ask for the date. Ok, here is what we do: First create our Start/End date form. It looks like the following:



Now, every time we need a start end date for a form, we drop the above form into a *existing* form. Hence, for a daily invoice prompt we get:

Notice how we have a pre-build start end date assembly that we can drop into any form. Thus, the above is actually two forms. Here is another one where I used the date start end (once again, a simple drag and drop gets me two calendars on a form pre-built for me!):

Again, the goal here is to re-use what we develop. Re-using is NOT THE same as copying...not even close!. Down the road, if I decide to make a change to how the calendar is to display, or replace it...*all* my forms in all areas that uses this sub from will be updated...since it *is* the same sub form.

So, the lesson here is not to overlook the power of sub forms. They model real world requirements, and in general don't require any code to implement. They are ONE OF SEVERAL reasons why a project in Access will cost only $10,000 to develop, and in VB it will cost $30,000 to develop.



* Sub forms are not the only reason why Access enables one to develop applcitions faster than most tools. However, it is one of the main reasons why.