How to: Link a tree to a master record

Show only the records that belong to the current record of a form or another recordset, just as an Access subform does, for example the lines of the order that the form displays.

Requires WKL Forms Object Library 1.23 or later.

LinkChildFields names the fields in the tree's record source, and LinkMasterFields names the matching master fields, in the same order. Both are saved with the form. By default, the master is the current record of the form that contains the control. At run time, you can make a recordset the master instead (MasterRecordset) or pass the values directly (MasterValues), which also works on Excel, Word, and Outlook UserForms. BeforeMasterChange and AfterMasterChange occur when the master record changes. The rules that decide which source takes precedence are described under `LinkMasterFields`.

On the designer's Link page (the control's Custom row, [...]), add one field pair for each link field. Suggest fields with the same name proposes pairs of fields that have the same name in both record sources, just as the Subform Wizard does. Set control source sets the control's ControlSource to the master fields, so Access reports every record change right away. Without it, the control checks the master record every 250 ms.

Here are the same settings in the property sheet (Other and Data tabs) for a list of order lines on the order form frmOrders:

Respond to a change of the master record in the form module:

Private Sub List1_AfterMasterChange(ByVal Values As Variant)
    Me!lblLines.Caption = Me!List1.Object.NodeCount & " lines"
End Sub

Use another subform as the master instead of the form itself:

Private Sub Form_Load()
    Set Me!List1.Object.MasterRecordset = Me!sfmOrders.Form.Recordset
End Sub

On a UserForm, which has no form recordset, pass the values in code:

Private Sub cboOrder_Change()
    Me.List1.MasterValues = Me.cboOrder.Value     ' Array(...) for several link fields
End Sub

• Without a master record (a new or empty master), the tree is empty, and adding or copying nodes raises wkeInvalidValue.
• New records get the master values in their link fields.
• Setting MasterValues in code doesn't raise events; setting it to Null switches back to MasterRecordset or the form.
• LinkMasterFields can name a control on the form instead of a field, just as with a subform.
• If the record source is a pass-through query or a slow linked table, set MasterFilterMode to wmfLocal (designer: Load once, filter in memory). The tree then reads the source once and filters it in memory whenever the master changes, instead of running the query again.
• In Design view with LivePreview, and in the designer's preview, the tree shows the records for the form's first record, because Design view has no current record.
• On a UserForm, you address the control without .Object (Me.List1), because MSForms merges its own properties with the control's. If VBA reports that a member isn't found there, see "Method or data member not found" for a control on a UserForm.

• How to: Use a composite key
• API reference: LinkChildFields, MasterRecordset, MasterValues, BeforeMasterChange
• WklTreeView overview, WklTreeList overview