WklGrid

Dim myWklGrid As WklGrid
Set myWklGrid = Me!ControlName.Object

A flat, data-bound grid: the records of a table or query in columns, grouped by the user or by code, with a row for new records, linked to a master record like a subform.

None

WklGrid is built on the engine of `WklTreeList` and has its columns, bands, format rules, cell editors, totals, search, filter, master-detail (LinkChildFields, LinkMasterFields, MasterRecordset, MasterValues) and export. Its interface is its own: members of a tree (ParentField, ExpandLevel, lines, drag and drop) are not part of it. Members with the same name as in WklTreeView or WklTreeList behave as described there, with “record” for “node”; the members below that differ are described on this page.

Every record of the record source is a row; all records are read at once. GroupFields groups the loaded records without reading them again (unless a group field was not read yet): group rows show the field, the value and the number of records and can be expanded and collapsed. The text of a record, which also shows the text of its group row, is the value of its first column.

With AllowAdditions and AllowEditing, the grid shows a row for a new record (NewItemRow), as an Access datasheet does. Values typed into it are kept until the user leaves the row (another record, Shift+Enter); then the record is added with the link fields of the master and a key (the one typed, else AutoNumber, else the next number). Esc or Delete on the row discards it. When the data source refuses a write - a required field, a validation rule, an ODBC source such as a data service that rejects the change - DataError reports the text of Access or the driver and the row shows a red bar on its left edge until it is written successfully. Without a DataError procedure (or when it leaves Response at its default), a message box “WKL Grid” shows the text. The row for a new record keeps the typed values and the focus; an existing record is read again and shows the stored value, because the write was rolled back.

A cell is written when its editor is left: Enter, Tab, or a click elsewhere in the grid; a check box at once. The row for a new record is added when the user leaves the row. A value still open in an editor is written when the form closes, when a subform control shows another page (SourceObject), and before F5 - as Access saves the current record; a text that is no value of the field cannot be written and is then discarded.

Master-detail works in three ways without code: the grid follows the current record of its form like a subform (LinkChildFields, LinkMasterFields); it follows the selected record of another grid of the same form (LinkMasterFields = "Grid1" or "Grid1.KundeID"), and an Access subform follows the grid in the same way; and its records expand into detail views below them, one tab per related source (DetailViews, AddDetailView).

The export (ExportToXlsx, ExportToXml, ExportToJson, ExportToFile) writes the rows as the grid shows them: group rows with their text and group summaries, the records below them. In the workbook every level of groups is an outline level, the records of a group the user collapsed are hidden rows, and a group's summary is a SUBTOTAL formula over its rows - the footer's SUBTOTAL skips it, so nothing is counted twice and filtering in Excel keeps both right. XML and JSON carry the summaries of a group beside its text. With ExportRows = all, collapsed groups are exported too; the row for a new record never is. In server mode the records are read from the source for the export; more rows than a workbook holds (1,048,574) raise wkeInvalidValue.

The shortcut menu of a record (right mouse button, the menu key or Shift+F10; with ContextMenu = False only the custom entries):

The menu of a column header (right mouse button on the header) groups by the column or removes a grouping, shows the group panel and the filter row, and clears filters.

Keys:

A KeyDown procedure that sets KeyCode to 0 takes a key away from the grid; a row command with a shortcut wins over the grid's own key.

Buttons of the form or entries of a ribbon can run the same commands: NewRecord, DeleteSelected, EditSelected, CopySelected, ShowExportDialog and ShowContextMenu act on the focused record as the menu and the keys do, and CanAdd, CanDelete, CanEdit and CanExport tell whether each is available; CommandStateChange occurs when one of them changes. See How to: Run the grid's commands from your own buttons.

A detail view has the same shortcut menu and keys, on the records of its tab: Exportieren … exports that tab's records. It takes ContextMenu, AllowSearch and AllowExport from the grid, as it takes AllowEditing, AllowAdditions and AllowDeletions; custom menu entries are not shown there, and its events (MenuItemClick included) do not reach the form module.

Anchors: Access applies the anchors of a page's controls (a form in a subform control) when the Access window changes size, not when the page's width is set by code; a WKL control anchored left and right follows the page at once (see “Inside a page” in Layout overview). Anchor the grid in the page's design to let it fill the page.

For screen readers and test tools (UI Automation), a group row is a data item of the type “Gruppenzeile” that expands and collapses (ExpandCollapse), a record with detail views expands them the same way, the filter row is a header “Filterzeile” with one edit field per column whose value is the condition, and the group panel is a toolbar “Gruppierungsleiste” with one button per level; invoking it turns the order of the level's groups around.

An ActiveX control: inserted into a form in design view (Form Design › Controls › ActiveX Controls); reports are not supported. VBA reaches it through the Object property of the control, where ControlName is the name of the control on the form.

With Me!Grid1.Object
    .RecordSource = "tblCustomers"
    .KeyField = "CustomerID"
    .Columns.Add "CompanyName", "Company"
    .Columns.Add "City", "City"
    .Columns.Add("Turnover", "Turnover").Summary = wsuSum
    .GroupFields = "Country;City"
    .ExpandAll
End With

None

Property Get SelectedKey() As Variant
Property Let SelectedKey(RHS As Variant)

Gets or sets the key of the selected record.

None

Property value: Variant – The key of the selected record; Null when no record is selected or the row for a new record has the focus.

Setting a key selects that record, expands its groups and scrolls it into view; setting Null clears the selection. With MultiSelect, SelectedKey is the focused record of the selection; SelectedKeys returns all of them.

Default member: it can be left out, as in myWklGrid(...) for myWklGrid.SelectedKey(...). Read/write.

None

Declared by WklGrid.

Property Get RecordSource() As String
Property Let RecordSource(RHS As String)

Gets or sets the table, query or SQL statement that delivers the records.

None

Property value: String – A table name, a query name or an SQL statement. Queries may contain form references.

The name is RecordSource, not RowSource: an ActiveX property named RowSource makes the Access property sheet crash. Changing the value reloads the grid.

Read/write.

None

Declared by WklGrid.

Property Get KeyField() As String
Property Let KeyField(RHS As String)

Gets or sets the field with the unique key of a record.

None

Property value: String – A field name of the record source, or several separated by semicolons for a composite key (OrderID;LineNo).

A duplicate key raises wkeDuplicateKey when the grid loads. With a composite key, keys are arrays in VBA (Array(4711, 2)).

Read/write.

None

Declared by WklGrid.

Property Get HideSelection() As Boolean
Property Let HideSelection(RHS As Boolean)

Gets or sets whether the selection is hidden while another control has the focus.

None

Property value: Boolean – True (default) to hide it.

Read/write.

None

Declared by WklGrid.

Property Get BackColor() As OLE_COLOR
Property Let BackColor(RHS As OLE_COLOR)

Gets or sets the background color.

None

Property value: OLE_COLOR – A color number as in Access; system colors from &H80000000 on. Default: the Windows window color.

Read/write.

None

Declared by WklGrid.

Property Get ForeColor() As OLE_COLOR
Property Let ForeColor(RHS As OLE_COLOR)

Gets or sets the text color.

None

Property value: OLE_COLOR – A color number as in Access; system colors from &H80000000 on. Default: the Windows window text color.

Read/write.

None

Declared by WklGrid.

Property Get FontName() As String
Property Let FontName(RHS As String)

Gets or sets the font name.

None

Property value: String – The name of an installed font. Default: Segoe UI.

Read/write.

None

Declared by WklGrid.

Property Get FontSize() As Long
Property Let FontSize(RHS As Long)

Gets or sets the font size in points.

None

Property value: Long – The size in points. Default: 9.

Read/write.

None

Declared by WklGrid.

Property Get Recordset() As Object
Property Set Recordset(RHS As Object)

Gets or sets the DAO or ADO recordset the records were loaded from.

None

Property value: Object – The recordset, or Nothing when the grid is bound through RecordSource.

Assign with Set: all records are loaded at once from the recordset. A grid loaded this way does not write back with AutoSave and shows no row for a new record, because it has no record source to write to.

Read/write.

None

Declared by WklGrid.

Sub Requery()

Reads the record source again.

None

The selection and the expanded groups are kept as far as they still exist. Raises AfterRequery.

None

Declared by WklGrid.

Sub ExpandAll()

Expands every group.

None

None

None

Declared by WklGrid.

Sub CollapseAll()

Collapses every group.

None

None

None

Declared by WklGrid.

Sub EnsureVisible(ByVal Key As Variant)

Expands the groups of a record and scrolls it into view.

None

None

Declared by WklGrid.

Property Get AutoSave() As Boolean
Property Let AutoSave(RHS As Boolean)

Gets or sets whether changes are written into the record source right away.

None

Property value: Boolean – True (default) to write every change; False to change only the display.

Each operation is written in one transaction; BeforeUpdate can cancel it and roll back what was already written. A grid loaded from a Recordset never writes.

Read/write.

None

Declared by WklGrid.

Property Get MultiSelect() As Boolean
Property Let MultiSelect(RHS As Boolean)

Gets or sets whether several records can be selected.

None

Property value: Boolean – True to allow Ctrl+click and Shift+click; False (default) for a single selection.

Ctrl+click adds a record to the selection or removes it, Shift+click and Shift+↑ / ↓ select the records from the last one chosen without Shift, Ctrl+Shift+click adds that range. Group rows and the row for a new record are never selected. Deleting, copying and exporting the selection act on all selected records.

Read/write.

None

Declared by WklGrid.

Property Get SelectedKeys() As Variant
Property Let SelectedKeys(RHS As Variant)

Gets or sets the keys of all selected records.

None

Property value: Variant – A Variant array of keys, in display order.

Setting an array selects those records; setting an empty array clears the selection.

Read/write.

None

Declared by WklGrid.

Property Get ContextMenu() As Boolean
Property Let ContextMenu(RHS As Boolean)

Gets or sets whether the built-in context menu opens on the right mouse button.

None

Property value: Boolean – True (default) to show it; False to show only the custom items of AddMenuItem.

The built-in menu of WklTreeView (the menus of WklTreeList and WklGrid are described on their pages):

With ContextMenu = False only the custom entries are shown. Before any menu opens, NodeRightClick occurs; setting its Cancel shows no menu at all, for example to show an Access shortcut menu instead. The menu key and Shift+F10 open the same menu at the selected node, without NodeRightClick.

Read/write.

None

Declared by WklGrid.

Property Get AllowAdditions() As Boolean
Property Let AllowAdditions(RHS As Boolean)

Gets or sets whether the user can add records.

None

Property value: Boolean – True to show the row for a new record (with AllowEditing and NewItemRow); False (default) to prevent adding.

Read/write.

None

Declared by WklGrid.

Property Get AllowDeletions() As Boolean
Property Let AllowDeletions(RHS As Boolean)

Gets or sets whether the user can delete records.

None

Property value: Boolean – True to allow Delete and the menu item; False (default) to prevent it.

The selected records are deleted after a confirmation, which BeforeDelete can suppress.

Read/write.

None

Declared by WklGrid.

Sub BeginUpdate()

Suspends drawing until the matching EndUpdate.

None

Use it around many changes made by code. Calls can be nested.

None

Declared by WklGrid.

Sub EndUpdate()

Ends a BeginUpdate.

None

The last EndUpdate of a nesting redraws the grid once.

None

Declared by WklGrid.

Property Get EventProcedures() As String
Property Let EventProcedures(RHS As String)

Lists the events that have an event procedure in the form module.

None

Property value: String – The event names, separated by semicolons. Writing is ignored.

Access never lists the events of an ActiveX control on the property sheet's Event tab. This row of the Other tab is the way to them: its [...] button opens the Events page, which creates or opens the event procedures.

