Understanding the subform control

Subform controls allow you to create linked table like displays of information that are related to a main topic. We saw an example of a subform briefly at the beginning of this course when we used the form wizard to display a list of all of the orders that included a specific product. Let's now duplicate that form manually using the subform control. I'm going to go to the Create tab or the ribbon, and we will create a New Form in Design View here. The first thing that we want to do is link this to the table that we want to be the main topic. In this case, we want to have a form that displays products at the top and then down below, will list a table with all of the orders that included that product.

I will go to my Add Existing Fields here, I will Show All Tables and for my Products table I will expand it here, I will double-click on ProductID and double-click on ProductName. That'll add both of those fields to my form. I will go ahead and make this wider, because I know that my ProductNames are fairly long, and I am going to highlight both of these and I am going to push them up to the top with the arrow keys, okay. Let's go ahead and close the Field List here and I'm going to scroll down to the bottom of my form and make it a little shorter, so that it all fits on the screen. So Subform tool takes up a lot of real estate, which makes it a great and ideal opportunity to make use of a Tab Control.

The Tab Control if you remember is right here, I will click on it in the Control section of the Design tab and I will drag out a Tab Control, like that. Access places two tabs here. In the first tab I'm going to place my Subform, so I will click here to select the Tab and then I will find my Subform, I will use the down arrow or the more button, and the Subform button is this one right there. I will click on Subform and come down into my tab, well, it turns black. The next thing I am going to do is go inside of this tab, so I will drag out the area that I want my Subform. There, and the wizard starts. And the first page of the wizard is going to ask us where is our data coming from? Do you want to use an existing Table and Query or Do you want to use an existing form? Let's go ahead and use an existing Table and Query, we will go ahead and say Next.

We are going to build a table that displays order information for the product that we selected on the main part of the form. I will switch my table here, to the orders table and from there I will choose the OrderID and the ProductID. We also want to find out who ordered this product? So I will go back up here and I will choose my Direct Customers table. And I will choose First Name and Last Name. Let's go ahead and say Next. Access then asks how does that data in the subform relate to the data in the main part of the form up here? We can either Choose from the list or Define my own relationship. Now because we've already have a relationship that's been established in this database, Access has already correctly identified how they relate.

So I will just take that and go ahead and say Next. We can go ahead and name our Subform, it's going to appear down here at the Navigation Pane, or we can just accept the default. I will just leave as it is and say Finish. Access adds in the subform into our tab, now we can add another form into the second tab here, for instance, I will switch to this one that says Page4 and we will go up and add another subform down here. I will click the down arrow, the Subform tool, and I will just click, to put in there. This time instead of building a new table, we will use an existing form. I am going to choose the Reviews form. This is the one that has product reviews. I will go ahead and say Next, Access again asks me, well how does this relate to what I have selected up in the main section, and it's correctly identified that relationship.

So I will go ahead and say Next again, and we will just accept the default and say Finish. Access places that in my tab as well. Now let's go ahead and rearrange these a little bit. First of all I'm going to rename the tabs up here, so I don't need labels inside here. So I am going to go ahead and delete the label and then I will select the form, and I will resize it, so it fits inside the tab better. Next I will go to the other tab; I will do the same thing. I will delete the label and I will move this into position. Finally, I want to rename these tabs. And I can do that in the Property Sheet. I will select the tab, and the Caption property here, on this first one I'm going to put Orders.

I will press Enter and I will go to the next tab. And here the Caption will be Reviews and I will press Enter again. Okay, let's go back into our form and see how those all are working. We will switch it to Form View. Right now I am on my first product of 90. I can scroll to the different products that we have, as I do so I get an updated table here that shows me all of the Orders that has been placed for that specific product, and if I go to the Reviews tab, I can see reviews that might have been made on this product. So now I have an easy way to review lots of information about a specific product, all in one simple form.

The subform object brings in yet another way to view your content by displaying information that is related to a single record. Pairing the subform with the tab control also allowed us to keep related information together in one space efficient location, so that it is easy to get to.