How to: Show a record as a property list

Show the current record of a form, or the record selected in a list, as “caption | value” lines grouped into categories, for example as the “Properties” card of a side bar.

Requires WKL Forms Object Library 1.38 or later.

WklPropertyGrid has two modes. Without a RecordSource, it displays and writes the current record of its own form. With RecordSource, KeyField, and LinkMasterFields, it shows the details for a WklGrid or WklTreeList on the same form. Its rows are WklColumn objects (Rows), and a row's Band is its category.

The form's current record, on a form bound to tblCustomer, with a property grid that has no record source:

Private Sub Form_Load()
    With Me!Props1.Object
        .Rows.Clear
        .Rows.Add "Name", "Name"
        .Rows.Add "City", "City", "Address"
        .Rows.Add "Active", "Active", "Status"
        .CategoryCollapsed("Address") = False
    End With
End Sub

The details for a list whose source is a pass-through query: the property grid reads from and writes to its own writable source, and the list only supplies the key.

Private Sub Form_Load()
    With Me!Props1.Object
        .RecordSource = "tblCustomer"
        .KeyField = "ID"
        .LinkMasterFields = "Grid1"
        .LinkChildFields = "ID"
    End With
End Sub
 
Private Sub cmdSetCity_Click()
    With Me!Props1.Object
        If .FieldEditable("City") Then .Value("City") = "Boston"
    End With
End Sub

• In form mode, a changed value makes the form's record dirty; Access saves it when the user leaves the record or when you call Me.Dirty = False, running the form's validation and BeforeUpdate. The grid follows the form's records without any code in the form module.
• Without rows, every field in the source becomes a row, with an editor that matches its data type.
• With a recordset passed in code (Set .Recordset = rs), the grid doesn't query the source at all: every field counts as editable, and a changed value goes into that recordset. Whether it reaches the database depends on the recordset. A static or read-only ADO recordset keeps the change in memory without raising an error: every field appears editable, the typed value is in the recordset, and the database stays unchanged. In this mode, set ReadOnly on the rows yourself.
• Rows refer to fields by name. Before you give the grid a source that doesn't have those fields, clear the rows with Rows.Clear; if a row's field is missing from the new source, the grid rejects the source with wkeSourceError (“the recordset has no field …”).
• Read-only with nothing to configure: a pass-through or union query, a calculated field, an AutoNumber field, or a column that a linked ODBC table reports as not updatable; in form mode, also a form with AllowEdits = No. Writing such a field in code raises wkeInvalidValue.
• How a linked ODBC table is checked: Access reports DataUpdatable = True for nearly every column of a linked ODBC table, so the grid doesn't rely on it. Instead, it opens its own connection with the table's connection string and asks the driver about each column (SQLColAttribute: updatable and auto-value); a column that the driver reports as read-only or auto-value becomes read-only. The rule, so that driver authors can check a driver without testing it: a column is read-only when SQL_DESC_UPDATABLE (field 10, called SQL_COLUMN_UPDATABLE in ODBC 2) returns SQL_ATTR_READONLY (0), or when SQL_DESC_AUTO_UNIQUE_VALUE (field 11) returns a value other than 0. SQL_ATTR_WRITE (1) and SQL_ATTR_READWRITE_UNKNOWN (2), without an auto-value, leave the column writable as far as the grid is concerned; a driver that answers “unknown” for a column gives the grid nothing to go on, and the save then fails on the server. With ODBC Driver 18 for SQL Server, IDENTITY columns, computed columns, and rowversion columns are read-only. Editing columns of a linked SQL Server view that come from an expression or a scalar subquery isn't supported; whether they're recognized depends on what the driver reports for them. Until you've checked such a view, set ReadOnly for those rows yourself: a save that includes a column the server can't update fails as a whole, and the user's other changes are lost too.
• To lock a field for one record only (rather than for every record, as the row's ReadOnly does), set Cancel = True in BeforeFieldEdit for that key and field; no editor opens.
• The control doesn't limit the number of rows; lists of 100 rows or more aren't supported.
• WklGrid doesn't select a record when the form opens, so the details stay empty until the user makes the first selection.
• Collapsed categories set in Design view (CategoryCollapsed) are saved with the form; categories the user collapses stay that way until the form closes.

• WklPropertyGrid overview
• API reference: WklPropertyGrid (Rows, Value, FieldEditable, CategoryCollapsed)
• How to: Build a side bar with cards: the property grid as one card of a tab group