WKL Forms Object Library: controls for Access forms

ActiveX controls for Microsoft Access forms, built on .NET 10: trees, datasheets, a Gantt chart, layout, navigation, buttons, status displays, validation, and notifications. They work as if they were part of Access: early binding with IntelliSense, settings in the property sheet and in a designer window, events in the form module, fixed error numbers, and full support for ACCDB, ACCDE, and the Access Runtime. This documentation is for Access and VBA developers who put the controls on forms, and for administrators who install them on users' PCs. Current release: 2.0.1. For the changes in each release, see CHANGELOG.md in the release package. Up to release 1.68.2, the library was called WKL Access Components.

The controls Access offers in design view (text box, combo box, list box, subform, tab control, button, and so on) handle classic data entry well. What they lack is what users expect from business software today: a tree bound to a table, a datasheet that groups, filters, and totals like a modern grid, a Gantt chart, a navigation bar, docked areas that follow the window size, status chips, and toast notifications. Access developers fill that gap with workarounds such as nested subforms, continuous forms with conditional formatting, or the unbound Microsoft TreeView from the Common Controls, filled by VBA. Every application pays for these workarounds in code, and they still look and behave differently from the rest of Office.

Component suites such as DevExpress WinForms or Telerik UI for WinForms solve this problem for .NET applications with Windows Forms controls. An Access form, however, hosts only ActiveX controls, so those suites can't be used there. The WKL Forms Object Library brings comparable capabilities to Access as ActiveX controls that follow Access conventions: RecordSource, LinkMasterFields, Requery, the property sheet, events in the form module, ACCDE, and the runtime.

What the components don't do: they don't replace the Access controls for simple input fields, they don't work as lists in Access reports (a list shows a notice there instead), and they aren't Windows Forms controls for .NET applications.

• How to: Install the released package: prerequisites, bitness, and registration
• How to: Move an application to release 2.0: for applications built against release 1.x
• How to: Open the designer of a control
• Build an administration application: several controls working together in one application, with a shell for navigation and pages and a list with a docked edit form that validates input (tutorial of the WKL Administration Shell Template)
• Bind a tree to a table and Create a customer grid with orders: one control at a time

• How to: Put WKL controls into forms by code
• How to: Create event procedures for a control
• How to: Handle errors of WKL controls in VBA
• How to: Choose the language of the components
• How to: Localize an application
• How to: Match the colors of the database design
• How to: Build an Access application from its text sources

Tasks for an individual control are listed in its overview (see Key features).

You place the controls on Access forms (or UserForms). Components marked (object) are created in VBA and have no window of their own.

Show and edit records from tables and queries.

Single values on a form, bound the same way as Access controls.

Arrange, split, and collapse the areas of a form.

Move around an application and run its commands.

Show states, counts, and results to the user.

Barcodes, QR codes, and images on forms and in reports.

The application as a whole and its data sources.

FindNodes, JPath filters, and AsJSON use the JSON engine of WKL Office JSON, which is included in the release package. For a quick start, the package also contains an application shell template with a navigation bar, a page in a subform, and a command bar (under templates\app-shell).

Three ways to configure a control. The property sheet (Other tab), the control's designer window (How to: Open the designer of a control), and VBA through the control's Object property (Me!Tree1.Object.RecordSource = …). All three set the same properties, which Access saves with the form.

Events in the form module. A control raises its events in the module of the form it sits on. Access doesn't list them on the Event tab of the property sheet; you create the procedures from the designer's Events page or from the lists in the code window (How to: Create event procedures for a control).

Bound to data like an Access control. The lists read a RecordSource (table, query, or SQL statement) and write the user's changes back immediately (AutoSave). A tree needs only RecordSource, KeyField, ParentField, and TextField. What the record source must allow is described in Editing and saving: prerequisites. In design view, a control doesn't query its data source unless LivePreview is True (How to: Show real data in Design view).

Fixed error numbers. The controls raise errors with fixed numbers (the WklErrorCode constants) and Err.Source = "WklForms"; texts that users see are in the language of the components (How to: Handle errors of WKL controls in VBA).