Read/write.

None

Declared by WklGrid.

Property Get AllowSearch() As Boolean
Property Let AllowSearch(RHS As Boolean)

Gets or sets whether Ctrl+F opens the search bar in form view.

None

Property value: Boolean – True (default) to allow the search bar.

The search bar searches every visible column of the records the column filter leaves, as the cells show the values, and counts at most 1,000 matches. F3 and Shift+F3 move between matches; “Nur Treffer” filters to the matches.

Read/write.

None

Declared by WklGrid.

Property Get SearchText() As String
Property Let SearchText(RHS As String)

Gets or sets the search text.

None

Property value: String – The text; the cells of visible columns that contain it are highlighted. An empty string ends the search.

The search compares the text the cells show (formatted numbers and dates, lookup texts) and ignores case. It finds only records the column filter leaves, and runs again when the column filter or the grouping changes. SearchCount returns the number of matching records; FindNext selects them in display order, including records in collapsed groups, and expands their groups.

Read/write.

None

Declared by WklGrid.

Function FindNext(Optional ByVal Backward As Boolean = False) As Variant

Selects the next match of SearchText and returns its key.

Return value: Variant – The key of the selected match, or Null when there is no match.

None

None

Declared by WklGrid.

Property Get SearchCount() As Long

Returns the number of matches of SearchText.

None

Property value: Long – The number of matches.

Read-only.

None

Declared by WklGrid.

Property Get Filter() As String
Property Let Filter(RHS As String)

Gets or sets a filter that limits the records.

None

Property value: String – A WHERE condition of the record source (as Form.Filter), or a JPath expression starting with $ over the loaded records. An empty string shows all records.

A WHERE condition queries the record source each time it is set; for a pass-through query the whole query runs on the server. Groups show only the records the filter leaves.

Filter chips (AddFilterChip) narrow the records further: the grid applies (Filter) AND (chip 1) AND (chip 2) .... Filter keeps returning only the text set here, without the chips. While chips are set, a JPath filter is refused with wkeInvalidValue.

Read/write.

None

Declared by WklGrid.

Sub AddMenuItem(ByVal Caption As String, ByVal Tag As String, Optional ByVal BeginGroup As Boolean = False)

Adds a custom item to the context menu.

Adding an item with a tag that exists replaces that item. In WklTreeList, WklGantt and WklGrid, MenuItemEnabled, MenuItemChecked, MenuItemGlyph, MenuItemShortcut and MenuItemParent change an item afterwards, and MenuPlacement sets where the items stand.

None

Declared by WklGrid.

Sub RemoveMenuItem(ByVal Tag As String)

Removes the custom menu item with a tag.

None

None

Declared by WklGrid.

Sub ClearMenuItems()

Removes all custom menu items.

None

None

None

Declared by WklGrid.

Property Get Layout() As String
Property Let Layout(RHS As String)

Gets or sets the view state as text: expanded groups and selection, and the arrangement of the columns, without the records.

None

Property value: String – The layout text.

Store the text between sessions (for example in a table or TempVars) and assign it back to restore the view. Since type library 1.28 the text also holds the arrangement of the columns, the column filters and DetailHeight ("arrangement", as AutoSaveLayout remembers it); a text without it shows them as designed. Set before the grid shows (in Form_Load), the text wins over the remembered arrangement.

Read/write.

None

Declared by WklGrid.

Property Get FormatEvents() As Boolean
Property Let FormatEvents(RHS As Boolean)

Gets or sets whether RowFormat and CellFormat are raised.

None

Property value: Boolean – True to raise the events; False (default) to skip them.

Access passes every event call to the form, handled or not, at a cost of about 3 ms per call; the formatting events run for every visible record or cell on every redraw. Switch this on only when the form handles the events.

Read/write.

None

Declared by WklGrid.

Property Get LivePreview() As Boolean
Property Let LivePreview(RHS As Boolean)

Gets or sets whether design view shows the real data.

None

Property value: Boolean – True to draw the records of the record source in design view; False (default) to draw sample records.

With False, drawing the form in design view never queries the record source, so forms bound to pass-through queries or large linked tables open in design view without delay. Form view always reads the data. A grid linked to a master (LinkChildFields) shows in design view the records of the form's first record, because design view has no current record.

Read/write.

None

Declared by WklGrid.

Property Get LinkChildFields() As String
Property Let LinkChildFields(RHS As String)

Gets or sets the fields that link the records to a master record, as on an Access subform.

None

Property value: String – Field names of the record source, separated by semicolons; empty (default) for a grid without master.

With LinkChildFields set, the grid shows only the records whose link fields equal the master values (see LinkMasterFields, MasterValues and MasterRecordset). Without a master record - a new or empty master - the grid is empty and new records are refused. New records, also those of the row for a new record, get the master values in their link fields. The value is saved with the form.

Read/write.

With Me!Grid1.Object
    .LinkChildFields = "OrderID"
    .LinkMasterFields = "ID"
End With

Declared by WklGrid.

Property Get LinkMasterFields() As String
Property Let LinkMasterFields(RHS As String)

Gets or sets the fields or controls of the master that hold the values for LinkChildFields.

None

Property value: String – Names in the same order as LinkChildFields, separated by semicolons.

The master is the form the control sits on, unless MasterRecordset or MasterValues is set. A name is looked up as a field of the form's recordset first and as a control of the form second, as LinkMasterFields of an Access subform. In form view the control checks the master record on idle (every 250 ms) and reloads when it changed; with ControlSource set, Access reports each change at once through MasterValue.

A name can also name another WKL control of the same form (WklGrid, WklTreeList, WklTreeView, WklGantt) as the master. Only the form the control sits on is searched: a control inside a subform cannot name a control of the main form; use MasterValues or MasterRecordset from code for that case. Grid1 stands for its key (every part of a composite key), Grid1.KundeID for a field of its selected record - the key, a column, or any other field of its record source. The control then follows the selection of that control without code: at once for a change made by code, after a short pause while the user moves through the records with the keyboard. With nothing selected there, or a group row, the control shows no records. Names of both kinds can be mixed. In design view with LivePreview, the first record of the master control's record source is the master.

The other way round, an Access subform follows a WKL control when its LinkMasterFields names the control (Grid1) and its LinkChildFields the field that holds the key: Access reads the selected key through the control's Value, and the control requeries such subforms when its selection changes, because Access does not do it by itself.

The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Property Get MasterValue() As Variant
Property Let MasterValue(RHS As Variant)

Gets or sets the value Access writes into the control on every record change of the form when ControlSource is set.

None

Property value: Variant – The current value of the control's ControlSource.

MasterValue is the bindable default property of the control for Access' data binding: set the control's ControlSource (property sheet, tab Data) to a link field such as ID, or to an expression over several fields, and Access writes the current value on every record change - also for filters and new records. The value is only the trigger: the master values are read from LinkMasterFields. Without ControlSource, the idle check does the same with a delay of up to 250 ms.

Read/write.

None

Declared by WklGrid.

Property Get MasterValues() As Variant
Property Let MasterValues(RHS As Variant)

Gets or sets the master values the records are limited to.

None

Property value: Variant – A value for one link field, or an array with one value per link field; Null when there is no master record.

Reading returns the current master values, wherever they come from. Setting a value takes it from code and ignores the form and MasterRecordset until MasterValues is set to Null; this works in any container, also on Excel, Word and Outlook UserForms, which have no form recordset. Setting from code raises no events.

Read/write.

UserForm1.List1.MasterValues = Array("A", 4711)   ' two link fields, on an Excel UserForm

Declared by WklGrid.

Property Get MasterRecordset() As Object
Property Set MasterRecordset(RHS As Object)

Gets or sets a DAO or ADO recordset whose current record is the master.

None

Property value: Object – The recordset, or Nothing to use the form again.

Assign with Set. The control reads LinkMasterFields from the current record of the recordset, for example of another subform's recordset or a combo box's. In form view it checks the record on idle; SyncMaster reads it at once. A field that the recordset does not have raises wkeInvalidValue.

Read/write.

None

Declared by WklGrid.

Sub SyncMaster()

Reads the master values again and reloads the records when they changed.

None

Needed only when the master changes outside form view or must take effect at once, for example after moving MasterRecordset by code. Raises no events.

None

Declared by WklGrid.

Property Get MasterFilterMode() As WklMasterFilterMode
Property Let MasterFilterMode(RHS As WklMasterFilterMode)

Gets or sets how a linked grid selects the records of the master record.

None

Property value: `WklMasterFilterMode` – wmfSource (default) to read the records of each master record through a WHERE condition on the record source; wmfLocal to read the record source once and filter it in memory.

Applies only with LinkChildFields set. wmfLocal is meant for sources that Access can only wrap, such as a pass-through query or a slow linked table: with wmfSource every master change runs the whole query again. With wmfLocal the grid reads all records once, together with the link fields, and each master change only filters them; Requery and every change the grid writes read the source again. The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Property Get Columns() As WklColumns

Returns the columns.

None

Property value: `WklColumns` – The `WklColumns` collection, for example Columns("Price").Width = 1200.

Read-only.

None

Declared by WklGrid.

Property Get Bands() As WklBands

Returns the bands of the header.

None

Property value: `WklBands` – The `WklBands` collection.

Read-only.

None

Declared by WklGrid.

Property Get FormatRules() As WklFormatRules

Returns the conditional formatting rules.

None

Property value: `WklFormatRules` – The `WklFormatRules` collection.

Read-only.

None

Declared by WklGrid.

Property Get ColumnLayout() As String
Property Let ColumnLayout(RHS As String)

Gets or sets the columns, bands and format rules as text.

None

Property value: String – The layout text that Access saves with the form.

In the property sheet, [...] opens the column designer. Copy the text to transfer a layout from one list to another.

Read/write.

None

Declared by WklGrid.

Property Get ShowHeader() As Boolean
Property Let ShowHeader(RHS As Boolean)

Gets or sets whether the column headers are shown.

None

Property value: Boolean – True (default) to show them.

Read/write.

None

Declared by WklGrid.

Property Get GridLines() As WklGridLines
Property Let GridLines(RHS As WklGridLines)

Gets or sets the grid lines.

None

Property value: `WklGridLines` – A `WklGridLines` constant. Default: wglBoth.

Read/write.

None

Declared by WklGrid.

Property Get ShowFooter() As Boolean
Property Let ShowFooter(RHS As Boolean)

Gets or sets whether the footer with the column totals is shown.

None

Property value: Boolean – True to show it; False (default) to hide it.

Read/write.

None

Declared by WklGrid.

Property Get RowHeight() As Long
Property Let RowHeight(RHS As Long)

Gets or sets the height of a record row in twips.

None

Property value: Long – The height; 0 (default) to derive it from the font.

Read/write.

None

Declared by WklGrid.

Property Get AlternateBackColor() As Long
Property Let AlternateBackColor(RHS As Long)

Gets or sets the background color of every second row.

None

Property value: Long – A color number; -1 (default) for the same color as BackColor.

Read/write.

None

Declared by WklGrid.

Property Get AllowEditing() As Boolean
Property Let AllowEditing(RHS As Boolean)

Gets or sets whether cells can be edited.

None

Property value: Boolean – True (default) to allow editing with F2, Enter, typing or a double-click.

WklColumn.ReadOnly protects single columns. Group rows are never editable. Without editing, the row for a new record is not shown.

Read/write.

None

Declared by WklGrid.

Property Get FocusedColumn() As String
Property Let FocusedColumn(RHS As String)

Gets or sets the column of the focused cell.

None

Property value: String – The column name.

