Create a Data Grid Form Based on a Query

See Also

You can use the same drag-and-drop technique you used to create a simple data-bound form to create a data grid form based on a query. Simply create a Command object based on an SQL query, then drag the Command object onto a blank form. You can use the Query designer to create the SQL query.

In this topic, you'll create a form that displays orders for French customers in the Northwind Traders sample database in a data grid.

To create a data-bound form based on a query

1. Create a data environment Command object based on a query.
   
   
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 Data Environment Command Object Based on a Query

You can easily create a data environment Command object based on a query by using the Query designer. First, follow the instructions in the previous steps in this scenario to open an existing Data Environment designer or create a new one.

Create the Command object by clicking the Add Command button on the Data Environment toolbar, or by right-clicking the connection in the Data Environment designer and selecting Add Command from the menu. You can then specify the Command object's name, the connection it uses, and the source of its data in the Command Properties dialog box. To display this dialog box, right-click the Command object in your data environment and then choose Properties from the shortcut menu.

For example, to create a Command objects based on a query of orders by French customers in the Northwind Traders sample database, set the following properties:

Rather than select a specific database object as the basis for the command, as you did in the "Create a Data Environment Command Object" step, select SQL Statement as the source of data for the command. Then click the SQL Builder button to open the Query designer, where you specify the tables, fields, and criteria for the query.

For example, create a query that returns information about orders by French customers by dragging the Customers and Orders tables from the Data View window (available from the View menu) to the upper pane of the Query designer. (The designer automatically displays a line between the tables showing their related field, CustomerID.) Then click the check box next to the fields that you want to include in the query:

Finally, specify the criteria for the query by entering "France" in the Country field's Criteria box in the Query designer grid. As you specify the fields and criteria, the Query designer automatically builds the query's underlying SQL statement:

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShippedDate, Customers.Country
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID AND (Customers.Country = 'France')

To view the results of the query, right-click the Query designer and select Run. The Query designer displays the resulting recordset.

When you save the query, the Data Environment designer updates the Command object to use the SQL statement as its data source. To verify the statement, you can open the Command properties dialog box and see the statement is in the SQL Statement box.

For More Information   See Designing Queries.

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

In the "Create a Simple Data-Bound Form" topic, you created a data-bound form that displayed data in text boxes by dragging a Command object from the data environment designer to a blank form. You can also display the data in a data grid by dragging a data environment Command object.

For example, to create a data grid that displays the orders of French customers, select the FrenchCustomersOrders 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 or bound control. When you select Data Grid, Visual Basic automatically creates a data grid that displays records from the command's recordset.

When you run the form, Visual Basic displays the records returned by the FrenchCustomersOrders Command object in a data grid.

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.