Language. Designers, messages, Err.Description, shortcut menus, and the names read by screen readers are available in English, German, Slovenian, Croatian, and Italian. The components use the language of the application model, otherwise that of Office, then that of Windows, and finally English. Error numbers, property names, stored values, and IntelliSense are the same in every language; dates and numbers follow the Windows regional settings (How to: Choose the language of the components).

Colors. Every control has a color scheme (ColorScheme). The default, Automatic, first follows Windows high contrast, then the theme colors of the Access database (for example, “Riviera”), and otherwise uses a light palette; you can also select light, dark, or high contrast explicitly. The controls don't follow the Office theme (Colorful, Dark Gray, Black), which Office stores for each user account (How to: Match the colors of the database design).

One type library, versioned with its interface. A database references the WKL Forms Object Library type library and uses the version registered on the PC when it opens; see Versions.

• How to: Install the released package
• How to: Move an application to release 2.0
• How to: Deploy an application with WKL controls to user PCs
• How to: Deliver an application for production
• What the per-user registration writes
• Security and manufacturer obligations
• How to: Collect a trace of the WKL controls for support
• Licensing

Each page describes the symptom, the versions it applies to, the cause, and the solution.

• API reference for every class and for the enumerations: see the Documentation column under Key features. Collections and their items: WklColumns · WklColumn · WklBands · WklBand · WklFormatRules · WklFormatRule · WklLinkDifferences · WklLinkDifference
• File formats: access-app.json · app-model.json · app-manifest.json · navpane.json

• CHANGELOG.md in the release package: the changes in each release
• Application shell template (in the package under templates\app-shell, described in its own README): the frame of an application, built by the build tool

All controls share one type library, WKL Forms Object Library, whose version goes up with every change to its interface. Each release has a version number x.y.z: x.y is the type library version, and z counts fixes that don't change the interface. The changes in each release are listed in CHANGELOG.md in the release package; the current release is 2.0.1.

Which version is registered. Only one version per bitness is registered at a time: registering a release removes the registration of every other version of the library for that user, so a database never picks up an older copy. To find out which version is registered:

• In the VBA editor, Tools › References shows WKL Forms x.y Object Library for a database that uses the controls.

• In VBA:

  Dim r As Reference
  For Each r In References
      If r.Guid = "{A37D549E-FCD0-48EC-8B14-C7323B747E4F}" Then Debug.Print r.Major & "." & r.Minor, r.FullPath
  Next

• The components report their release themselves: WklApplication.Version returns it (1.55.0), and WklApplication.Build returns the release, a plus sign, and the full commit ID of the source (1.55.0+ce4b826c...). Include Build in bug reports.
• In the file properties (Details tab), Wkl.Forms.ObjectLibrary.dll shows the release as the file and product version, and Wkl.Forms.ObjectLibrary.Managed.dll shows the release with the commit ID as the product version. A build from uncommitted source carries no commit ID; a release package is always built from the commit its tag points to.

Compatibility guarantee. Once released, members with their DISPIDs and classes with their GUIDs never change and are never removed. New releases only add, and every addition raises the type library version. Forms store the settings of their WKL controls in the form (OleData); a newer release reads settings stored by an older one, and settings that a newer release adds get their default value in forms saved with an older one. A fix release (z) changes no members.

A database that references an older version keeps working with a newer registration of the same major version: Access resolves the reference to the registered library when the database opens. Release 2.0 starts a new major version with the library name WklForms: a database built against 1.x has to be moved once (How to: Move an application to release 2.0). Before the database uses members of the newer release, compile it again (Debug › Compile) and create a new ACCDE. The reverse doesn't work: a database built against a newer release doesn't run with an older registration. The reference is broken (MISSING), and Access reports error 459 or “Object library not registered.” Install the components first, then the database (How to: Deploy an application with WKL controls to user PCs). Excel and Word UserForms behave differently: Office keeps its own copy of the library for each version from the first time it's used, which is why every interface change raises the version.