Read/write.

None

Declared by WklGrid.

Property Get CellValue(ByVal Key As Variant, ByVal Column As String) As Variant
Property Let CellValue(ByVal Key As Variant, ByVal Column As String, RHS As Variant)

Gets or sets the value of a cell.

Property value: Variant – The stored value of the field.

Setting the value saves it; with AutoSave it is written into the record source.

Read/write.

None

Declared by WklGrid.

Property Get CellText(ByVal Key As Variant, ByVal Column As String) As String

Returns the displayed text of a cell.

Property value: String – The text with the column's format applied; for a lookup column the displayed list value.

Read-only.

None

Declared by WklGrid.

Property Get SummaryValue(ByVal Column As String) As Variant

Returns the total of a column.

Property value: Variant – The value of the column's Summary over the loaded records; Null without a summary.

Read-only.

None

Declared by WklGrid.

Sub BestFitColumns()

Sets the width of every visible column to fit its header and contents.

None

Measures the header and the text of every loaded record in each visible column as the list shows it (format, lookup text, label of a status column) and adds the indent of the record's level in the tree column. At most the first 5,000 records in display order (a parent before its children) are measured. The widths are saved with the form like widths the user drags.

Loaded records: without LazyLoad, all records, also those below collapsed nodes. With `LazyLoad`, only the levels read so far, that is the levels down to `ExpandLevel` and the children of nodes that were expanded before; children read later, when their node is expanded, are not measured. Call BestFitColumns again after `ExpandAll` or after expanding nodes. Records not read yet (before the first load) leave only the headers to measure.

Call it while the control is shown in form view (for example in Form_Current or after Requery), not in Form_Open: without its window the control measures the texts without the tree indent and without the room for symbols and labels, so records of deeper levels stay shortened. There is no separate fitting for child records; the measurement above already includes every loaded level.

An error of the method itself raises a run-time error in VBA. Records the control reads on its own, such as the children of a node expanded with LazyLoad, report a failure through `DataError` instead; unless the event procedure sets Response to 0 (acDataErrContinue), the control shows the message in a box with its name as title (“WKL TreeList”).

None

Declared by WklGrid.

Sub EditCell(ByVal Key As Variant, ByVal Column As String)

Opens the editor of a cell in form view.

None

None

Declared by WklGrid.

Property Get GroupFields() As String
Property Let GroupFields(RHS As String)

Gets or sets the fields by which the records are grouped.

None

Property value: String – Field names separated by semicolons, outermost first, each optionally followed by DESC for descending groups; empty for no grouping.

Every distinct value becomes a group row with the column caption, the value ((leer) for an empty value), the number of records and the group summaries of the columns with a Summary. Users change the grouping in the group panel (ShowGroupPanel). Grouping changes only the view of the loaded records: selection and expanded groups stay, and the records are read again only when a group field was not read yet. Group rows stand for no record: they cannot be edited or deleted, and totals count only records.

Read/write.

Me!Grid1.Object.GroupFields = "Country;City DESC"

Declared by WklGrid.

Sub ExportToXlsx(ByVal Path As String, Optional ByVal Mode As WklExportMode = wemDataAware, Optional ByVal Rows As WklExportRows = werAll, Optional ByVal RawValues As Boolean = False, Optional ByVal AutoStart As Boolean = False)

Exports the records as an Excel workbook, without Excel.

The visible columns are exported in display order with their values and types; numbers and dates keep the column's format. The records are written in the order of the sorted columns, without group rows; werVisible writes the same records as werAll (those the filter and the master leave). Footer totals are written as SUBTOTAL formulas with their value. The header row is frozen and repeated on every printed page. A missing folder raises wkeInvalidValue. With wemWysiwyg the workbook shows the columns as displayed: bands and multi-line records as merged cells, the colors and fonts of the format rules, grid lines.

None

Declared by WklGrid.

Sub ExportToFile(ByVal Path As String, Optional ByVal Format As WklExportFormat = wefAuto, Optional ByVal Mode As WklExportMode = wemDataAware, Optional ByVal Rows As WklExportRows = werAll, Optional ByVal RawValues As Boolean = False, Optional ByVal AutoStart As Boolean = False)

Exports the list in a file format.

wefAuto takes the format from the extension: .xlsx (also without extension), .xml or .json; another extension raises wkeInvalidValue. A path without extension gets the one of the format. Mode applies to the workbook only: XML and JSON always hold the data of the list. The rules of ExportToXlsx apply to the XLSX format, those of ExportToXml and ExportToJson to the others.

None

Declared by WklGrid.

Sub ExportToXml(ByVal Path As String, Optional ByVal Rows As WklExportRows = werAll, Optional ByVal RawValues As Boolean = False, Optional ByVal AutoStart As Boolean = False)

Exports the records as XML, without Excel: the columns, the records with their values, and the sums.

The format is that of `WklTreeList.ExportToXml`; the records are row elements of rows without children, in the order of the sorted columns. Group rows are not exported, and werVisible writes the same records as werAll.

None

Declared by WklGrid.

Sub ExportToJson(ByVal Path As String, Optional ByVal Rows As WklExportRows = werAll, Optional ByVal RawValues As Boolean = False, Optional ByVal AutoStart As Boolean = False)

Exports the records as JSON, without Excel: the columns, the records with their values, and the sums.

The format is that of `WklTreeList.ExportToJson`; the records are the entries of rows without children, in the order of the sorted columns. Group rows are not exported, and werVisible writes the same records as werAll.

None

Declared by WklGrid.

Property Get AllowExport() As Boolean
Property Let AllowExport(RHS As Boolean)

Gets or sets whether the shortcut menu offers Export ….

None

Property value: Boolean – True (default) to offer the export; False to hide the item.

The user chooses the file and the format (Excel workbook, XML, JSON) in the save dialog; ExportMode and ExportRows decide the rest. An Excel workbook opens afterwards. The methods ExportToXlsx, ExportToXml, ExportToJson and ExportToFile work regardless of this setting. The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Property Get ExportMode() As WklExportMode
Property Let ExportMode(RHS As WklExportMode)

Gets or sets how the context menu's export writes an Excel workbook.

None

Property value: `WklExportMode` – wemDataAware (default) or wemWysiwyg.

Applies to the Excel workbook only; XML and JSON always hold the data of the list. Invalid values raise wkeInvalidValue. The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Property Get ExportRows() As WklExportRows
Property Let ExportRows(RHS As WklExportRows)

Gets or sets which records the context menu's export writes.

None

Property value: `WklExportRows` – werAll (default) or werVisible.

For the grid both write the records the filter and the master leave; collapsed groups do not limit the export. Invalid values raise wkeInvalidValue. The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Property Get RecordCount() As Long

Returns the number of records the grid shows.

None

Property value: Long – The number of records the filter, the search filter and the master leave, grouped or not; the row for a new record does not count.

Read-only.

None

Declared by WklGrid.

Property Get NewItemRow() As WklNewItemRow
Property Let NewItemRow(RHS As WklNewItemRow)

Gets or sets where the row for a new record appears.

None

Property value: `WklNewItemRow` – A WklNewItemRow value; wniBottom (default) below the last record, wniTop above the first, wniNone for no row.

The row appears only with AllowAdditions and AllowEditing, and only when the grid writes to its record source (not with Recordset or a loaded file). See the class remarks for how the row is saved.

Read/write.

None

Declared by WklGrid.

Property Get ShowGroupPanel() As Boolean
Property Let ShowGroupPanel(RHS As Boolean)

Gets or sets whether the group panel is shown above the column headers.

None

Property value: Boolean – True (default) to show the panel; False to hide it.

Drag a column header onto the panel to group by that column, at the place it is dropped; drag a group off the panel to remove it, or within the panel to change the order; click a group to turn the order of its groups around. The shortcut menus of the panel and of the column headers offer the same, and expanding or collapsing all groups. Every change sets GroupFields, so the grouping is saved with the form. Group rows show the column caption, the value and the number of records, and the summaries of the columns with a Summary; Right and + expand a selected group row, Left and - collapse it. The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Property Get ShowFilterRow() As Boolean
Property Let ShowFilterRow(RHS As Boolean)

Gets or sets whether the filter row is shown below the column headers.

None

Property value: Boolean – True to show the row; False (default) to hide it.

Each cell of the filter row takes one condition for its column, applied while the user types. A leading operator (=, <>, <, <=, >, >=) works for every column; text without an operator means “begins with”, or LIKE when it contains * or ?; a number or date without an operator means “equals”. leer selects blank values; “not empty” in the language of the components (not empty, nicht leer, ni prazno …) selects the others. Numbers and dates are read in the Windows regional format first and invariantly second, so in a German Windows both 1,5 and 1.5 mean one and a half; thousands separators are not accepted. Input that does not fit the column, such as letters in a number column, is shown in red and leaves the filter unchanged. Esc clears the cell. The value is saved with the form; the conditions are not.

Read/write.

None

Declared by WklGrid.

Property Get ColumnFilter(ByVal Column As String) As String
Property Let ColumnFilter(ByVal Column As String, RHS As String)

Gets or sets the condition of one column, in the format of the filter row.

Property value: String – The condition, for example >= 100, Bo* or leer; an empty string removes it.

Reading returns the text of the filter row cell; for a condition from the value list it returns its short form. Setting text that does not fit the column raises wkeInvalidValue; an unknown column raises wkeKeyNotFound. The column filter applies to the loaded records, in addition to Filter, which limits what is read from the record source. Setting it from VBA raises no event.

Read/write.

Me.Grid.Object.ColumnFilter("Ort") = "Bo*"
Me.Grid.Object.ColumnFilter("Menge") = ">= 100"
Debug.Print Me.Grid.Object.ColumnFilterText

Declared by WklGrid.

Property Get ColumnFilterValues(ByVal Column As String) As Variant
Property Let ColumnFilterValues(ByVal Column As String, RHS As Variant)

Gets or sets the values of a column that pass the filter, as the value list in the column header selects them.

Property value: Variant – An array of values; Null or Empty removes the condition. Reading returns Empty when the column has no value condition.

The funnel button in a column header opens the value list: every value of the loaded records with its count, a search box, and “(Select all)”. Ticking all values removes the condition.

Read/write.

None

Declared by WklGrid.

Property Get ColumnFilterEnabled() As Boolean
Property Let ColumnFilterEnabled(RHS As Boolean)

Gets or sets whether the column filter applies.

None

Property value: Boolean – True (default) to filter; False to show all records without losing the conditions.

The check box of the filter bar sets the same value.

Read/write.

None

Declared by WklGrid.

Property Get ColumnFilterText() As String

Gets the conditions of the column filter as text, as the filter bar shows them.

None

Property value: String – For example [Ort] beginnt mit "Bo" und [Menge] >= 100; an empty string without a condition.

Read-only.

None

Declared by WklGrid.

Sub ClearColumnFilters()

Removes every column condition.

None

Filter stays as it is. Raises no event.

None

Declared by WklGrid.

Property Get ShowFilterBar() As Boolean
Property Let ShowFilterBar(RHS As Boolean)

Gets or sets whether the filter bar appears below the records while filter chips are set or columns are filtered.

None

Property value: Boolean – True (default) to show the bar; False to hide it.

The bar shows the filter chips first, one button “Caption ✕” each, which removes its chip. Then, while columns are filtered, the column conditions as text, a check box that switches the column filter off and on, and the button “Clear filter”. “Clear filter” removes only the column conditions, not the chips. With False the bar stays hidden; chips and column filters still apply. The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Property Get DataMode() As WklGridDataMode
Property Let DataMode(RHS As WklGridDataMode)

Gets or sets where the grid reads its records from.

