Drilling Down from a List Box Selection

The most common use of a drill-down form that displays a list of orders is to present the line items of a particular order. It's relatively easy to add and program a line items list box, based on the Order Details table, to the form you created in the preceding sections. An additional use of a line items list box is to verify that the cboProduct combo box correctly performs its assigned role.

Creating the Drill-Down Query and Adding the List Box

Create the query and add the list box with the following steps:

1. Open frmCombo1 or select it in the Database window, and save it as frmDrillDown.

2. Create a new query and add the Order Details and Products tables.

3. Drag the Product Name field of the Products table and the OrderID, UnitPrice, Quantity, and Discount fields of the Order Details table to the Query Design grid. Move the OrderID field to the first column of the query. OrderID doesn't appear in the line items list box; it's used to link to the OrderID column of lstOrders.

4. Add a calculated field defined by typing the following expression in the sixth column of the Field row:

    Extended: CCur(Format([Order Details].[UnitPrice]* [Quantity]*(1-[Discount]),"$#,###.00")) 

   Figure 29.9 illustrates the design of the query.

   Figure 29.9. The query of this design populates a list box of order line items.

   

5. Run the query to verify your design, and then close it, saving it as qryDrillDown.

6. Open frmDrillDown in Design view, increase the height of frmDrillDown to about 3.75 inches. and add a list box with the same width as lstOrders and a height of about 1 inch at the bottom of the form.

7. Select the Table or Query option in the first Wizard dialog, accept the default, and click Next.

8. Select Queries and qryDrillDown, and then click Next.

9. Click the >> button to add all query columns to the Selected Fields list and then select the OrderID field and click < to remove it. Click Next twice.

10. Adjust the widths of the columns to fit the size of the data in the fields. Click Next.

11. Accept the default ProductName column for the default value of the list box. Click Next.

12. Replace the default caption of the label for the combo box with Line Items. Click Finish.

13. Move the label to a spot above the new list box, and click the Bold button.

14. Select the new list box, click the Format tab of the Properties window, and change the value of the Column Heads property to Yes.

15. Change the Caption property value of the form to Orders and Line Items by Country and Product.

16. Change to Form view to check your design (see Figure 29.10) and press Ctrl+S to save your changes.

    Figure 29.10. The list box for the drill-down query in Form view displays the first few rows of the entire Order Details table.

    

Programming the Drill-Down List Box

The List Box Wizard supplies the following SQL statement as the Row Source property of the new list box:

 SELECT qryDrillDown.ProductName,       qryDrillDown.UnitPrice, qryDrillDown.Quantity,       qryDrillDown.Discount, qryDrillDown.Extended    FROM qryDrillDown; 

The simplified Jet SQL statement used to populate the Line Items list box from an order selected in the lstOrders list box is as follows:

 SELECT ProductName, UnitPrice, Quantity, Discount, Extended    FROM qryDrillDown    WHERE OrderID = lstOrders.Value; 

The following steps complete the modification of the list box and add VBA code to execute the preceding query:

1. Return to Form Design view, select the drill-down list box, click the Other tab of the Properties window, and change the value of the Name property to lstLineItems.

2. Select the label for lstLineItems and change the value of its Name property to lblLineItems.

3. Click the Code button and add the following string constants to the Declarations section of the frmDrillDown Class Module:

    Private Const strSQL4 = "SELECT ProductName, UnitPrice, Quantity, " &    _ "Discount, Extended FROM qryDrillDown WHERE OrderID = " Private Const strMsg3 = "Double-click an order to display line items" Private Const strMsg4 = "Line items for order " Private Const strMsg5 = "Line items" 

4. Select lstOrders from the Object list and DblClick from the Procedures list to add a lstOrders_DblClick subprocedure stub.

5. Add the following code to the lstOrders_DblClick stub to set the value of the RowSource property of the list box and requery the control:

    If Me!lstOrders.Value <>"" Then      With Me!lstLineItems          strSQL =strSQL4 & Me!lstOrders.Value &";"         .RowSource =strSQL         .Requery     End With     Me!lblLineItems.Caption = strMsg4 & Me!lstOrders.Value End If 

6. Add the following lines to the end of the Form_Activate, cboCountry_AfterUpdate, and cboProduct_AfterUpdate event handlers to clear the list box and change the label caption when opening the form or setting new query criteria:

    With Me!lstLineItems      .RowSource =""      .Requery End With Me!lblLineItems.Caption =strMsg5 

7. Add the following lines to the end of the Form_Activate event handler to clear the persisted values in the lstLineItem list:

    With Me!lstLineItems       .RowSource =""       .Requery End With 

8. Add the following line above the End Ifline of the FillList subprocedure to change the Line Item list box label's caption:

    Me!lblLineItems.Caption =strMsg3 

9. Return to Access and change to Form view. Double-click one of the order items to populate lstLineItems (see Figure 29.11).

Figure 29.11. The Line Items list box displays Order Details records for the order you double-clicked in the Orders list box.