Microsoft Access is one of the best database management systems offered by Microsoft. Tables, Queries, Forms, Reports, and many such specific commands allow users to add, edit, display, or delete items within the database. Forms give us accessibility to the data stored in the database. SubForm is a Form within a Form. It is created to provide more insight into the data saved in the Form.
To understand the relationship between a subform and a form, let's look at an example. Suppose we have a form that displays a company and a subform that shows every employee within the company.
Another example would be an order in a form, a subform would have all the order items. The need to create a subform is when we want to display data from multiple tables in a one-to-many relationship. Here, to show order with the order details, the creation of a subform is necessary.
Step by Step Approach to create a subform
Step 1 : Go to the Form
Here, on the left navigation pane, right-click and select the option "Design View".
Step 2 : Go to the Controls Toolbar
You can find the Controls Toolbar in the Design tab. Click on the down arrow to expand the Controls toolbar.
Step 3 : Click on the Subform/Subreport option.
Now you can drag the Subform and place it in the main Form.
Step 4 : Verify
Now click on the exact location of the main Form where you want the Subform to be set up. The Subform is launched, and you get a pop-up if you wish to use the same Form or create a new form using tables or queries.
Step 5 : Navigate
Once you make the selection, click on the Next option. Select the table and query from the drop-down menu. Now select each field you want to display in your Subform and click on the > button to move the selected fields to the right pane. Move on to the next table or query and select fields if you want to display them.
Step 6 : Link
In this step, Access asks you to link the main Form with the Subform. Based on the primary key and foreign key relationship set up by you, there will be a field recognized by Access. You can also choose the option "Define my own" and choose a field. Click the Next button.
Step 7: Create
Give a name to your Subform and click on the Finish button. It creates a subform in the location set up at Step 4 in Access and adds it to the main Form. In the Design view, it appears like a big encapsulated form.
Step 8 : Make Edits
If you need to edit specific sections like font, colours, label, you can switch to Layout view, edit, and go back to the Design view and get the minor modification done. You can switch to the Form View to see how the forms are displayed to the user. The main Form is seen and embedded within is the Subform with the table and query results. The data in the Subform updates itself as you move through the main Form.
Step 9 : Save the Subform
The Subform is saved as a separate form. You can view the saved Subform in the Navigation pane. It appears as an independent form and opens in the Datasheet view. As you open it, it displays all data. When you open it after opening the main Form to which it is linked, it only shows the main Form's data.
A Simple Illustration
Let us assume that the Order table is created with the following fields
And Customer table is created with the following fields
- Now, it must establish a relationship between these two tables. It is one-to-many as one customer can place many orders.
- Select the Customer table, click on Design---Subform in the controls group of the tab. Extend the Form for the order list.
- Select the Orders table and click Next.
- The Subform Wiz will show the two tables as related via one field, i.e. Customer Id.
- Click Next. Give a name to the Subform, say XYZ, and click Finish.
- Change the view to Layout View. The result shows all the orders for each customer.
As we see, it is effortless to create subforms in MS Access. From Customizing Your Start Menu to providing records of data stored, Microsoft has a lot to offer. MS Access helps in displaying large amounts of data and also avoids confusion for the user. You must use this feature and test with varying data fields. Isn't the impact of technology over human lives commendable?
We at, OpenGrowth, are committed to keeping you updated with the best content on the latest trendy topics from any major field. Also, both your feedback and suggestions are valuable to us. So, do share them in the comment section below.