How to: Build a tree from flat records

Show a view or pass-through query without a parent key as a tree, grouped by the values of some of its fields.

Requires WKL Forms Object Library 1.23 or later.

GroupFields lists the fields to group by, separated by semicolons, starting with the outermost. On each level, the list creates one group node for each distinct value, and the records become the leaf nodes. ParentField can stay empty.

With Me!List1.Object
    .RecordSource = "ptqTasksFlat"            ' pass-through query to SQL Server
    .KeyField = "TaskID"
    .ParentField = ""
    .TextField = "Instruction"
    .GroupFields = "Site;Building;Floor"
End With

• Each group node shows its value as text ((empty) for Null or empty text) and the number of its records as a tooltip.
• The groups on each level are ordered by their value, compared by its data type, so dates and numbers sort as dates and numbers rather than as text. SortOrder sets the direction for every level: with wsoAscending (the default) the oldest day comes first, with wsoDescending the newest. A sorted column (SortDirection) orders only the records within a group, because group nodes have no value in the columns. The one exception is the TextField column, which shows the group value as text: from release 1.62, sorting that column orders the groups by their value in the column's direction. A hidden column for the group field doesn't change the order of the groups.
• Groups don't represent records: they can't be edited, renamed, or deleted; adding, copying, and moving are turned off; and footer totals count only records.
• Grouped records are always loaded completely, so search runs over the loaded nodes without querying the source again. A Filter still queries the record source.
• If ParentField is also set, a record whose parent record exists stays below it.
• Expanded groups and the selection survive Requery, because group keys are built from the values along their path.
• Most of the Requery time on grouped records is spent reading the query; building the groups adds little.

• API reference: GroupFields
• How to: Search and filter nodes
• WklTreeList overview