Like many desktop database application development tools, Microsoft Access has very robust facilities for creating forms. For reporting applications you will use yourself you may not need many forms. But if you are setting up a database for someone else to use you will want to create a set of forms for data entry, report selection, etc.
The purpose of this article is not to present a tutorial on how to design a form. There are many good sources of information on that. Rather, I want to focus on the effect that table relationships and queries have on the basic structure of the forms. Along the way we'll see just how much of the legwork can be done for you by the Access form wizard, especially if you have properly designed your tables, queries and relationships. So let's cover a few basics first.
Background
Bound vs Unbound Forms
Access can create two types of forms: bound and unbound. A bound form is connected to a table or query via a record source property. An unbound form has no record source. It can be used to collect criteria, such as a date range, for reports, searches, etc. This article focuses on bound forms.
Data Sources
Each form has one (and only one) record source. This is an important point since it would seemingly imply that a form can be used to update only one table. But this is not so. A query which draws from more than one table can also be used. If it is updatable then you can update multiple tables via one form (more about this later). In the example below we are using a work_orders table. It could just as easily have been a query.
Another way to update multiple tables is through subforms.
Subforms
Access has the ability to create subforms. These are similar to regular forms and have their own record source. Once created they can be embedded in or linked to a main form and Access will automatically maintain the relationship between the data in the two forms.
The remainder of this article will discuss form creation in light of existing tables, relationships and queries. We'll start with tables and then delve into using queries.
Simple Data Entry Forms
You did do a thorough job of setting your field properties, didn't you? If not go back and finish this job now. The Form Wizard will use the caption property to generate the field labels. If you've set these already then you will automatically get meaningful labels on your forms with no further work.
A simple data entry form is all that's needed to update many of the tables in your database. For example customers, suppliers, vendors, parts, locations, etc. You should have a data entry form for each of these. It won't handle your transactions or fancier displays but we'll get to that later. Here's a real simple form for updating employee information for a sample database.
Notice the record source. This form was created simply by running the Form Wizard, selecting the employees table as the record source and using the columnar format. Of course I added a few things and moved a field or two, but basically the wizard did all the work. Do note that you should also set the caption property for the form.
Name your form with a prefix of frm. For example: frmEmployees.One-to-many Forms
These are sometimes called master-detail forms. The basic idea is that one table is the parent or master table. The other is the child or detail. The parent is the record source for a main form in which a subform is embedded with the child table as the record source. There are several ways to create one-to-many forms. First let's have a look at what one looks like. It's a simple example showing a work order with labor tickets applied.
The main form uses the work_orders table as the record source. The subform uses the labor table. There is a foreign key, wono, in the labor table which refers to the primary key, wono, in the work_orders table. Here is what the relationship look like.
We'll create this the manual way. Once we know what is going on we'll look at how to speed the process up with the Form Wizard.
There are three basic steps.
Create the subform
Create the main form
Add the subform to the main form
1. Create the subform
Use the From Wizard to create a form just as for a simple data entry form. But select a tabular or dataheet format. Remove extraneous controls, labels, etc. Use the child table as the record source. When done you should have something that looks like the one below (I did mine in tabular format).
Save it. The convention for subform name prefixes is sfrm. Some people use frmsub.
2. Create the Main Form
Create the main, or master, form using a columnar format and the parent table as the record source.
3. Add the Subform to the Main Form
Drag and drop the subform on the the main form. Voila. You now have a master-detail form.
One-to-Many Forms (Cont'd)
Below is the property sheet for the subform. Note the link master and link child properties. These show the foreign key, link child, referencing the master's primary key. Both are wono(work order number) in this case. Clicking on the ellipsis symbol, "...", will bring up a dialog to configure the linking fields if you need to change them (which you shouldn't, ordinarily).
Creating a one-to-many Form in one step
Now that we know what is going on let's look at how to speed the process up with the Form Wizard. Remember how we created a subform for the child table, a main form for the parent table, and then linked them? Well, the wizard can figure this out, too. Or, at least it can with a little coaching.
Launch the Form Wizard and supply both tables as the record sources. You will get a dialog box asking how you want to arrange the data.
By selecting how you view the data you can control the type of form that gets created. The example above will build a work_orders main form with a labor subform just as we did earlier - but in one step! Selecting by Labor would cause a single form to be created based on a query which Access would create automatically.
The Subform control
One more point - there is also a subform control you can use if it's installed. (Marked in red below). Drag and drop it on to your main form and follow the dialog.
There really isn't a many-to-many form, per se. Typically when you have a many-to-many relationship you will build a main form with a subform. Remember that we represent a many-to-many relationship with the two original tables plus a third intersection table. Build a main form based on one of the original parent tables. The subform will refer to two tables: the intersection table and the other parent. How is this done? With a query.
You can either create the query yourself or have the wizard do it for you by adding fields from all three tables.
Many-to-One Forms (Cont'd)
Here is a form, dressed up a little, based on the previous query. Empno and wono are the composite primary key of the labor ticket. They are also foreign keys to the primary keys in the employees and work_orders tables. By selecting an empno or work_order no from the drop downs, the corresponding information from the employees and work_order tables is automatically filled in on the form.
This same technique could be used to display shipping and billing addreses for a customer, etc. Equally the fields could have been displayed in tabular format like an invoice line-item. This is illustrated below.