Major version 2.0. Resolving a reference to a newer registration is supported only within its major version. Access does bind the reference of a database for 1.x to release 2.0 through the same library ID, but the ProgIDs WklComponents.* are no longer registered then. Release 2.0 renamed the library from WklComponents to WklForms, the ProgIDs from WklComponents.* to WklForms.* and the start module to Wkl.Forms.ObjectLibrary.dll. Members, DISPIDs and GUIDs are those of 1.68, and forms keep their controls. Move an application for 1.x once (How to: Move an application to release 2.0).

Support by control, all in Access from Microsoft 365:

“Basic use” on Excel and Word UserForms means that you can insert the control on a UserForm, bind it to its data, use it from VBA, and save it with the UserForm; anything beyond that isn't supported on UserForms.

WklTreeView, WklTreeList, WklGantt, and WklGrid write each user change (a rename, a move, a cell, a check mark, a new, copied, or deleted record) to the RecordSource immediately (AutoSave = True, the default). Whether that succeeds depends on the record source.

• The record source must be updatable in Access. To check, open it as a datasheet and try to change a value. A table, a select query on a single table, and SQL text as the RecordSource can be saved to; a UNION query, a query with GROUP BY, a pass-through query, and a linked ODBC table without a unique index reject the write with wkeSourceError (&H80040302): “The change (Rename) to the node 2 could not be saved in “qUnion”: Cannot update. Database or object is read-only.” Nothing is saved, and the node keeps the text from the source.
• Fields the source can't write don't open an editor (WklGrid, WklTreeList, WklGantt, WklPropertyGrid). With AutoSave = True, the list checks the source once: DAO's Updatable and DataUpdatable, AutoNumber fields, and, for a linked ODBC table, the driver (IDENTITY, computed, and rowversion columns). Such cells behave as ReadOnly. With AutoSave = False, nothing is turned off, because the application writes the change itself. Adding, deleting, renaming in WklTreeView, and dragging stay enabled: the rejection appears only when the change is saved. For a user's change, the transaction is rolled back, the tree reloads, DataError fires with the text above, and a message box follows unless DataError sets Response to acDataErrContinue (0). For a source that can't be updated, turn editing off: AllowEditing = False (LabelEdit = False in WklTreeView), AllowAdditions = False, AllowDeletions = False, and no drag and drop.
• KeyField must identify a single record. The record is located by its key: with SQL (SELECT * FROM [source] WHERE [key] = …) for a table or query, and with FindFirst in the dynaset for SQL text. A key that occurs twice rejects the write with wkeDuplicateKey (&H80040303): “The key 2 occurs more than once in the data source. KeyField must identify each record uniquely.” Nothing is saved. A composite key is written as KeyField = "OrderID;LineNo"; its fields are combined in the same condition with AND.
• Linked ODBC tables (SQL Server, Wkl OData) need a unique index in Access, which Access takes from the primary key when you link the table. A linked SQL Server table with a primary key can be saved to; the same table without any index is read-only and rejects writes as described above. The control opens its dynaset with dbSeeChanges, which SQL Server tables with an IDENTITY column require. Through the Wkl OData driver, a table also needs a row version to be writable, whatever its key type. Without one, Access locates the record by comparing all old values (UPDATE … WHERE Id = ? AND Name = ?). The service doesn't offer that comparison for a table with a key, and the driver doesn't drop it either, so the write is rejected with wkeSourceError and the driver's message that the condition on Name can't be translated because the service doesn't offer a comparison with the old value. To fix this, add a rowversion column to the table or turn on the service's row stamp for the table (see the service documentation), and then relink the table.
• One transaction, one place. All writes for a single user action run in a transaction of the default DAO workspace, including the cell editor of WklTreeList (BeforeUpdate with Action = wuaCellValue). Cancel in BeforeUpdate or a rejection by Access rolls back the entire action and reloads the tree. Writes your own code makes through CurrentDb inside BeforeUpdate belong to the same workspace and are rolled back with it. Cancel in ValidateCell comes before any write: the editor closes and the cell keeps its old value.
• One record source for all nodes. A tree whose node types come from several tables through a UNION query can't be edited in place, because there's no mapping from node type to table. Edit such a tree through forms instead: EditForms opens the Access form for the selected node's type (sourceKeys names the key of the record behind it, and deleteFrom the table to delete from; see How to: Edit records in a form). Alternatively, set AutoSave = False and save the change yourself: the tree then changes only its display and raises no BeforeUpdate, and your handler for ValidateCell, AfterLabelEdit, NodeDrop, or BeforeDelete writes the record by its key. Set Cancel there if your write fails.

