Subform controls allow for the creation of linked table-like displays of information that are related to the main topic of the form. In this movie, Adam creates two subforms inside of a tab object that display records related to the products sold at H+ Sport. One tab includes related product feedback, and the other includes order details.
- [Voiceover] 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 in 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 and we'll do that by creating a new form in design view. The first thing we need to do is connect this form to a data source, so on the property sheet, I'm going to switch over to the data tab, find the record source property, and using the drop-down list, I'm going to choose the products table here.
Next, we can go ahead and turn on our Add Existing Fields window and start adding some fields from that table. I'm going to double click on ProductID, Item, Size, and Color to add all four of those fields to my form. Then, I'll draw a box around all of these to select them all and I'll press the up arrow key on my keyboard to nudge them up against the top of the screen and then I'm going to hold down the shift key and press right to make them all wider. So that's the beginning of our form. Now we can turn our attention to subforms. Subforms take up a lot of screen real estate. That makes them a perfect candidate to pair with a tab control. So let's first place a tab control on our form and then we can place the subform within the tabs.
I'll grab a new tab control from the Design tab of the ribbon, then we'll come down here and drag out a big box here for the tabs. That'll add two pages to my tab control. Now let's go ahead and change the name of these tabs. I'll switch over to the property sheet here and then I'll check on this first one, which for me says Page5, but yours probably says something different. Go ahead and switch over to the other tab on the property sheet where we'll find the name property and we can name this one here orders. Then I'll change the name of the other tab here to feedback. So essentially what we're going to do is be able to select a product from the upper portion of the form and then in these two tabs we'll display all of the related details.
In this tab, we'll display the related feedback for that product and in this tab over here, we'll display the related orders for this product. Make sure that you're on the Orders tab now and we'll turn our attention to the subform, which you can find on the second row of the controls and it's this icon here with the two boxes on the right hand side. Go ahead and click on it to activate it, then come on down here into the Orders tab and when it turns black, you know that you're gonna put something inside the tab. I'm going to click once to add in the new subform and the subform wizard starts up. First, we need to tell Access what data we want to use for our subform or subreport.
I'm going to choose the option here to use an existing table or query, but also note that we can use an existing form and pull all the forms that are in our navigation pane in, as well. Let's go ahead and leave it on that first option, though, and press the Next button. Then we get to tell it which tables are related to the Products table that we want to see the information from. In this case, I want to see the related orders so I'll pull out the Orders table and I'm going to grab the OrderID, I'll skip the CustomerID cuz we'll get that from the other table, but we will take the quantity, the SalesPersonID, and the OrderDate and Status. Then, we'll come back up here to the drop-down list and we'll choose the Customer's table so we can see the first and last name of the customer that placed the order.
I'll press next, then Access wants to know how the data is related to each other. In this case, we're going to show orders for each record in the Products using the ProductID as the related field. We'll go ahead and press the next button, and then we can name our subform, but we already have a tab here, so I actually don't need to name it. Let's go ahead and press the finish button there. That'll add the subform here into my Orders tab. Now I can get rid of this label. Because I have the tab named Orders, I don't need another label that says Orders subform. That's a little bit redundant so I'll select it and press the delete key on my keyboard. Then I'll select the subform itself and using those arrow keys, I'll just kind of nudge it into position here.
And I'll make it a little bit wider and a little bit taller. So there's our first subform. Let's switch over to the Feedback tab and do the same thing for the Feedback. I'll go up to the Design tab, we'll scroll down to the second row, we'll grab the subform control and we'll come in here and just click again. This time, we're still going to use an existing table or query so I'll press next. This time the information is going to come out of the Feedback table so I'll come up here to the top and choose the Feedback table. I want to use all of the fields here so I'll press this double arrow to move everything over to selected. Press next. We'll then confirm that the two tables are linked based off of the ProductId field here.
Go ahead and say next. And again we'll say finish to accept the default name. Once again, I'll delete this redundant label and we'll select the form itself and using those arrow keys, I will nudge it into position. And finally click here and drag to re-size. At this point, it's probably a good idea to save our form so I'll press Ctrl S on my keyboard and we'll call it Products with subforms. That'll add it in to the navigation pane so we can review it later. Now at this point, let's go ahead and see the results. I'll switch over into Form view and we can see the different products here. Now we actually have two different record selectors at this point.
We have this lower record selector that controls the selection of our different product items and then we have a separate record selector inside of each of these tabs that corresponds to the related data. So for instance, if I scroll over here to a different product, how about the small teal cross-back training tank top, which is product ID number 106, in here I can see all of the different orders that were placed for this product. And down at the bottom in the record selection, I can confirm there were 15 of those. If I switch over to the Feedback tab, we could see any feedback that may have been left for this product. Now, unfortunately, this product hasn't had any feedback left for it yet.
That's why this table is mostly empty. So the subform object brings yet another way that we can view your content by displaying information that's related to a single record. Pairing the subform with the tab control allowed us to keep related information together in one space-efficient location so it's easy to get to.
Then the course dives into reports: creating efficient and readable layouts, grouping data into categories, tying reports to queries, and using conditional formatting rules to highlight key takeaways from the data. Finally, Adam demonstrates how to link forms and reports and print your results, and introduces unique ways to save time filling out paperwork and generating form letters.
- Creating forms with the Form Wizard
- Formatting and aligning form objects
- Combining text boxes
- Adding a header and labels
- Controlling input
- Adding attachments and images
- Linking form controls
- Creating menus and data entry forms for the database
- Building reports
- Creating calculation fields
- Linking forms and reports
- Printing and exporting reports
- Creating a form letter