Create a hierarchical datasheet

In this tutorial, you create a table of orders and order lines, place a WklTreeList on a form, define columns, a band, a format rule, and a footer total, and then edit a cell in Form view. You set up the definition once in Design view, and it's saved with the form. It takes about fifteen minutes.

• Microsoft 365 Access or the Access Runtime, 32-bit or 64-bit, with the WKL Forms Object Library 1.23 or later installed and registered for the current Windows user (How to: Install the released package).
• A new database in a trusted location.
• Every WKL control opens its designer the same way: in Design view, open the property sheet (F4), go to the Other tab, and click [...] in the Custom row (Opening the designer).

Run these lines in the Immediate window (Ctrl+G), one by one:

CurrentDb.Execute "CREATE TABLE tblItems (ItemID COUNTER PRIMARY KEY, ParentID LONG, ItemName TEXT(50), Quantity LONG, Price CURRENCY)"
CurrentDb.Execute "INSERT INTO tblItems (ParentID, ItemName, Quantity, Price) VALUES (0, 'Order 1001', 0, 0)"
CurrentDb.Execute "INSERT INTO tblItems (ParentID, ItemName, Quantity, Price) VALUES (1, 'Line 1', 1, 99)"
CurrentDb.Execute "INSERT INTO tblItems (ParentID, ItemName, Quantity, Price) VALUES (1, 'Line 2', 2, 15.25)"
CurrentDb.Execute "INSERT INTO tblItems (ParentID, ItemName, Quantity, Price) VALUES (0, 'Order 1002', 0, 0)"
CurrentDb.Execute "INSERT INTO tblItems (ParentID, ItemName, Quantity, Price) VALUES (4, 'Line 1', 1, 12)"

Create a blank form, save it as frmItems, and stay in Design view. Choose Form Design › Controls › ActiveX Controls, select WKL TreeList, click OK, draw the control wide enough for three columns, and name it List1. Save the form.

With the form still open in Design view, insert a standard module with this procedure, place the cursor inside it, and press F5:

Public Sub DefineList()
With Forms!frmItems!List1.Object
    .RecordSource = "tblItems"
    .KeyField = "ItemID": .ParentField = "ParentID": .TextField = "ItemName"
    .ExpandLevel = 1
    .Bands.Add "Values", "Values"
    .Columns.Add("ItemName", "Item").Width = 2600
    .Columns.Add("Quantity", "Quantity", "Values").Summary = wsuSum
    With .Columns.Add("Price", "Price", "Values")
        .Format = "Currency"
        .Summary = wsuSum
    End With
    .FormatRules.Add("Price", wrkValue, wroGreater, 30).Bold = True
    .ShowFooter = True
End With
DoCmd.Close acForm, "frmItems", acSaveYes
End Sub

The procedure closes and saves the form; the definition is stored with the form in ColumnLayout. Open the form in Form view for the next step.

You can also make the same settings without code: property sheet, Custom row, [...], and then the designer's Record source, Columns, Bands, and Format rules pages.

Select the Quantity cell of Line 2, type 3, and press Enter.

• Two orders with their lines, with Order 1001 expanded. The header shows the Values band above Quantity and Price.
• Prices appear in the Windows currency format; $99.00 (Line 1 of Order 1001) is bold because it's greater than 30.
• The footer shows the totals of Quantity and Price across all records.
• After step 4, the Quantity of Line 2 in tblItems is 3, and the footer total has increased by 1.

• How to: Use lookup columns and How to: Format cells conditionally.
• How to: Build a tree from flat records.
• How to: Export the list to Excel.
• Overview of the concepts.