Main/Sub Forms

In This Site

In This Section

The number one source for making your website sell!

Duplicating the ability of Microsoft Access to have main and sub forms

While FrontPage cannot produce standard main-sub forms, you can duplicate this functionality with a little creative design

As you take your Microsoft Access databases online, you'll find yourself looking for ways to duplicate some of the basic functionality that you had when you developed Microsoft Access databases for offline use. You may also need to be able to work with the same data both online AND offline.

When you use a system of main and subforms in Access, it is critical that the main form have a unique key that will be used to connect, or link, to the subform.

In its most basic form, FrontPage is not capable of this same simple integration. Like most challenges, it can be solved through some creative planning and a little FrontPage Magic.

Here is the basic process

In this example, we'll use the Northwind database that comes free with Microsoft Access and Microsoft FrontPage.

In order for this to work, you need to begin by designing a pick list which will pass a unique ID parameter to the page on which you want to display the main and subform results. If you are not sure how to do this, then work through our lesson on Passing Parameters.

Your pick list will show an alphabetical list of customers. Once you've created your pick list, set the link to pass the CustomerID parameter to your main-subform page.

Our main-subform page will include two DRW regions. One DRW will be placed at the top of the page to display the customer details and below that, we'll create a second one that lists all the orders connected to that customer.

Create the first DRW Region

Open a blank page in FrontPage and from the menu select Insert-Database-Results. Select the Northwind database connection.

In Step 2, select the Customers table as your record source.

In Step 3, leave the list of fields as is, but click on the More Options button.

Click the Criteria button.

Click Add.

Select CustomerID as the Field Name. Your screen should look like the following. Click OK twice to return to the More Options screen.

Click the Ordering button and select CompanyName. Make it ascending order (arrow pointing up).

Click OK twice and then Next to move to Step 4 of the DRW. Select List-one field per item. Deselect the Place horizontal separator option. Click next.

In Step 5, select Display all records together and deselect the Add search form option. Click Finish.

Your DRW should look like the following:

Now, whenever you select a name from the picklist, it will display the contact information for the relevant company. You're half way there. Now we need to create the subform DRW that will list the orders placed by this particular company.

Create the Subform

In order to create the subform that will display the orders placed by the company selected in the picklist, you will place a second DRW below the first. Select Northind as the database connection and Orders Qry as the record source.

We only need to view some of the fields for this query. In Step 3 click the Edit List button and arrange your field values as shown in the following screen shot.

Click OK and in Step Four click the More Options Button and then the Criteria Button. Once again, select CutomerID as the criteria.

Click OK, and in the More Options screen change the message to one similar to the one in the following screen shot.

Click the ordering button and set it to sort by Order Date. Set the order as descending (arrow pointing down).

Click OK and then Next to move to Step 4. In the formatting options, select Table. Click next.

If you know you will only have a few orders for each customer, you can select Display all records together, otherwise place 5 in the Split records into groups option. Deselect the Add Search Form option.

Your second DRW regions will now look like the following:

If you want, you can delete the CustomerID and CustomerName fields as they are not necessary for the display. The CustomerID field is necessary for linking the data to the picklist, but it does not need to be displayed. If you want to do this, simply delete the relevent columns from the DRW.

Putting It All Together

We've now created a page that will display the contact information of a single customer as well as a list of orders placed by that customer. In the following form, select a customer and click the View Results button.

If you wanted to take this one step further, you could make the OrderID hyperlink to a page that would show the details of that order.

These tutorials are part of an upcoming training course called "FrontPage Magic - How To Create A Database Driven Website For Non-Programmers". Stay tuned for more details on this exciting new product.