None

Property value: `WklGridDataMode` – A WklGridDataMode value; wgdMemory (default) loads every record, wgdServer reads only the records shown.

Use wgdServer for record sources too large to load: linked SQL Server or Wkl OData tables with hundreds of thousands or millions of records. The column filter, the value lists, the search, the groups and the summaries then run as statements on the record source, so the back end does the work.

Server mode needs RecordSource and KeyField. How the grid reads depends on the record source:

• A linked ODBC table (SQL Server through ODBC Driver 18, Wkl OData): the grid opens its own ODBC connection with the link's connect string and asks for the number of records and for the page at a position (OFFSET … FETCH), past the Access database engine, which reads linked tables far slower. Writing still goes through Access. Over Wkl OData this needs a data service that allows pass-through statements for the data source and the caller; otherwise the driver refuses and the grid uses the next way.
• Any other record source (local tables, queries, SQL text), a link whose password Access did not save, or a refused pass-through: the grid reads the keys of the filtered, sorted records through Access in chunks and the values of the records shown by key. Totals the source refuses (Wkl OData without pass-through) stay empty.

A grid bound with Recordset, or with MasterFilterMode set to filter in memory, loads every record whatever the setting.

Differences to memory mode:

• Filter is part of every statement and must be an Access SQL condition; JPath raises wkeInvalidValue. With Filter set, a linked table is read through Access.
• A click on a column header asks the source again in the new order.
• Groups are ordered by their value.
• The search compares text columns with LIKE, and number and date columns by value when the search text reads as a number or a date.
• Text conditions of the filter row (contains, begins with, *) compare the stored values, not the formatted text.
• Columns Access cannot compare on a link, such as SQL Server datetime2 linked as Date/Time Extended, are left out of the search and refuse a column filter when the grid reads through Access.
• An edited record keeps its place until the next Requery; added and deleted records make the grid ask the source again.
• “Nur Treffer” in the search bar does not filter.

The setting is saved with the form; changing it reloads the records.

Read/write.

With Me.Grid1.Object
    .RecordSource = "tblUmsatz"   ' linked table with 1,000,000 records
    .KeyField = "ID"
    .DataMode = wgdServer
    .ColumnFilter("Ort") = "= Ort 07"
    Debug.Print .RecordCount, .SummaryValue("Betrag")
End With

Declared by WklGrid.

Property Get DetailViews() As String
Property Let DetailViews(RHS As String)

Gets or sets the detail views as JSON text: the records of other sources that belong to a record of the grid, shown below it when it is expanded.

None

Property value: String – The detail views as JSON; an empty string for none.

A grid with detail views shows an expand arrow at the left of every record. Expanding a record opens a tab per detail view below it, each with a grid of the detail records; the records below move down. The detail records are those whose LinkChildFields equal the LinkMasterFields of the record, as in an Access subform; the master fields can be the key, a column or any other field of the grid's record source.

