Create a Master/Detail Form

See Also

In database applications, it's often useful to simultaneously view a record with a group of related records. For example, you may want to view a customer record and the current orders for the customer. A common way to accomplish this is to create a master/detail form.

To create a Master/Detail form

1. Create a parent/child data environment Command object.
   
   
2. Drag the Command object from the Data Environment designer to a blank form.

Note   This topic is part of a series that walks you through creating a simple database application that interacts with data in Nwind.mdb. It begins with the topic, Interacting with Data in a Microsoft Jet/Microsoft Access Database.

Create a Parent/Child Data Environment Command Object

You've seen in the "Create a Data Environment Command Object" and "Create a Data Grid Form Based on a Query" topics how to create a data environment Command object based on a single table or query. You can also use a hierarchical parent/child Command object that lets you simultaneously view a record from one table or query with a group of related records in a second table or query.

For example, you can create a parent Command object that returns address information for all French customers in the Northwind Traders sample database. You can then add a related child Command object to the parent Command object that displays order information for each French customer.

Create the parent Command object by following the instructions in the previous steps in this scenario to open an existing Data Environment designer or create a new one. Then click the Add Command button on the Data Environment toolbar and set the following properties in the Command Properties dialog box:

Select SQL Statement as the source of data for the Command object. Click the SQL Builder button to open the Query designer, drag the Customers table from the Data View window to the upper pane of the Query designer, and then click the check box next to the CustomerID, CompanyName, Address, City, PostalCode, and Country fields. Specify the criteria for the query by entering "France" in the Country field's Criteria box. The Query designer builds the following SQL statement:

SELECT CustomerID, CompanyName, Address, City, PostalCode, Country
FROM Customers
WHERE (Country = 'France')

Add the child command to the parent command by right-clicking the FrenchCustomers command in the Data Environment designer, and then select Add Child Command from the popup menu. Set the following properties in the Command Properties dialog box:

Select SQL Statement as the source of data for the Command object. Click the SQL Builder button to open the Query designer, drag the Orders table from the Data View window to the upper pane of the Query designer, and then click the check box next to the OrderID, CustomerID, OrderDate, and ShippedDate fields. The Query designer  builds the following SQL statement:

SELECT OrderID, CustomerID, OrderDate, ShippedDate
FROM Orders 

Finally, define the relationship between the parent and child Command objects. In the Data Environment designer, select the OrderDates Command object and click the Properties button on the toolbar. Click the Relation tab. Define a relationship between the CustomerID fields in the parent and child Command objects by selecting CustomerID in the Parent Fields and Child Fields/Parameters lists. Then click the Add button. The Data Environment designer adds the relation to the Relation Definition box.

When you've finished, the data environment displays a hierarchical view of the parent Command object and its fields along with the child Command object and its fields.

For More Information   See How Commands are Exposed for Programmatic Access.

Drag the Command Object from the Data Environment to a Blank Form

As with the data-bound forms based on a single table or query, you can create a master/detail form based on a hierarchical parent/child Command object by simply dragging the Command object from the Data Environment designer to a blank form.

For example, create a master/detail form that displays address information and related order information for French customers by selecting the FrenchCustomers Command object you created in the previous step. Then while pressing the right mouse button, drag the Command object onto a blank form. Visual Basic displays a popup menu that lets you select whether to create a data grid, hierarchical flexgrid, or bound controls. When you select hierarchical flexgrid, Visual Basic automatically creates a master/detail form based on the parent/child Command object.

When you run the form, Visual Basic displays the address records returned by the FrenchCustomers Command object and related order records returned by the OrderDates Command object.

Step by Step

This topic is part of a series that walks you through creating a simple database application that interacts with data in Nwind.mdb.