WklPropertyGrid

Dim myWklPropertyGrid As WklPropertyGrid
Set myWklPropertyGrid = Me!ControlName.Object

One record as lines “caption | value”, the fields grouped into categories that fold away - like the property window of Visual Studio - with the editors, formats, lookups and rules of WklGrid.

None

Two modes. Without RecordSource the grid shows the current record of the Access form it sits on: every value is read like Me!Field, a changed value goes into the form's record, which becomes dirty and is saved by Access as any typing on the form - when the record is left or saved, with the form's own validation. The grid follows the form's records by itself (it compares the record shown with the form's current one a few times per second; no form module is needed). With RecordSource and LinkMasterFields it is the detail of a WklGrid or WklTreeList on the same form - LinkMasterFields = "Grid1" follows the record selected in Grid1, whose own source may be a pass-through query - and writes each field into its own record source when the user leaves the field (AutoSave). WklGrid selects no record when the form opens, so the detail stays empty until the first selection.

The rows are WklColumn objects (Rows): field, caption, editor, format, lookup, read-only state and rules exactly as for a grid column; the column's Band is the row's category. Without rows every field of the source is a row, with an editor for its type. Fields without category come first; the categories follow in the order of their first field, each with a header that folds its fields away (click, or Left/Right/Enter on the header).

Which fields can be edited, the data source tells without any setting: a pass-through or union query cannot be written, calculated fields of a query, AutoNumber fields and columns a linked ODBC table reports as not updatable are read-only; in form mode the form's AllowEdits applies too. Read-only rows have a gray caption and open no editor; FieldEditable tells the state. With a recordset handed over by code nothing is asked: every field counts as editable and a change goes into that recordset, which a static or read-only ADO recordset keeps in memory without an error - set ReadOnly on the rows in that mode. Rows name fields: empty them with Rows.Clear before a source without those fields, which the grid refuses otherwise (wkeSourceError).

Keyboard as in the Visual Studio property window: Up and Down go from line to line (category headers included), Page Up/Down by a page, Ctrl+Home/End to the first and last line; Tab and Shift+Tab go from value to value; F2 or typing edits, Enter saves, Esc discards; on a category header Left folds, Right unfolds, Enter toggles; Left on a field goes to its category. Screen readers hear each field by its caption, with its value in the Value pattern.

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.

' The detail of the customer list: Props1 shows the customer selected in Grid1.
Private Sub Form_Load()
    With Me!Props1.Object
        .RecordSource = "tblCustomers"
        .KeyField = "ID"
        .LinkMasterFields = "Grid1"
        .LinkChildFields = "ID"
        .Rows.Clear
        .Rows.Add "Name", "Name"
        .Rows.Add "Ort", "Ort", "Adresse"
        .Rows.Add "Aktiv", "Aktiv", "Status"
    End With
End Sub

None

Property Get Key() As Variant

Gets the key of the record shown (the value of KeyField); Null when no record is shown.

None

Property value: Variant

In form mode without KeyField the record's position in the form (CurrentRecord), “neu” on a new record.

Default member: it can be left out, as in myWklPropertyGrid(...) for myWklPropertyGrid.Key(...). Read-only.

None

Declared by WklPropertyGrid.

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

Gets or sets the table, query or SQL statement with the record shown; empty: the record of the form the grid sits on.

None

Property value: String

With LinkMasterFields the grid shows the record linked to the master's current record. Without a record source it follows its form's current record and writes into it (form mode, see the class remarks). Setting it in form view reads the record again.

Read/write.

None

Declared by WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

Property Get Rows() As WklColumns

Gets the rows: one WklColumn per field, in the order shown.

None

Property value: `WklColumns`

Rows.Add Field, Caption, Category appends a row; the third argument is the column's Band, here the category. Without rows every field of the source is a row. Width, footer summary, line in band and the tree column of a grid column have no meaning here.

Read-only.

None

Declared by WklPropertyGrid.

Property Get FormatRules() As WklFormatRules

Returns the conditional formatting rules.

None

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

Read-only.

None

Declared by WklPropertyGrid.

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

Gets or sets the height of a line in twips; 0 (default): from the font.

None

Property value: Long

Read/write.

None

Declared by WklPropertyGrid.

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

Gets or sets whether values can be edited (default True); what the data source cannot write stays read-only either way (FieldEditable).

None

Property value: Boolean

Read/write.

None

Declared by WklPropertyGrid.

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