The text has the form {"format":"WklGridDetails","version":1,"views":[{"name":…,"caption":…,"source":…,"key":…,"child":…,"master":…,"columns":…}]}. name, source (table, query or SQL statement), key, child and master are required, caption (tab text, default the name), columns (fields separated by semicolons, default every field except the link fields), editForm and editKeys (the view's edit form and its key fields, as EditForm and EditKeyFields) are optional. Setting text that is not valid raises wkeInvalidValue. AddDetailView and RemoveDetailView change the text from VBA without writing JSON.

A detail view reads through the same Access database as the grid, in the same DataMode, with the grid's font, colors, AllowEditing, AllowAdditions, AllowDeletions and NewItemRow; a record added in it gets the master's values in its link fields. With editForm and an EditFormMode of the grid other than wfmNone, the view's records open in that form (docked and automatic open a dialog there, as a detail view has no subform control of its own); BeforeEditForm and AfterEditForm are raised by the master grid. Its other events do not reach the form module; DetailGrid returns its grid for code. Changing the detail views closes every expanded record. The value is saved with the form.

What a definition holds, and what it does not:

• Columns: a view stores only the list of fields. The header of a column is the field name as the record source returns it; for an SQL statement that is the alias (SELECT Name AS Bezeichnung ...). The Caption property of a table field is not read. Use aliases in an SQL source for headers in another language than the field names. An alias equal to a field name of the same table ([Person] AS [Person]) is a circular reference for Access, and the view cannot be read; give the alias another name.
• No per-column settings: width, ReadOnly, format, lookup and status of a column cannot be stored in a view. The widths fit the records shown when a record is expanded. To change columns of an open view, use DetailGrid in the DetailExpand event; such a change lasts only while the record stays expanded (see DetailGrid).
• One level: a detail view cannot have detail views of its own. A detail grid starts without detail views every time a record is expanded.

Read/write.

None

Declared by WklGrid.

Property Get DetailHeight() As Long
Property Let DetailHeight(RHS As Long)

Gets or sets the height of the detail views below an expanded record.

None

Property value: Long – The height in twips, from 600 to 30000; the default is 3600 (about 6.3 cm).

Every expanded record gets the same height; the detail grid scrolls when its records do not fit. Setting a value outside the range raises wkeInvalidValue. The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Sub AddDetailView(ByVal Name As String, ByVal RecordSource As String, ByVal KeyField As String, ByVal LinkChildFields As String, ByVal LinkMasterFields As String, Optional ByVal Caption As String = "", Optional ByVal ColumnFields As String = "")

Adds a detail view, or replaces the one with the same name.

A missing part, or a different number of child and master fields, raises wkeInvalidValue. A replaced view keeps its tab position. The definitions are part of DetailViews and are saved with the form when they are added in design view.

With Me.Grid1.Object
    .RecordSource = "tblCustomers": .KeyField = "ID"
    .AddDetailView "Orders", "tblOrders", "OrderID", "CustomerID", "ID"
    .AddDetailView "Notes", "tblNotes", "NoteID", "Customer", "ID", "Notes on the customer", "Date;Note"
End With

Declared by WklGrid.

Sub RemoveDetailView(ByVal Name As String)

Removes a detail view.

An unknown name raises wkeInvalidValue. Every expanded record is closed.

None

Declared by WklGrid.

Sub ExpandDetail(ByVal Key As Variant, Optional ByVal ViewName As String = "")

Expands a record: opens its detail views below it.

Works in form view only (wkeNotConfigured otherwise, and when the grid has no detail views); an unknown key raises wkeKeyNotFound, an unknown view wkeInvalidValue. On an expanded record, ExpandDetail only changes the tab. The user expands a record with a click on its arrow or with Ctrl and +, and closes it the same way or with Ctrl and -.

A detail view whose records cannot be read shows the reason on its tab. A reload of the grid (Requery, another master record, a changed binding) closes every expanded record.

None

Declared by WklGrid.

Sub CollapseDetail(ByVal Key As Variant)

Closes the detail views of a record.

A record that is not expanded is left as it is.

None

Declared by WklGrid.

Property Get DetailExpanded(ByVal Key As Variant) As Boolean

Gets whether the detail views of a record are open.

Property value: Boolean – True when the record is expanded.

Read-only.

None

Declared by WklGrid.

Property Get DetailGrid(ByVal Key As Variant, Optional ByVal ViewName As String = "") As Object

Gets the grid of a detail view of an expanded record.

Property value: Object – The detail grid, a WklGrid.

Use it to read the detail records (SelectedKey, RecordCount, CellValue) or to change columns and filters of an open detail view. The object is valid while the record stays expanded. A record that is not expanded, or an unknown view, raises wkeInvalidValue.

Changes made through the detail grid - columns, captions, widths, filters - last only while the record stays expanded. Collapsing the record, Requery, another master record and a change of DetailViews build the detail grid anew from the definition. To apply a change every time, make it in the DetailExpand event, which occurs after the grid of the first tab exists. Calling AddDetailView on a detail grid is not supported: the definition has one level only.

Read-only.

Me.Grid1.Object.ExpandDetail 3, "Orders"
Debug.Print Me.Grid1.Object.DetailGrid(3, "Orders").RecordCount

Declared by WklGrid.

Sub NewRecord()

Starts a new record as the user does with Ctrl++ or the shortcut menu.

None

In a WklGrid the focus goes to the row for a new record and its first editable cell opens; the record is added when the user leaves the row (BeforeInsert, AfterInsert). In a WklTreeList a new node is added after the focused one, as Ins does (BeforeNodeAdd, AfterNodeAdd). With an edit form (EditForm, EditFormMode) the form opens in add mode instead, after the call has returned. Raises wkeNotAvailable when CanAdd is False or the control is not in form view.

Called in an event procedure of the same control (for example ItemCommand, RowCommand, ItemClick, RowClick), the command runs right after that procedure has ended, not during the call: VBA does not deliver an event of an object while an event procedure of the same object runs, so BeforeDelete, BeforeInsert or BeforeEditForm would otherwise not reach the form. The method returns before the command has run. Code after it in the same procedure still sees the old state - the record is still there, RecordCount is unchanged - and cannot tell whether the user or Cancel refused. The result shows in the events that follow: AfterDelete after a deletion, AfterInsert after a new record, AfterEditForm after the edit form. Code that reacts to the result - for example a WklToast note with Undo after a deletion - belongs there. Availability is checked at once: an unavailable command still raises wkeNotAvailable in the call.

Private Sub cmdNew_Click()
    Me!Grid1.SetFocus
    Me!Grid1.Object.NewRecord
End Sub

Declared by WklGrid.

Sub DeleteSelected()

Deletes the selected records as the Delete key does.

None

BeforeDelete occurs for each record, with its question unless the procedure sets Confirm to False; AfterDelete follows each deletion. With MultiSelect all selected records are deleted. Raises wkeNotAvailable when CanDelete is False (no AllowDeletions, nothing focused, a group row or the row for a new record) or the control is not in form view.

Called in an event procedure of the same control (for example ItemCommand, RowCommand, ItemClick, RowClick), the command runs right after that procedure has ended, not during the call: VBA does not deliver an event of an object while an event procedure of the same object runs, so BeforeDelete, BeforeInsert or BeforeEditForm would otherwise not reach the form. The method returns before the command has run. Code after it in the same procedure still sees the old state - the record is still there, RecordCount is unchanged - and cannot tell whether the user or Cancel refused. The result shows in the events that follow: AfterDelete after a deletion, AfterInsert after a new record, AfterEditForm after the edit form. Code that reacts to the result - for example a WklToast note with Undo after a deletion - belongs there. Availability is checked at once: an unavailable command still raises wkeNotAvailable in the call.

None

Declared by WklGrid.

Sub EditSelected()

Opens the editor of the focused cell, or of the first editable cell of the focused record; with an edit form, opens the form for the focused record.

None

BeforeCellEdit occurs first and can cancel; a cancel is no error. With EditForm and an EditFormMode other than wfmNone the edit form opens instead, after the call has returned (BeforeEditForm). Raises wkeNotAvailable when CanEdit is False or the control is not in form view. Call SetFocus on the control first, so that the user can type into the editor.

Called in an event procedure of the same control (for example ItemCommand, RowCommand, ItemClick, RowClick), the command runs right after that procedure has ended, not during the call: VBA does not deliver an event of an object while an event procedure of the same object runs, so BeforeDelete, BeforeInsert or BeforeEditForm would otherwise not reach the form. The method returns before the command has run. Code after it in the same procedure still sees the old state - the record is still there, RecordCount is unchanged - and cannot tell whether the user or Cancel refused. The result shows in the events that follow: AfterDelete after a deletion, AfterInsert after a new record, AfterEditForm after the edit form. Code that reacts to the result - for example a WklToast note with Undo after a deletion - belongs there. Availability is checked at once: an unavailable command still raises wkeNotAvailable in the call.

None

Declared by WklGrid.

Sub CopySelected()

Copies the focused cell, or the selected records with a header row, to the clipboard as Ctrl+C does.

None

A single cell is copied as text; several records as tab-separated text and as an HTML table, as an Access datasheet copies them. Group rows and the row for a new record are not copied. Raises wkeNotAvailable when the control is not in form view.

None

Declared by WklGrid.

Sub ShowExportDialog()

Shows the export dialog of the shortcut menu: Excel workbook, XML or JSON file.

None

ExportMode and ExportRows apply as for the menu entry. Raises wkeNotAvailable when AllowExport is False or the control is not in form view. The methods ExportToXlsx, ExportToXml, ExportToJson and ExportToFile export without a dialog.

None

Declared by WklGrid.

Sub ShowContextMenu()

Opens the shortcut menu of the focused record below its focused cell, as the menu key does.

None

NodeRightClick (RowRightClick in a WklGrid) does not occur, as for the menu key. Raises wkeNotAvailable when no record is focused or the control is not in form view.

None

Declared by WklGrid.

Property Get CanAdd() As Boolean

Gets whether NewRecord can start a new record now.

None

Property value: Boolean – True when AllowAdditions is set and, in a WklGrid, the grid shows the row for a new record (NewItemRow, AllowEditing, a record source the grid writes to); in a WklTreeList, when the records' own hierarchy is shown (no GroupFields) and the master allows writing. With an edit form, when AllowAdditions is set, the master allows writing and a form for new records is set (EditForm, or the entry of EditForms without kind). False outside form view.

The value is the enabled state of the shortcut menu's New record (New node here in a WklTreeList). Read-only. Reading it sets the starting point for CommandStateChange, as reading CanDelete, CanEdit and CanExport does.

Read-only.

None

Declared by WklGrid.

Property Get CanDelete() As Boolean

Gets whether DeleteSelected can delete now.

None

Property value: Boolean – True with AllowDeletions when a record is focused that is neither a group row nor the row for a new record. False outside form view.

The value is the enabled state of the shortcut menu's Delete. Read-only.

Read-only.

None

Declared by WklGrid.

Property Get CanEdit() As Boolean

Gets whether EditSelected can open a cell editor now.

None

Property value: Boolean – True with AllowEditing when the focused record has a cell that can be edited: a column that is not read-only and not a check box, a status link or a command column. With an edit form, True when a form edits the focused record (EditForm, EditForms), whatever AllowEditing says. False outside form view.

Read-only.

Read-only.

None

Declared by WklGrid.

Property Get CanExport() As Boolean

Gets whether ShowExportDialog can show the export dialog.

None

Property value: Boolean – True with AllowExport in form view.

Read-only.

Read-only.

None

Declared by WklGrid.

Property Get AutoSaveLayout() As Boolean
Property Let AutoSaveLayout(RHS As Boolean)

Gets or sets whether the user's arrangement of the columns is remembered per Windows user.

None

Property value: Boolean – True (default) to remember and restore it; False to show the design every time.

Remembered are column widths, order, hidden columns, bands, sorting, GroupFields, the column filters of a WklGrid and its DetailHeight - as the difference from the design, so a later change of the design still reaches every column the user did not touch; columns removed from the design are ignored, new ones appear as designed. Not remembered: selection, search text and expanded records or groups.

The design is what the control shows when form view first displays it: the columns saved with the form plus what Form_Load changed by code. The remembered arrangement is applied then, before the records are read; every later change - by the user or by code - is stored at once, and a change back to the design removes it. A Layout set by code before the control shows wins over the remembered arrangement.

In Access the arrangement goes into the local table USysWklLayout of the database holding the form (one row per Windows user and key, as for WklDockLayout); on Excel and Word UserForms into HKEY_CURRENT_USER\Software\The White Knight Labs\Access Framework\Layouts. A read-only front end does not break the form; the arrangement is then not remembered. The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Property Get LayoutKey() As String
Property Let LayoutKey(RHS As String)

Gets or sets the name the arrangement is remembered under.

None

Property value: String – Up to 255 characters; empty (default) for “form name.control name”.

Two lists with the same key share one arrangement. A form used as subform in several places shares its arrangement already, because the key names the form holding the control. Longer keys raise wkeInvalidValue. The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Sub Reset()

Shows the columns, sorting, grouping, filters and detail height of the design again and removes the user's remembered arrangement.

None

Before form view showed the control, nothing changes. Selection and expanded records stay.

Private Sub cmdDefaultColumns_Click()
    Me!Grid1.Object.Reset      ' also removes the user's row in USysWklLayout
End Sub

Declared by WklGrid.

Property Get ShortcutMenuBar() As String
Property Let ShortcutMenuBar(RHS As String)

Gets or sets the name of an Access shortcut menu bar whose entries the control's shortcut menu shows.

None

Property value: String – The name of a popup command bar in Application.CommandBars, up to 255 characters; empty (default) for none.

The property is the control's counterpart of the Shortcut Menu Bar (ShortcutMenuBar) of Access forms and controls, which Access does not offer for an ActiveX control. It gives the control a shortcut menu of the application without a form module: each entry runs its action (OnAction, an expression such as =MarkOrderDone() or the name of a macro) the way Office runs it from a command bar. The control has the focus during the call, so the function reaches it through Screen.ActiveControl and the record through ContextKey.

The menu shows the visible buttons and submenus of the bar with their caption (& marks the access key), ShortcutText, enabled state, the pressed state (State = msoButtonDown) as a check mark, and BeginGroup as a separator line. Combo boxes, edit boxes and pictures (FaceId, Picture) of a bar are not shown. The bar is read each time the menu opens, so a change by code - CommandBars("mnuOrders").Controls(1).Enabled = False - shows the next time. A name Access does not know adds no entries. An entry whose function does not exist shows the message of Access that the expression contains the name of a function it cannot find, as for an entry of an Access bar. An error the function does not handle shows the runtime error dialog of VBA, as for an entry of an Access bar - handle errors inside the function.

MenuPlacement sets where the entries stand next to the built-in ones. In a WklGrid, detail views show the same entries; ContextView names the view. The value is saved with the form; the bar is not part of the form. Create it in code when the application starts, as in the example: the bar is then the same in every copy of the front end, in ACCDE files and in the Access Runtime.

Read/write.

' Standard module basMenus. Run CreateOrderMenu once at start,
' for example from the AutoExec macro (RunCode CreateOrderMenu()).
Public Function CreateOrderMenu()
    On Error Resume Next
    CommandBars("mnuOrders").Delete
    On Error GoTo 0
    With CommandBars.Add("mnuOrders", 5, False, True)   ' 5 = msoBarPopup, temporary
        With .Controls.Add(1)                             ' 1 = msoControlButton
            .Caption = "Als &erledigt markieren"
            .OnAction = "=MarkOrderDone()"
        End With
        With .Controls.Add(1)
            .Caption = "&Lieferschein drucken"
            .OnAction = "=PrintDeliveryNote()"
            .BeginGroup = True
        End With
    End With
End Function
 
Public Function MarkOrderDone()
    Dim grid As Object
    Set grid = Screen.ActiveControl.Object
    If IsNull(grid.ContextKey) Then Exit Function
    CurrentDb.Execute "UPDATE tblOrders SET Done = True WHERE OrderID = " & grid.ContextKey, dbFailOnError
    grid.Requery
End Function
 
Public Function PrintDeliveryNote()
    DoCmd.OpenReport "rptDeliveryNote", acViewPreview, , "OrderID = " & Screen.ActiveControl.Object.ContextKey
End Function

In design view, set ShortcutMenuBar of the grid to mnuOrders (property sheet, Other tab, or the designer page Behavior).

Declared by WklGrid.

Property Get MenuPlacement() As WklMenuPlacement
Property Let MenuPlacement(RHS As WklMenuPlacement)

Gets or sets where the own entries stand in the shortcut menu.

None

Property value: `WklMenuPlacement` – A WklMenuPlacement value; wmpOwnAfter (default) puts them after the built-in entries.

Own entries are the entries of ShortcutMenuBar, followed by those added with AddMenuItem; a separator line divides them from the built-in entries. ContextMenu = False removes the built-in entries in every placement. Other values raise wkeInvalidValue. The value is saved with the form.

Read/write.

None

Declared by WklGrid.

Property Get ContextKey() As Variant

Gets the key of the record the shortcut menu was last opened on.

None

Property value: Variant – The key, in the type of the key field; Null when the menu was opened on a group row or has not been opened yet.

The value is set before BeforeContextMenu and kept until the menu opens again, so the actions of the entries - functions of ShortcutMenuBar, MenuItemClick - work on the record the menu belongs to, also when the menu was opened with the menu key, Shift+F10 or ShowContextMenu. In a detail view of a WklGrid it is the key of the detail record, and it is the master grid's property that holds it (ContextView names the view). Read-only.

Read-only.

None

Declared by WklGrid.

Property Get ContextColumn() As String

Gets the column the shortcut menu was last opened on.

None

Property value: String – The column name; empty when the menu was opened outside a cell.

Read-only. Set together with ContextKey.

Read-only.

None

Declared by WklGrid.

Property Get ContextView() As String

Gets the detail view the shortcut menu was last opened in.

None

Property value: String – The name of the detail view, as DetailGrid takes it; empty when the menu was opened in the control itself.

Only a WklGrid has detail views; in a WklTreeList and a WklGantt the value is always empty. Read-only.

Read-only.

Public Function ShowContext()
    With Screen.ActiveControl.Object
        If .ContextView = "" Then
            MsgBox "Auftrag " & .ContextKey
        Else
            MsgBox "Position " & .ContextKey & " in " & .ContextView
        End If
    End With
End Function

Declared by WklGrid.

Property Get MenuItemEnabled(ByVal Tag As String) As Boolean
Property Let MenuItemEnabled(ByVal Tag As String, RHS As Boolean)

Gets or sets whether a custom menu item can be chosen.

Property value: Boolean – True (default) or False; a disabled item is shown grayed.

An unknown tag raises wkeInvalidValue. AddMenuItem with an existing tag replaces the item and resets this and the other MenuItem... properties. Set it in BeforeContextMenu to follow the record the menu opens on.

Read/write.

None

Declared by WklGrid.

Property Get MenuItemChecked(ByVal Tag As String) As Boolean
Property Let MenuItemChecked(ByVal Tag As String, RHS As Boolean)

Gets or sets whether a custom menu item shows a check mark.

Property value: Boolean – True or False (default). A checked item shows the check mark instead of its glyph.

An unknown tag raises wkeInvalidValue.

Read/write.

None

Declared by WklGrid.

Property Get MenuItemGlyph(ByVal Tag As String) As String
Property Let MenuItemGlyph(ByVal Tag As String, RHS As String)

Gets or sets the glyph of a custom menu item.

Property value: String – A glyph code as for WklButton.Glyph; empty (default) for none.

An unknown tag raises wkeInvalidValue; an unknown glyph code shows no glyph.

Read/write.

None

Declared by WklGrid.

Property Get MenuItemShortcut(ByVal Tag As String) As String
Property Let MenuItemShortcut(ByVal Tag As String, RHS As String)

Gets or sets the key combination shown at a custom menu item.

Property value: String – The text shown at the right edge, for example Ctrl+P; empty (default) for none.

Display only: the control does not bind the keys. An unknown tag raises wkeInvalidValue.

Read/write.

None

Declared by WklGrid.

Property Get MenuItemParent(ByVal Tag As String) As String
Property Let MenuItemParent(ByVal Tag As String, RHS As String)

Gets or sets the custom menu item whose submenu holds an item.

Property value: String – The tag of the parent item; empty (default) for the menu itself.

An item with child items opens its submenu and raises no MenuItemClick itself. A parent tag that names no item, or a chain of parents that leads back to the item, puts the item into the menu itself, so a slip in code never hides an item. An unknown Tag raises wkeInvalidValue.

Read/write.

Private Sub Form_Load()
    With Me!Grid1.Object
        .AddMenuItem "&Status", "status"
        .AddMenuItem "Offen", "status.open"
        .AddMenuItem "Erledigt", "status.done"
        .MenuItemParent("status.open") = "status"
        .MenuItemParent("status.done") = "status"
        .AddMenuItem "&Drucken", "print", True
        .MenuItemShortcut("print") = "Ctrl+P"
    End With
End Sub

Declared by WklGrid.

Property Get EditForm() As String
Property Let EditForm(RHS As String)

Gets or sets the name of the Access form that edits a record of the list (the edit form).

None

Property value: String – The name of a form of the database, up to 255 characters; empty (default) for editing in the cells.

The edit form is an ordinary bound Access form, designed in Access without code; its record source is the table or query of the records it edits. The control draws no fields of its own: it opens the form, limits it to the chosen record and reads its records again when the form is done. Validation rules, required fields and errors of the data source are shown by the form, as in every Access form.

The form is used only while EditFormMode is not wfmNone. Then the commands that edit a record open the form instead of a cell editor:

Group rows are no records and open nothing. CanEdit is True on a record that has a form, CanAdd when AllowAdditions is True and a form for new records is set. The form opens after the input has been handled, so the event procedures of the double click (NodeDblClick, RowDblClick, CellDblClick) run first.

The form is limited to the record with a WhereCondition over its key fields: [ID] = 17, or one comparison per field of a composite key joined with And. The fields are those of EditKeyFields, or the list's KeyField when it is empty. A record whose key has a Null part cannot be opened.

New record of a linked list (LinkChildFields with a master record): the controls of the form bound to the link fields get the master values as DefaultValue, as Access fills the link fields of a new record in a subform. The form needs a control for each link field; it may be invisible.

After a dialog is closed the list reads its records again (as Shift+F9) and keeps the selection; AfterEditForm follows. BeforeEditForm can choose another form or cancel.

The value is saved with the form. Outside an Access form (UserForm in Excel or Word) no form is opened.

Read/write.

' In design view: EditForm = frmCustomer, EditFormMode = wfmDialog.
' The same by code, for example in Form_Load:
Private Sub Form_Load()
    With Me!Grid1.Object
        .EditForm = "frmCustomer"
        .EditFormMode = wfmDialog
    End With
End Sub

Declared by WklGrid.

Property Get EditKeyFields() As String
Property Let EditKeyFields(RHS As String)

Gets or sets the fields of the edit form's record source that hold the parts of the list's key.

None

Property value: String – Field names separated by semicolons, in the order of KeyField; empty (default) when the form names them as the list does.

Needed only when the names differ, typically in a tree over a UNION query whose common key column comes from a differently named field in each table. The number of fields must match the number of parts of KeyField; otherwise opening the form reports wkeInvalidValue through DataError. Applies to EditForm; a rule in EditForms names its own fields.

Read/write.

' The list reads tblOrderLine as Key = OrderNo;LineNo, the form names them OrderID;Position.
Me!Grid1.Object.EditKeyFields = "OrderID;Position"

Declared by WklGrid.

Property Get EditFormMode() As WklEditFormMode
Property Let EditFormMode(RHS As WklEditFormMode)

Gets or sets where the edit form is shown.

None

Property value: `WklEditFormMode` – A WklEditFormMode value; wfmNone (default) for editing in the cells.

wfmDialog opens the form with acDialog: the user works in it, the list waits and reads again when it is closed. wfmWindow opens an ordinary form window (a tab or an overlapping window, as the database option Document Window Options sets); the list goes on at once and reads again when the window is closed. wfmDocked shows the form in the subform control EditFormControl of the list's own form, for example in a pane of a WklDockLayout, and follows the selection. wfmAuto docks while the list keeps enough width beside the subform control and opens a dialog otherwise. wfmOverlay uses the same subform control, but places it over the right part of the list (EditFormWidth) only while a record is edited, and hides it when the focus comes back to the list; it does not follow the selection.

The value is saved with the form.

Read/write.

Me!Grid1.Object.EditFormMode = wfmWindow

Declared by WklGrid.

Property Get EditKindField() As String
Property Let EditKindField(RHS As String)

Gets or sets the field of the record source that holds the kind of a record, for the edit forms per kind in EditForms.

None

Property value: String – A field name; empty (default) when every record has the same kind.

A tree that shows several tables through a UNION query typically has a column such as Kind with values like Customer or Order. The list compares the value of that field with the kinds of EditForms: text without regard to case, numbers by value. The field may be any field of the record source: the list reads it with every record without showing it. Changing the property reads the records again.

Read/write.

With Me!Tree1.Object
    .EditKindField = "Kind"
    .EditForms = "{""forms"":[{""kind"":""Customer"",""form"":""frmCustomer"",""keys"":""CustomerID""}," & _
                 "{""kind"":""Order"",""form"":""frmOrder"",""keys"":""OrderID""}]}"
    .EditFormMode = wfmDialog
End With

Declared by WklGrid.

Property Get EditForms() As String
Property Let EditForms(RHS As String)

Gets or sets the edit forms per kind of record, as JSON text.

None

Property value: String – {"format":"WklEditForms","version":1,"forms":[...]} with one object per kind: kind (the value of EditKindField; left out for the form of every other record), form (the form name), keys (its key fields, as EditKeyFields; left out when named as in the list or in sourceKeys), sourceKeys (fields of the list's record source whose values make up the form's key, in the order of keys; left out when the list's key is the form's key), deleteFrom (the table or query the records of this kind are deleted from; left out for the form's record source) and children (the kinds that can be added below a record of this kind: objects with kind, optionally form, and link as FormField=ParentField pairs separated by semicolons). Empty (default) for none.

Which form opens: the entry whose kind equals the record's kind, else the entry without kind, else EditForm.

A tree over a UNION query. Its KeyField must be unique across all tables, for example 'r5' for rule 5 and 'i5:resource' for an item of it, while each form needs the key of its table. sourceKeys names the columns of the union that carry that key ("sourceKeys":"RuleId;Item"); they need not be shown. Deleting a record of such a kind (DeleteSelected, Ctrl+-, the Delete key) deletes it in deleteFrom - or the form's RecordSource - over keys, with BeforeDelete, the question and AfterDelete as usual; a node with children deletes them first, each by the entry of its own kind. In a list with edit forms only records whose kind has a form can be deleted (CanDelete).

New records. NewRecord and Ctrl++ open the first kind of children of the focused record's entry, below that record: the controls bound to the link fields get the parent's values as default value, and the parent is expanded. Without such a kind - nothing focused, or a kind without children - they open the entry without kind, else EditForm, on the top level. NewRecordOfKind opens a given kind: below the focused record when its entry lists it, else on the top level. A child entry without form takes the form of the entry of its kind.

Setting an invalid text raises wkeInvalidValue with what is wrong: no valid JSON, an entry without form, a kind that occurs twice, keys and sourceKeys of different length, a child without kind, a malformed link, or a child whose kind has no form. Unknown properties are ignored, so a form saved by a newer version still opens. The value is saved with the form.

Read/write.

Debug.Print Me!Tree1.Object.EditForms
' {"format":"WklEditForms","version":1,"forms":[{"kind":"Customer","form":"frmCustomer","keys":"CustomerID"}]}
 
' A tree of rules and their items over a UNION query with the columns Key, Kind, Nr and Item:
With Me!Tree1.Object
    .KeyField = "Key"
    .EditKindField = "Kind"
    .EditForms = "{""forms"":[" & _
        "{""kind"":""Rule"",""form"":""frmRule"",""sourceKeys"":""Nr"",""keys"":""Id""," & _
        """children"":[{""kind"":""Item"",""link"":""RuleId=Nr""}]}," & _
        "{""kind"":""Item"",""form"":""frmRuleItem"",""sourceKeys"":""Nr;Item"",""keys"":""RuleId;Item""," & _
        """deleteFrom"":""tblRuleItem""}]}"
    .EditFormControl = "sfEdit"
    .EditFormMode = wfmDocked