Trust. Some members write files (SaveToFile, Export…, SaveImage), open files (AutoStart), and read files named in stored properties (WklImage, including from a network share). The controls therefore don't claim to be safe for scripting and initialization (up to 2.0.0 they did). Use them only in databases and workbooks from trusted locations. On an Excel UserForm, Microsoft Forms asks when the workbook opens, even from a trusted location, whether to initialize the controls: OK gives them their saved settings, Cancel their default values.

Where you can place a control on a form (each component's page describes its own workaround):

• Reports: on a report, the lists (WklTreeView, WklTreeList, WklGantt, WklGrid, WklPropertyGrid) show a notice instead of data. WklBarcode and WklImage are designed for reports (a bound control draws each record's value in print preview and in PDF); the other controls aren't supported on reports. Build hierarchical reports with grouping levels and subreports, and use a list's export to take its view out of Access.
• Supported: 32-bit and 64-bit Access. The VBA modules that the components write (basWklRibbon, the build tool's module, the add-in) declare no API functions, so there's no PtrSafe to adjust.
• High-DPI settings aren't supported.
• Drag and drop works only within the same control: nodes in WklTreeView, WklTreeList, and WklGantt move or copy within their own tree; rows in WklGrid can't be dragged (it accepts only files from File Explorer). Dragging records from one control onto another, such as dropping many selected people onto a rule in a second list, isn't possible. Instead, let the user select with MultiSelect, read SelectedKeys, and run the assignment from a command (a WklButton, a shortcut menu command, or RowCommand), taking the target key from the other control's SelectedKey. Copying nodes doesn't copy attachment or multivalued fields.
• AutoSave writes only to a RecordSource that Access can update, with a KeyField that identifies a single record. A UNION, GROUP BY, or pass-through query and a linked table without a unique index reject the write when the change is saved, not before. There's no mapping from node type to table. A tree loaded from a Recordset or from a file has no source to write back to. See Editing and saving: prerequisites.
• The Filter of a bound control is a WHERE condition on the data source and queries it each time; for a pass-through query, the whole query runs on the server.
• In UI Automation, Access doesn't list an ActiveX control's window as a child of its form. Screen readers reach the grid through the focus, but searching the Access element tree doesn't find it.
• NodeFormat and CellFormat fire only when FormatEvents = True. Every event call into Access takes a few milliseconds, which add up for every visible node or cell on every repaint.
• Export: XLSX, XML, and JSON; WklGantt also writes images, PDF, and Microsoft Project XML. Other formats (XLS, CSV, and so on) aren't part of the controls: open the XLSX file in Excel and save it in the format you need, manually or with a few lines of VBA. The formatted Excel export omits data bars and icon sets, and, for WklGantt, dependency arrows, baselines, and deadlines (they have no cell equivalent).
• WklGantt: text in the PDF is part of the image and can't be searched. Microsoft Project XML follows the Project 2007 schema: manual scheduling, the gaps in split tasks, and the chart's appearance aren't exchanged. The Project files follow Microsoft's published schema and the Project 2010 file format; opening them in Microsoft Project itself isn't supported.

The controls and objects contain no license check: no license key and no check in design view or at run time, including in the Access Runtime and on Excel and Word UserForms. A database that uses them runs on any PC where the release is installed and registered (How to: Deploy an application with WKL controls to user PCs). The terms of use are those of your agreement with the supplier.