Main Form and Subform Concepts
Relational data refers to the one-to-many relationship between records that are stored in separate tables. A one-to-many relationship is an association between two tables in which the primary key value of each record in the primary table corresponds to a value in the matching field or fields of many records in the related table.
The main table and child table records can be viewed in the access forms separately. If we want to view the relational data in a single form, then the “main form - subform" relationship plays a role. A subform is a form that is inserted in another form. The primary form is called the main form. A form/subform combination is sometimes referred to as a hierarchical form, a master/detail form, or a parent/child form.
The form and subform concept can be illustrated with the "Company-Product" example. There will be two tables: Company - the main table - has Company ID as the primary key and Product has multiple rows for each company ID. Each company will have its own Product manufactured. If we want to list the number of products manufactured by each company, we can use the Product form (Subform) in the Company form (Main form)
The first step is to create a new access database with two new tables “Company” and “Product”. In the Company and Product tables, we can enter the sample data below.
Then we can create a new form called “frmProduct” with the fields listed from the table “Product”. The records will bind and display by setting the Record source of the form and control source for each control. Set the additional property as continuous form for the “Default View”.
This creates the subform “frmProduct” which can be used in the main form “frmCompanyDetail”. The screenshot below shows how to add the subform. Pick the control “Subform/Subreport” from the toolbox, and click and drag to where you want to place the product listing. The wizard will prompt you to select the form. Click “Next”.
Choose the option “Define my own”, which allows you to specify any number of links between the main form and subform. In our example, we are going to give company ID as a link. Click “Next”.
Once finishing the wizard, the subform will be set in the main form. Run the form and see the product details for each company. Use navigation buttons to view the other company and its products.
By Admin at 26 Jan 2016, 17:19 PM