End With

Declared by WklGrid.

Property Get EditFormControl() As String
Property Let EditFormControl(RHS As String)

Gets or sets the subform control of the list's own form that shows the edit form when EditFormMode is wfmDocked, wfmAuto or wfmOverlay.

None

Property value: String – The name of a subform control (ControlType acSubform) of the same form; empty (default) for none.

The list sets the control's SourceObject to the edit form and filters it on the selected record whenever the selection changes. Access saves the record of the subform when the focus goes back to the list, as for any subform; the list then reads that record again. Without a usable control the docked and the automatic mode open a dialog, and so does the overlay without a control of that name. For wfmOverlay the control must come after the list in the form's order (Bring to Front) to lie above it; the list moves and sizes it over its own right part, makes it visible and hides it again afterwards. The value is saved with the form.

Read/write.

With Me!Grid1.Object
    .EditForm = "frmCustomer"
    .EditFormControl = "sfEdit"
    .EditFormMode = wfmDocked
End With

Declared by WklGrid.

Property Get EditFormResult() As WklEditFormResult

Gets what became of the record of the edit form that was done last.

None

Property value: `WklEditFormResult` – A WklEditFormResult value; wfrUnchanged before any edit form.

The list reads the record from its record source before the form opens and again after it is done, and compares all fields (text with regard to case): a form that saved without a change reports wfrUnchanged. For a new record it compares the keys the list holds before and after, without a query of its own; in server mode, where the list holds only part of its records, it compares the largest key (a single numeric key field). A new record the list does not show (outside its filter) is therefore reported as wfrUnchanged unless the list is in server mode. Without a record source the list can read again (a Recordset, a file) the result is wfrUnknown. A UNION query compares the columns of the union, not every field of the form's table. The value is set before AfterEditForm and stays until the next edit form is done; a detail view's result is read from the master grid.

Read-only.

Private Sub Grid1_AfterEditForm(ByVal Key As Variant, ByVal NewRecord As Boolean)
    Select Case Me!Grid1.Object.EditFormResult
        Case wfrAdded: Me!lblStatus.Caption = "Neu: " & Nz(Me!Grid1.Object.EditFormNewKey, "?")
        Case wfrDeleted: Me!lblStatus.Caption = "Deleted"
    End Select
End Sub

Declared by WklGrid.

Property Get EditFormNewKey() As Variant

Gets the key of the record the edit form that was done last has added.

None

Property value: Variant – The key in the list (as SelectedKey); Null when no record was added, or when several were added at the same time and the form's is not certain.

The list selects that record, expands its parents and scrolls it into view. A record the list does not show (another master record, a filter) keeps its key here, but nothing is selected.

Read-only.

If Not IsNull(Me!Tree1.Object.EditFormNewKey) Then Debug.Print Me!Tree1.Object.NodeText(Me!Tree1.Object.EditFormNewKey)

Declared by WklGrid.

Property Get EditFormWidth() As Long
Property Let EditFormWidth(RHS As Long)

Gets or sets the width of the overlaying edit form (wfmOverlay).

None

Property value: Long – A width in twips; 0 (default) for the width of the subform control EditFormControl from design view.

The overlay never gets wider than the list. A negative value raises wkeInvalidValue. The value is saved with the form.

Read/write.

Me!Grid1.Object.EditFormWidth = 6 * 567 ' 6 cm

