WklGrid
A flat, data-bound grid for Access forms. It shows the records of a table, query, or SQL statement in columns, and users or code can group, filter, and total them. Like a subform, it follows a master record, and it stays fast even with a million linked records. It's built on the same engine as `WklTreeList` and shares its columns, cell editors, formatting, and export.
Get started
Common tasks
Visual aid
+- Grid1 -------------------------------------------------------------------+ | [City ▲] [Active ▲] [1] | +-------------+--------------+-------------------+--------------------------+ | Customer ▼ | City ⏷ | Sales ⏷ | [2] | | Ad* | | >= 100 | [3] | +-------------+--------------+-------------------+--------------------------+ | v City: Boston (3) | $430.00 | [4] | | > Adams | Boston | $120.00 | [5] | | +- Orders | Notes -----------------------------------------------+ [6] | | | Product | Quantity | | | | Product 1 | 10 | | | +----------------------------------------------------------------+ | | > Carter | Boston | $300.00 | | | > City: Chicago (2) | $80.00 | | +-------------+--------------+-------------------+--------------------------+ | | | $560.00 | [7] | +---------------------------------------------------------------------------+ | [x] [Customer] begins with "Ad" and [Sales] >= 100 Clear filter [8] | +---------------------------------------------------------------------------+
| Region | Meaning |
|---|---|
| 1 | Group panel: drag a column header here to group by that column; click a chip to reverse the order of its groups (ShowGroupPanel, GroupFields) |
| 2 | Column headers: click to sort, drag to move or group; the funnel icon opens the column's value list |
| 3 | Filter row: one condition per column (ShowFilterRow, ColumnFilter) |
| 4 | Group row with the record count and the group summary of each column |
| 5 | Record; the arrow expands its detail views (DetailViews) |
| 6 | Detail views of the expanded record, one tab per related source |
| 7 | Footer with the column summaries (ShowFooter, a column's Summary) |
| 8 | Filter bar, shown while columns are filtered (ShowFilterBar) |
Key features
- Bound like a datasheet:
RecordSourceandKeyFieldare all it needs; the columns come from the fields (designer page Record source, „Create columns from fields“). Access writes changes back, with Before and After events, validation rules, and a row for new records. - Grouping: by the user in the group panel or by code (
GroupFields), on several levels, with group rows that show the record count and the column summaries. - Column filters: a filter row, a value list in each column header, and a filter bar, plus a search across all visible columns (Ctrl+F).
- Master-detail without code: the grid follows the current record of its form, another grid on the form (
LinkMasterFields = "Grid1"or"Grid1.KundeID"), or a recordset; an Access subform can follow the grid the same way. - Detail views: a record expands to show the related records from other sources, one tab per source (
DetailViews,AddDetailView). - Server mode for record sources with a million records or more (
DataMode = wgdServer): the grid reads only the records it shows, and filtering, grouping, and totals run on the source. It reads linked SQL Server and Wkl OData tables directly, bypassing the Access database engine. - Export to Excel, XML, and JSON as displayed: groups become an outline, and group summaries become
SUBTOTALformulas. - Designer with its own pages and a live preview of the real data.
- UI Automation: records, group rows, the filter row, the group panel, and detail views are exposed to screen readers and test tools.
Core concepts
The data stays in your tables. The grid reads the record source and writes each change back
through Access (AutoSave), one record at a time, in a transaction. When the data source rejects a
change (because of a validation rule, a required field, or a data service behind an ODBC link), the
grid reports the Access or driver message in DataError and marks the row. A new record keeps its
values until the user corrects or discards them; an existing record shows the value from the source
again.
Records are flat; groups are a view. Every record in the source is one row. Grouping doesn't
change the records or read them again: it's a view that the user changes in the group panel and VBA
changes through GroupFields. A group row doesn't represent a record; it shows the summaries of
its records.
Two ways to read. By default (wgdMemory), the grid loads every record and filters, groups,
and totals in memory, which suits tens of thousands of records. In server mode (wgdServer), it
asks the source for the record count and for the page it displays, and every filter, group, and
total becomes a statement that runs on the source. The mode is a setting on the form and doesn't
affect the code that uses the grid: the members are the same in both modes.
Master-detail in three directions. The grid can be the detail of its form (LinkChildFields,
LinkMasterFields with fields of the form), the detail of another WKL control on the form
(LinkMasterFields names the control), or the master of an Access subform (the subform's
LinkMasterFields names the grid). Code can set the master values directly (MasterValues), for
example on Excel and Word UserForms, which have no form record.
Changes by the user and by code. Typing, the group panel, the filter row, and the shortcut menu
raise events (BeforeColumnFilter, BeforeGroupExpand, BeforeInsert, …) in which VBA can adjust
or cancel the change. The same changes made by code (ColumnFilter, GroupFields, CellValue)
raise none of these events, just as setting a value in code doesn't raise AfterUpdate for an
Access control.
Any form, including a page in a subform. The grid belongs to the form it sits on; on a page
shown in a subform control, that's the page's form. Its keys (F2, Ctrl+F, Esc) work in the grid
wherever it is. A name in LinkMasterFields is looked up only on that same form, so a grid on a
page can't name a control on the main form; use MasterValues from code instead. Anchors work on
such a page as on any form, so a grid anchored in both directions follows the page
(layout overview). To arrange it with other areas, put it
in an area of a WklDockLayout with FillSection on that page.
Administration
Troubleshooting
For developers
Related topics
None