Gets or sets the field of the line with the focus.

None

Property value: String

Read/write.

None

Declared by WklPropertyGrid.

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

Gets or sets whether FieldFormat is raised while each value is drawn (default False).

None

Property value: Boolean

Every call to VBA costs about 3 ms per value shown; switch it on only when the form handles FieldFormat.

Read/write.

None

Declared by WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

Property Get Value(ByVal Field As String) As Variant
Property Let Value(ByVal Field As String, RHS As Variant)

Gets or sets the value of a field of the record shown.

Property value: Variant – The value; Null for an empty field.

Setting writes as the user does: in form mode into the form's record (dirty until Access saves it), with RecordSource into the record source at once. A field the source cannot write is refused with wkeInvalidValue and a text naming the field. Without a record shown: wkeNotConfigured.

Read/write.

None

Declared by WklPropertyGrid.

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

Gets or sets the width of the caption column in twips (default 2160 = 1.5 inches).

None

Property value: Long

Read/write.

None

Declared by WklPropertyGrid.

Property Get CategoryCollapsed(ByVal Category As String) As Boolean
Property Let CategoryCollapsed(ByVal Category As String, RHS As Boolean)

Gets or sets whether the fields of a category are folded away.

Property value: Boolean – True: only the category's header is shown.

Set in design view it is saved with the form. The user folds and unfolds with a click or Left/Right on the header; the user's folding lasts until the form closes (it is not remembered per user).

Read/write.

None

Declared by WklPropertyGrid.

Sub SetError(ByVal Field As String, ByVal Text As String)

Marks a field as wrong, as a rule would, or clears its mark.

None

None

Declared by WklPropertyGrid.

Property Get ErrorText(ByVal Field As String) As String

Gets the message of a field's error; empty without error.

Property value: String

Read-only.

None

Declared by WklPropertyGrid.

Property Get FieldEditable(ByVal Field As String) As Boolean

Gets whether the user could edit a field.

Property value: Boolean – True when the data source can write the field, the row is not ReadOnly and AllowEditing is on.

The source decides without any setting: a pass-through or union query, a calculated field of a query, an AutoNumber field or a column a linked ODBC table reports as not updatable cannot be written; in form mode the form's AllowEdits applies too. With a recordset handed over by code every field counts as writable (the grid cannot ask that recordset); a row's ReadOnly decides there. A field without row: wkeKeyNotFound - the rows are those of Rows; in form mode without rows they are made from the form's fields the first time the grid reads the form's record.

Read-only.

None

Declared by WklPropertyGrid.

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 lines, captions and categories: 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 WklPropertyGrid.

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. The value is saved with the form; a form saved before version 1.47 takes the default.

Read/write.

None

Declared by WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

Event FieldClick(ByVal Key As Variant, ByVal Field As String)

Occurs when the user clicks a value.

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

None

Declared by WklPropertyGrid.

Event FieldDblClick(ByVal Key As Variant, ByVal Field As String)

Occurs when the user double-clicks a value.

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

None

Declared by WklPropertyGrid.

Event BeforeFieldEdit(ByVal Key As Variant, ByVal Field As String, ByRef Cancel As Integer)

Occurs before an editor opens for a value.

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

None

Declared by WklPropertyGrid.

Event ValidateField(ByVal Key As Variant, ByVal Field As String, ByRef NewValue As Variant, ByRef Cancel As Integer)

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

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

None

Declared by WklPropertyGrid.

Event AfterFieldUpdate(ByVal Key As Variant, ByVal Field As String)

Occurs after a value was written.

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

None

Declared by WklPropertyGrid.

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

Occurs while a value is drawn, when FormatEvents is on; the procedure can change its colors and font.

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

None

Declared by WklPropertyGrid.

Event FocusedFieldChange(ByVal Key As Variant, ByVal Field As String)

Occurs when the focus moved to another field.

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

None

Declared by WklPropertyGrid.

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 WklPropertyGrid.

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 WklPropertyGrid.

Event ValidationChange()

Occurs when the errors shown have changed.

None

After a rule refused an input or is met again, after SetError and ClearErrors, and after a write the data source refused. Read ErrorCount and ErrorSummary for your own summary. Like all events of the control it needs the form's module.

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

Private Sub Grid1_ValidationChange()
    Me!lblFehler.Caption = Me!Grid1.Object.ErrorSummary
End Sub

Declared by WklPropertyGrid.