Declared by WklGrid.

Sub NewRecordOfKind(ByVal Kind As String)

Starts a new record of a kind in its edit form.

Below the focused record when the entry of its kind lists Kind in children (with the link values of the focused record as default values), else on the top level with the entry of Kind. Raises wkeNotAvailable when CanAddKind(Kind) is False. For one button per kind, as “Neue Regel” and “Neues Merkmal”.

Private Sub cmdNewItem_Click()
    If Me!Tree1.Object.CanAddKind("Item") Then Me!Tree1.Object.NewRecordOfKind "Item"
End Sub

Declared by WklGrid.

Property Get CanAddKind(ByVal Kind As String) As Boolean

Gets whether NewRecordOfKind can start a record of a kind now.

Property value: Boolean – True when AllowAdditions is set, the list edits in forms and a form takes this kind below the focused record or on the top level.

Read it in CommandStateChange and after a change of the selection to enable your own buttons.

Read-only.

Me!cmdNewItem.Enabled = Me!Tree1.Object.CanAddKind("Item")

Declared by WklGrid.

Property Get ValidateMode() As WklValidateMode
Property Let ValidateMode(RHS As WklValidateMode)

Gets or sets when the rules of the columns are checked.

None

Property value: `WklValidateMode` – A WklValidateMode value; wvmOnLeave (default).

A value that breaks a rule is never written, as Access does not save a value that breaks a field's validation rule: the cell editor stays open, the cell shows a red frame with the error symbol and the message as tooltip, and the error bar below the records counts the errors; there is no message box. Escape discards the input. wvmOnLeave checks when the editor is left (Enter, Tab, a click elsewhere), wvmImmediate marks the input while the user types and refuses it when the editor is left, wvmOnSave checks the row for a new record only when it is saved. Existing records are saved cell by cell, so their cells are checked when they are left in every mode. The value is saved with the form.

Read/write.

Me!Grid1.Object.ValidateMode = wvmImmediate

Declared by WklGrid.

Sub SetError(ByVal Key As Variant, ByVal Column As String, ByVal Text As String)

Shows an error at a cell or at a record.

For rules the columns cannot express - over several fields, or the refusal of a data service that names no field: the cell shows a red frame, the record without column a red bar at its left edge; the message is the tooltip and the text of the error bar. A write the data source refused is kept with the source's text by the list itself. An error of the application stays until it is removed with an empty Text or ClearErrors. An unknown column raises wkeKeyNotFound.

Private Sub Grid1_ValidateCell(ByVal Key As Variant, ByVal Column As String, NewValue As Variant, Cancel As Integer)
    If Column = "Login" And IsNull(NewValue) And Me!Grid1.Object.CellValue(Key, "Kind") = "Login" Then
        Cancel = True
        Me!Grid1.Object.SetError Key, "Login", "A sign-in needs a login name."
    End If
End Sub

Declared by WklGrid.

Sub ClearErrors(Optional ByVal Key As Variant)

Removes the errors of a record, or every error.

Removes the errors of the rules as well as those of SetError; a rule that is still broken is reported again when its cell is checked next.

Me!Grid1.Object.ClearErrors

Declared by WklGrid.

Property Get ErrorCount() As Long

Gets the number of errors shown.

None

Property value: Long – The errors of all cells and records; 0 without errors.

Read it in ValidationChange to enable your own buttons, for example a Save button that only makes sense without errors.

Read-only.

Me!cmdWeiter.Enabled = (Me!Grid1.Object.ErrorCount = 0)

Declared by WklGrid.

Property Get ErrorText(ByVal Key As Variant, ByVal Column As String) As String

Gets the message of a cell or of a record.

Property value: String – The message; empty when the cell or record has no error.

An unknown column raises wkeKeyNotFound.

Read-only.

Debug.Print Me!Grid1.Object.ErrorText(Me!Grid1.Object.SelectedKey, "Netz")

Declared by WklGrid.

Sub GoToFirstError()

Selects the record of the first error and shows its cell.

None

The first error is the one found first. The list expands the record's parents and scrolls to it, as the button Go to the first error of the error bar does. Raises wkeNotAvailable without errors and outside form view.

If Me!Grid1.Object.ErrorCount > 0 Then Me!Grid1.Object.GoToFirstError

Declared by WklGrid.

Property Get ErrorSummary() As String

Gets the summary of the errors as the error bar shows it.

None

Property value: String – “1 Fehler”, “2 Fehler” …; empty without errors.

Read-only.

Me!lblFehler.Caption = Me!Grid1.Object.ErrorSummary

Declared by WklGrid.

Property Get ViewMode() As WklGridViewMode
Property Let ViewMode(RHS As WklGridViewMode)

Gets or sets whether the grid shows its records as a datasheet or as a list of entries.

None

Property value: `WklGridViewMode` – A WklGridViewMode; wgvTable (default) or wgvList. Other values raise wkeInvalidValue.

The list view draws each record after ItemTemplate - an optional glyph, the title, one or two detail lines, a status pill, a badge and up to two buttons - without column headers, filter row and footer, as the two-line lists of Outlook and Teams. It is a view of the same grid: RecordSource, server mode (DataMode), sorting and filtering by code, selection and MultiSelect, LinkMasterFields of a subform beside it, detail views, BeforeDelete and DeleteSelected work as in the table. Groups (GroupFields) show as group rows between the entries. There are no cells to edit; Enter raises ItemClick, and with an edit form (EditForm) a double click or F2 opens it.

Without ItemTemplate the list stays empty. The fields of the template are read with the records, also when no column shows them.

Sorting by code works also through a column the list does not show. The list draws no columns, so add the column by code: Set c = .Columns.Add("No"), c.Visible = False, c.SortDirection = wsdAscending.

Events of the list view:

Keyboard: Up, Down, Page Up, Page Down, Home and End move between the entries; Enter raises ItemClick; Tab moves from the entry into its buttons one by one and on past the last one to the next control, Shift+Tab back; Enter or Space on a button raises ItemCommand; Esc leaves the buttons. Screen readers hear the grid as a list whose entries are named by their whole statement (“Jane Doe, Windows · 10.0.4.21 · 18:02, running”); the link and the buttons of an entry are its children and can be invoked.

Read/write.

