method Group.PutItems (Items as Variant, [Parent as Variant])
Adds data to the control from a SafeArray containing numbers, strings, dates, or nested SafeArrays of numbers, strings, and dates, positioning them as child items of the specified parent item

TypeDescription
Items as Variant An array that control uses to fill with. The array can be one or two- dimensional. If the array is one-dimensional, the control requires one column being added before calling the PutItems method. If the Items parameter indicates a two-dimensional array, the first dimension defines the columns, while the second defines the number of items to be loaded. For instance, a(2,100) means 2 columns and 100 items.

For instance:

  • PutItems Array("Item 1", "Item 2", "Item 3"), adds the rows at the end of the list
  • PutItems Array("Root", Array("Child 1", "Child 2")), adds data in a hierarchical structure, at the end of the list
  • PutItems rs.GetRows(), appends data from a recordset using the GetRows method of the Recordset
  • PutItems rs.GetRows(10), inserts the first 10 records from a Recordset using the GetRows method, at the end of the list

where GetRows() method in ADO retrieves multiple records from a Recordset object and stores them in a two-dimensional array.

Parent as Variant Indicates one of the following:
  • missing, empty or 0 {number}, specifies that the data(Items) is being appended (added to the end of the list)
  • a long expression,  that specifies the handle of the item where the array is being inserted
  • a string expression of of "parent;IDColumn;ParentIDColumn" format, where, 'parent' denotes the handle of the item where the data is being inserted, 'IDColumn' refers to the index of the column containing row identifiers, and 'ParentIDColumn' indicates the index of the column containing identifiers of parent rows. This way, you can insert data hierarchically using parent-id relationship. A parent-id relationship is a way of organizing data in a hierarchical structure where each element (or "child") is associated with a parent element. Please be aware that the rows of the data are inserted as they were provided by the Items parameter. Therefore, it is important that the data provided be sorted by the IDColumn so that the parent row referred to by the ParentIDColumn value is already present and can be used to insert the current row as a child of it.

For instance:

  • PutItems Array("Item 1", "Item 2", "Item 3"), Items.ItemByIndex(2), inserts the rows as children of the item with index 2
  • PutItems Array("Root", Array("Child 1", "Child 2")), Items.FirstVisibleItem, Inserts data as a hierarchical structure, placing it as a child of the first visible item
  • PutItems rs.GetRows(), Items.ItemByIndex(0), inserts the records from the recordset using the GetRows method of the Recordset, placing them as children of the item with index 0
  • PutItems rs.GetRows(), ";0;3", inserts the records from the recordset using the GetRows method of the Recordset, utilizing parent-child relationships. The first column (index 0) contains the identifiers of the rows, while the fourth column (index 3) contains the keys of the parent rows.

where GetRows() method in ADO retrieves multiple records from a Recordset object and stores them in a two-dimensional array.

The PutItems method loads items from a safe array. The PutItems method may raise one of the following exceptions:

The PutItems method performs: 

  1. Insertion Order: The data is inserted into the system in the same order as it is provided by the Items parameter. This means that the sequence of rows in the Items parameter directly affects how the data is inserted.
  2. Sorting Requirement: To ensure correct insertion, it's crucial that the data is sorted by the IDColumn (when the Parent parameter is of "parent;IDColumn;ParentIDColumn" format). This sorting ensures that parent rows are inserted before their corresponding child rows.
  3. Parent-Child Relationship: The sorting ensures that when a row refers to a parent row using the ParentIDColumn  value (when the Parent parameter is of "parent;IDColumn;ParentIDColumn" format). The parent row is already present in the control. This allows the current row to be inserted as a child of the parent row without encountering errors or inconsistencies.

In essence, by sorting the data appropriately, you establish a clear hierarchy where parent rows are inserted before child rows, maintaining the integrity of the parent-child relationships within the dataset.

For instance, let's say we have the following data:

 EmployeeID  EmployeeName  DepartmentID  ParentID
 1  John  101  
 2  Alice  102  1
 3  Bob  101  1
 4   Sarah  102  1
 5  Emma  101  2
 6  Mike  102  2

Each row represents an employee.

Having this data organized into a two-dimensional array, the statement PutItems d loads it as a flat table:

whereas PutItems d, ";0;3" loads it as a group structure:

where d is an array as defined next: 

Dim d(3, 5) As Variant
d(0, 0) = "1": d(1, 0) = "John": d(2, 0) = "101": d(3, 0) = ""
d(0, 1) = "2": d(1, 1) = "Alice": d(2, 1) = "102": d(3, 1) = "1"
d(0, 2) = "3": d(1, 2) = "Bob": d(2, 2) = "101": d(3, 2) = "1"
d(0, 3) = "4": d(1, 3) = "Sarah": d(2, 3) = "102": d(3, 3) = "1"
d(0, 4) = "5": d(1, 4) = "Emma": d(2, 4) = "101": d(3, 4) = "2"
d(0, 5) = "6": d(1, 5) = "Mike": d(2, 5) = "102": d(3, 5) = "2"

Use the GetItems method to get a safe array with the items in the control. The PutItems method fires AddItem event for each item added to Items collection. Use the Items property to access the items collection. Use the ConditionalFormats method to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula.