Create a customer grid with orders

In this tutorial, you create a customer table and an order table, place two WklGrid controls on a form, bind the first one with the designer, and link the second one to it without code. Then you add a detail view and work with groups and filters in Form view. 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 tblCustomers (CustomerID COUNTER PRIMARY KEY, Company TEXT(50), City TEXT(50), Turnover CURRENCY)"
CurrentDb.Execute "CREATE TABLE tblOrders (OrderID COUNTER PRIMARY KEY, CustomerID LONG, Item TEXT(50), Quantity LONG)"
CurrentDb.Execute "INSERT INTO tblCustomers (Company, City, Turnover) VALUES ('Adams', 'Boston', 120)"
CurrentDb.Execute "INSERT INTO tblCustomers (Company, City, Turnover) VALUES ('Baker', 'Chicago', 80)"
CurrentDb.Execute "INSERT INTO tblCustomers (Company, City, Turnover) VALUES ('Carter', 'Boston', 300)"
CurrentDb.Execute "INSERT INTO tblOrders (CustomerID, Item, Quantity) VALUES (1, 'Screws', 100)"
CurrentDb.Execute "INSERT INTO tblOrders (CustomerID, Item, Quantity) VALUES (3, 'Nuts', 50)"
CurrentDb.Execute "INSERT INTO tblOrders (CustomerID, Item, Quantity) VALUES (3, 'Washers', 20)"

Create a blank form, save it as frmCustomers, and stay in Design view. Choose Form Design › Controls › ActiveX Controls, select WKL Grid, click OK, draw the control across the upper half of the form, and name it Grid1. Insert a second WKL Grid below it and name it Grid2. Save the form.

Select Grid1, open the property sheet, go to the Other tab, and click [...] in the Custom row. In the designer:

1. Record source page: choose tblCustomers as the table and CustomerID as the key field, and then click Create columns from fields.
2. Columns page: select Turnover, and set Format to Currency and Total in the footer to Sum.
3. Grouping and totals page: turn on Footer with totals.
4. Appearance page: turn on Live preview with real data.

The preview on the right shows the three customers with the total. Click OK and save the form.

Select Grid2 and open its designer the same way:

1. Record source page: tblOrders, key field OrderID, Create columns from fields.
2. Link page: for Master field, choose Grid1 (the list offers the form's other grids and their fields), and for Detail field, choose CustomerID.

Click OK and save the form. No code is needed: Grid2 shows the orders of the customer selected in Grid1.

Open the designer of Grid1 again, go to the Detail views page, click Add, and fill in table tblOrders, key field OrderID, detail field CustomerID, master field CustomerID, and caption Orders. Click OK and save the form.

Or do the same in code, for example in Form_Load:

Me.Grid1.Object.AddDetailView "Orders", "tblOrders", "OrderID", "CustomerID", "CustomerID", "Orders"

Open frmCustomers in Form view:

1. Click Carter in Grid1: Grid2 shows two orders.
2. Click the arrow to the left of Carter (or press Ctrl and +): the orders open below the record on the Orders tab.
3. Drag the City header onto the group panel above the headers: the customers are grouped by city, and each group row shows its record count and its total.
4. Right-click a column header and choose Show filter row, and then type >= 100 below Turnover: only Adams and Carter remain, and the filter bar below the grid shows the condition.
5. Right-click the grid and choose Export … to save the grid as an Excel workbook with the groups as an outline.

A form with two grids that work together without a single line of code: the orders follow the selected customer, every customer expands to show its orders, and groups, filters, and the total respond to the user right away. The designer settings are saved with the form.

• How to: Link two grids without code, also with an Access subform
• How to: Show large tables in server mode
• How to: Group records and show totals
• WklGrid overview