' The open sessions as a two-line list with an "End" button.
With Me!grdSessions.Object
    .RecordSource = "qryOpenSessions"
    .KeyField = "ID"
    .ItemTemplate = "{""title"":{""field"":""Person"",""link"":true}," & _
        """lines"":[[{""field"":""Method""},{""field"":""Origin""},{""field"":""Expires"",""format"":""hh:nn""}]]," & _
        """status"":{""field"":""State"",""kindField"":""StateKind""}," & _
        """commands"":[{""key"":""end"",""caption"":""End"",""visibleField"":""CanEnd""}]}"
    .EmptyText = "No open sessions."
    .ViewMode = wgvList
End With

Declared by WklGrid.

Property Get ItemTemplate() As String
Property Let ItemTemplate(RHS As String)

Gets or sets the template of the entries of the list view, as JSON text.

None

Property value: String – A JSON object; an empty string for none. An invalid text raises wkeInvalidValue with the reason, and the template stays as it was.

The parts of the object:

• title (required): {"field": "Person", "link": true} - the field of the first line, in Semibold; with link it is drawn as a link and a click raises LinkClick.
• lines: up to two detail lines, each a list of fields, smaller and in the gray text color. A field is a name or {"field": "Expires", "format": "hh:nn"} with an Access format (“Short Date”, “Standard”, “0 %”, and so on). The values are joined by separator (default “ · ”); a Null or empty value is left out together with its separator.
• status: {"field": "Zustand", "kindField": "ZustandArt"} - a pill at the right with the formatted value; its color comes from the kind field (0-4 or neutral, ok, warn, bad, info), else from the value itself.
• badge: {"field": "Anzahl"} for a count or {"dot": true} for a dot, optional "kind"; the rules of WklBadge apply (zero and Null hide it, “99+”).
• glyph: {"code": "E77B"} or {"field": "Symbol"} - a symbol of Segoe Fluent Icons at the left.
• commands: up to two buttons at the right, {"key": "end", "caption": "End", "glyph": "E711", "visibleField": "CanEnd", "enabledField": "Aktiv"}; a button without visibleField always shows, one whose field is False, 0 or Null is left out; a disabled button is gray. A click, Enter or Space on it raises ItemCommand.

Names not listed here are refused, so a typing error shows when the template is set. The designer page List edits the same text.

Read/write.

None

Declared by WklGrid.

Property Get ItemHeight() As Long
Property Let ItemHeight(RHS As Long)

Gets or sets the height of an entry of the list view.

None

Property value: Long – Twips from 0 to 14400; 0 (default) makes each entry as high as its title and detail lines. Other values raise wkeInvalidValue.

An entry lower than its text leaves out detail lines from the bottom; the title always stays. Text that does not fit in the width ends with “…”, and its tooltip shows it whole.

Read/write.

None

Declared by WklGrid.

Property Get ItemSeparator() As Boolean
Property Let ItemSeparator(RHS As Boolean)

Gets or sets whether a thin line separates the entries of the list view.

None

Property value: Boolean – True (default): a line between the entries; False: none.

Read/write.

None

Declared by WklGrid.

Property Get EmptyText() As String
Property Let EmptyText(RHS As String)

Gets or sets the text the list view shows without records.

None

Property value: String – For example No open sessions.; empty (default) for none.

Read/write.

None

Declared by WklGrid.

Sub AddFilterChip(ByVal Name As String, ByVal Caption As String, ByVal Condition As String)

Sets a named filter that the user can remove in the filter bar, or replaces the filter of the same name.

A filter chip says why a list shows fewer records, and lets the user take that reason away with one click - the pattern of the filter tokens in Outlook and the Windows file search. The grid applies (Filter) AND (chip 1) AND (chip 2) ..., each part in brackets, so an Or inside one part cannot widen another; the condition works in memory and in server mode alike. Chips keep the order in which they were added; a chip with an existing name keeps its place.

Chips need a record source (RecordSource) to run the condition on: a grid bound to a Recordset only - on an Excel or Word UserForm, for example - refuses AddFilterChip with wkeInvalidValue. Chips are not saved with the form and not in the remembered arrangement (AutoSaveLayout): set them when the form opens the list for a purpose, for example in Form_Load or after OpenForm with OpenArgs. The filter bar (ShowFilterBar) shows them; screen readers hear each chip as the button “Filter entfernen: <Caption>”.

Private Sub Form_Load()
    With Me!Grid1.Object
        .AddFilterChip "expiring", "expires in 14 days", "Expires Between Date() And Date() + 14"
        .AddFilterChip "locked", "gesperrt", "Status = 'gesperrt'"
    End With
End Sub
 
Private Sub Grid1_FilterChipRemoved(ByVal Name As String, ByVal ByUser As Boolean)
    If ByUser Then Me!lblHinweis.Caption = "Filter entfernt: " & Name
End Sub

Declared by WklGrid.

Sub RemoveFilterChip(ByVal Name As String)

Removes the filter chip with the name.

Raises FilterChipRemoved with ByUser = False. The records are read again with the remaining condition.

None

Declared by WklGrid.

Sub ClearFilterChips()

Removes every filter chip, without events.

None

Filter and the column filters stay. Without chips the call does nothing.

None

Declared by WklGrid.

Property Get FilterChipCount() As Long

Gets the number of filter chips.

None

Property value: Long – 0 without chips.

Read-only.

None

Declared by WklGrid.

Property Get FilterChipName(ByVal Index As Long) As String

Gets the name of the filter chip at a position.

Property value: String – The name as given to AddFilterChip.

Read-only.

Dim i As Long
For i = 0 To Me!Grid1.Object.FilterChipCount - 1
    Debug.Print Me!Grid1.Object.FilterChipName(i)
Next

Declared by WklGrid.

Property Get ColorScheme() As WklColorScheme
Property Let ColorScheme(RHS As WklColorScheme)

Gets or sets the color scheme the control draws in.

None

Property value: `WklColorScheme` – wcoAutomatic (default), wcoLight, wcoDark or wcoHighContrast.

wcoAutomatic follows, in this order, Windows high contrast mode, then the color set of the database design (Access: Design › Themes or Colors; the Riviera theme of the release package is one), and draws light when the database has no design. Office's own design setting (File › Account) and the Windows app mode are deliberately not followed: a database looks the same on every PC. In high contrast mode the control always uses the system colors, whatever the setting, as Office does. The scheme colors the records and the list view: background and text while BackColor and ForeColor keep their defaults (a color you set wins), headers, lines, selection, focus, group rows and marks. Every text keeps a contrast of at least 4.5:1, also with a database color set whose own colors would fall below it. The control reads the database design when it is first drawn and again with Requery, never while Access draws the form; a design chosen while the form is open shows after Requery or when the form is opened again. The value is saved with the form.

Read/write.

Me!List1.Object.ColorScheme = wcoDark

Declared by WklGrid.

Property Get EditOnClick() As Boolean
Property Let EditOnClick(RHS As Boolean)

Gets or sets whether a single click into an editable cell opens its editor.

None

Property value: Boolean – True or False; default True (as in an Access datasheet).

With True a click into a cell edits it as in an Access datasheet: the editor opens and the caret lands where the click was (after the last character when the click was right of the text). A drop-down list does not open on the click; its arrow or Alt+Down opens it. A double click selects the word under the caret and raises DblClick and CellDblClick as without the setting. With False a click only selects the record; F2, Enter, typing or a double click open the editor. The click edits only where editing is possible at all: AllowEditing is True, the column is not read-only and the source can be written. A check box, a toggle switch, a command button, a status link and an edit form (EditForm) keep their own click. Ctrl+click and Shift+click still extend the selection. Detail views take the value of the master grid. The value is saved with the form; a form saved before version 1.47 takes the default.

Read/write.

None

Declared by WklGrid.

Property Get AllowFileDrop() As Boolean
Property Let AllowFileDrop(RHS As Boolean)

Gets or sets whether files and folders dragged from Windows Explorer can be dropped on the grid.

None

Property value: Boolean – True to accept files; False (default) to refuse them.

With True the grid reports a drag from Windows Explorer through FileDragOver and a drop through FileDrop; it writes nothing itself. The target is the record under the mouse: the upper quarter of the row means before it, the lower quarter after it and the middle on it, shown by a line or a frame in the accent color. A group row, the row for a new record and the area below the last record have no target (TargetKey Null). Other drags than files are refused, and files are taken only in form view. The grid's own moving of rows and columns is not affected. The value is saved with the form; a form saved before version 1.57 takes the default.

Read/write.

None

Declared by WklGrid.

Event RowSelect(ByVal Key As Variant)

Occurs when the user selects a record.

In a form module: Private Sub <ControlName>_RowSelect(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event GroupExpand(ByVal Key As Variant)

Occurs after a group is expanded.

In a form module: Private Sub <ControlName>_GroupExpand(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event GroupCollapse(ByVal Key As Variant)

Occurs after a group is collapsed.

In a form module: Private Sub <ControlName>_GroupCollapse(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event RowDblClick(ByVal Key As Variant)

Occurs when the user double-clicks a record.

In a form module: Private Sub <ControlName>_RowDblClick(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event BeforeRowSelect(ByVal Key As Variant, ByRef Cancel As Integer)

Occurs before a record is selected.

In a form module: Private Sub <ControlName>_BeforeRowSelect(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event BeforeGroupExpand(ByVal Key As Variant, ByRef Cancel As Integer)

Occurs before a group is expanded.

In a form module: Private Sub <ControlName>_BeforeGroupExpand(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event BeforeGroupCollapse(ByVal Key As Variant, ByRef Cancel As Integer)

Occurs before a group is collapsed.

In a form module: Private Sub <ControlName>_BeforeGroupCollapse(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event RowClick(ByVal Key As Variant)

Occurs when the user clicks a record with the left mouse button.

In a form module: Private Sub <ControlName>_RowClick(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event RowRightClick(ByVal Key As Variant, ByVal X As Single, ByVal Y As Single, ByRef Cancel As Integer)

Occurs when the user right-clicks a record.

In a form module: Private Sub <ControlName>_RowRightClick(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event AfterRequery()

Occurs after the records were loaded again.

None

In a form module: Private Sub <ControlName>_AfterRequery(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event DataError(ByVal DataErr As Long, ByVal Description As String, ByRef Response As Integer)

Occurs when loading or saving fails.

Also raised when a list reads records while it draws, scrolls or follows the keyboard - WklGrid in server mode reads page by page: once per failure, after the current message; the window then shows the text instead of records and reads nothing more until Requery. Code that needs the records receives the error as a run-time error.

In a form module: Private Sub <ControlName>_DataError(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event KeyDown(ByRef KeyCode As Integer, ByVal Shift As Integer)

Occurs when a key is pressed while the control has the focus.

In a form module: Private Sub <ControlName>_KeyDown(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event KeyPress(ByRef KeyAscii As Integer)

Occurs when a character is typed.

In a form module: Private Sub <ControlName>_KeyPress(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event KeyUp(ByRef KeyCode As Integer, ByVal Shift As Integer)

Occurs when a key is released.

In a form module: Private Sub <ControlName>_KeyUp(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Occurs when a mouse button is pressed over the control.

In a form module: Private Sub <ControlName>_MouseDown(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Occurs when a mouse button is released over the control.

In a form module: Private Sub <ControlName>_MouseUp(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Occurs when the mouse moves over the control.

In a form module: Private Sub <ControlName>_MouseMove(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event MouseWheel(ByVal Page As Boolean, ByVal Count As Long)

Occurs when the mouse wheel turns over the control.

In a form module: Private Sub <ControlName>_MouseWheel(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event SelectionChange()

Occurs when the user changes the multiple selection (SelectedKeys).

None

In a form module: Private Sub <ControlName>_SelectionChange(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event BeforeDelete(ByVal Key As Variant, ByVal Count As Long, ByRef Cancel As Integer, ByRef Confirm As Boolean)

Occurs before the user deletes a record.

With several records selected (MultiSelect), the event occurs once per record. A record whose Cancel is set is left out; the others are deleted. The confirmation is one message for all records (“Do you want to delete 3 records?”); it is shown unless every call set Confirm to False. All records are deleted in one transaction: when the data source refuses one of them, or BeforeUpdate cancels, nothing is deleted, the records are read again, and DataError reports a refusal. AfterDelete occurs for each record only after the whole deletion succeeded.

In a form module: Private Sub <ControlName>_BeforeDelete(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event AfterDelete(ByVal Key As Variant)

Occurs after a record was deleted.

In a form module: Private Sub <ControlName>_AfterDelete(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event BeforeUpdate(ByVal Key As Variant, ByVal Action As WklUpdateAction, ByRef Cancel As Integer)

Occurs before each write into the record source caused by an action of the user.

One user action can write several records (for example a copied subtree); all writes of an action share one transaction.

In a form module: Private Sub <ControlName>_BeforeUpdate(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event AfterUpdate(ByVal Key As Variant, ByVal Action As WklUpdateAction)

Occurs after each write into the record source caused by an action of the user.

In a form module: Private Sub <ControlName>_AfterUpdate(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event RowFormat(ByVal Key As Variant, ByRef ForeColor As Long, ByRef BackColor As Long, ByRef Bold As Boolean, ByRef Italic As Boolean)

Occurs before a record is shown in form view, to format it.

Raised only with FormatEvents = True, for every visible record on every redraw. Keep the procedure short and avoid queries in it.

In a form module: Private Sub <ControlName>_RowFormat(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event MenuItemClick(ByVal Tag As String, ByVal Key As Variant)

Occurs when the user clicks a custom menu item.

Detail views show the custom items of their master grid; a click there raises the event of the master grid with the key of the detail record, and ContextView names the detail view.

In a form module: Private Sub <ControlName>_MenuItemClick(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event CellClick(ByVal Key As Variant, ByVal Column As String)

Occurs when the user clicks a cell.

In a form module: Private Sub <ControlName>_CellClick(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event CellDblClick(ByVal Key As Variant, ByVal Column As String)

Occurs when the user double-clicks a cell.

In a form module: Private Sub <ControlName>_CellDblClick(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event BeforeCellEdit(ByVal Key As Variant, ByVal Column As String, ByRef Cancel As Integer)

Occurs before the editor of a cell opens.

In a form module: Private Sub <ControlName>_BeforeCellEdit(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event ValidateCell(ByVal Key As Variant, ByVal Column As String, ByRef NewValue As Variant, ByRef Cancel As Integer)

Occurs after the user entered a value, before it is saved.

In a form module: Private Sub <ControlName>_ValidateCell(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event AfterCellUpdate(ByVal Key As Variant, ByVal Column As String)

Occurs after a cell value was saved.

Raised only when the value changed: a cell opened and left with the value it had (also a double click and leaving it, or the same value typed again) is not written, as Access writes only changed fields. Null and an empty value of the source count as the same, numbers are compared by their value, text exactly (a change of case is a change).

In a form module: Private Sub <ControlName>_AfterCellUpdate(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event ColumnClick(ByVal Column As String, ByRef Cancel As Integer)

Occurs when the user clicks a column header, before the list is sorted by it.

In a form module: Private Sub <ControlName>_ColumnClick(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event ColumnResize(ByVal Column As String, ByVal Width As Long)

Occurs after the user changed the width of a column.

In a form module: Private Sub <ControlName>_ColumnResize(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event ColumnMove(ByVal Column As String, ByVal VisibleIndex As Long)

Occurs after the user moved a column.

In a form module: Private Sub <ControlName>_ColumnMove(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event CellFormat(ByVal Key As Variant, ByVal Column As String, ByRef ForeColor As Long, ByRef BackColor As Long, ByRef Bold As Boolean, ByRef Italic As Boolean)

Occurs before a cell is shown, to format it.

Raised only with FormatEvents = True, for every visible cell on every redraw. Format rules (FormatRules) need no event.

In a form module: Private Sub <ControlName>_CellFormat(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event FocusedCellChange(ByVal Key As Variant, ByVal Column As String)

Occurs when the focused cell changes.

In a form module: Private Sub <ControlName>_FocusedCellChange(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event BeforeMasterChange(ByVal Values As Variant, ByRef Cancel As Integer)

Occurs before the grid changes to the records of another master record.

Raised for changes of the form's record and of MasterRecordset, not when MasterValues is set by code.

In a form module: Private Sub <ControlName>_BeforeMasterChange(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event AfterMasterChange(ByVal Values As Variant)

Occurs after the grid was loaded with the records of another master record.

In a form module: Private Sub <ControlName>_AfterMasterChange(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event BeforeInsert(ByRef Cancel As Integer)

Occurs when the user types the first value into the row for a new record.

In a form module: Private Sub <ControlName>_BeforeInsert(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event AfterInsert(ByVal Key As Variant)

Occurs after a new record was saved.

In a form module: Private Sub <ControlName>_AfterInsert(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklGrid.

Event BeforeColumnFilter(ByVal Column As String, ByVal Text As String, ByRef Cancel As Integer)

Occurs when the user changes the column filter, before the records are filtered.