How to: Use a composite key
Bind a tree to records whose key is made up of several fields, such as order lines keyed by order number and line number, which Access itself can show only in a flat subform.
Requires WKL Forms Object Library 1.23 or later.
Solution overview
KeyField and ParentField accept several field names separated by semicolons, the same number
of names in the same order. In VBA, a composite key is an array with one value per field wherever
a key appears: in properties, method arguments, return values, and event parameters. KeyCriteria
builds the WHERE condition for a key. A single key field works as before, and its key remains a
single value.
Implementation
Bind the tree on the Other tab of the property sheet or on the designer's Record source page:
| Property | Value |
|---|---|
RecordSource | tblLines |
KeyField | OrderID;LineNo |
ParentField | OrderID;ParentLineNo |
TextField | Description |
Work with the keys in the form module:
Private Sub List1_NodeSelect(ByVal Key As Variant)
' Key is Array(OrderID, LineNo)
Me!txtLine = Key(1)
Me!sfmDetails.Form.Filter = Me!List1.Object.KeyCriteria(Key) ' [OrderID] = 4711 AND [LineNo] = 2
Me!sfmDetails.Form.FilterOn = True
End Sub
Private Sub cmdAddLine_Click()
Dim newKey As Variant
newKey = Me!List1.Object.AddNode(Array(4711, 1), "New line") ' below line 1 of order 4711
Debug.Print newKey(0), newKey(1) ' 4711, next free line number
End Sub
Remarks
- A composite key has no AutoNumber. For a new node, the control proposes a key in which the last part continues from the highest value among records with the same leading parts.
BeforeNodeAddandBeforeNodeCopycan set their own key withNewKey = Array(…). - A record becomes a root node if its parent key has a Null part or doesn't match any record.
- Moving a node writes every part of
ParentField. - An array of the wrong length raises
wkeInvalidValue. Parts are compared by Access rules: numbers by value, and text case-insensitively. - Files and strings written by
SaveToFileandSaveToStringstore a composite key as a JSON array; inFindNodes,@.Keyis that array and@.Key[1]is one of its parts. - Index all key fields together in the table; every query compares all parts.