Problem populating TreeView.

[rpk2006]

This question has been posted in other forums too. If any of you answered to this question there, kindly ignore this question.

I have a TREE on my Form to display customer names sorted by Cities and Locations. I want to display like this:

+CITIES
|...+City1
|...|..+Location1
|...|..|...Customer1
|...|..|...Customer2

I am using two arrays namely, LoadCollection and LoadLocations, to fill the DISTINCT names of cities and locations respectively.

I then use these array values to prepare the Tree and use SQL Query to extract related information from my Table, which consists of fields, city and location respectively.

I am using following code to populate the Tree. But it displays an error: "Key is not unique in collection", when the value of i (of first loop) changes to 2.

Kindly help me.

    Dim TotRec, i, j, l
    Dim SearchQuery As String
    Dim sCity, sLocation
    Dim rsSelectCity As ADODB.Recordset
    Dim rsSelectCustomer As ADODB.Recordset
    
    'Open recordset
    Set rsSelectCity = New ADODB.Recordset
    Set rsSelectCustomer = New ADODB.Recordset
    
    If rsSelectCity.State = adStateClosed Then
        rsSelectCity.Open "Select * from customer", Conn, adOpenKeyset, adLockReadOnly
    End If
    rsSelectCity.MoveFirst
    
    TreeView1.Nodes.Add , , "City", "CITIES"
    
    'Populate Tree
    For i = 1 To tot
        sCity = CityColl(i)
        
        'City Names
        TreeView1.Nodes.Add "City", tvwChild, "SubCity" & i, RTrim(UCase(sCity))
        
        For l = 1 To TotLoc
                'Load location name from location array
                sLocation = LocationColl(l)
                
                'Location Names within City Name
                TreeView1.Nodes.Add "SubCity" & i, tvwChild, "Location" & l, RTrim(UCase(sLocation))
                
                'Update Query String Instantly
                SearchQuery = "Select CustID, CustName, Location From customer Where City = '" & sCity & "' and Location = '" & sLocation & "'"
                
                If rsSelectCustomer.State = adStateClosed Then
                     rsSelectCustomer.Open SearchQuery, Conn, adOpenKeyset, adLockReadOnly
                End If
                
                TotRec = rsSelectCustomer.RecordCount
                
'                rsSelectCustomer.MoveFirst
                
                For j = 1 To TotRec
                    'TreeView1.Nodes.Add "p" & i, tvwChild, , RTrim(rsSelectCustomer!CustID) & "- " & rsSelectCustomer!custname, "CustomerImage"
                        TreeView1.Nodes.Add "Location" & l, tvwChild, , RTrim(rsSelectCustomer!CustID) & "- " & rsSelectCustomer!custname, "CustomerImage"
                        rsSelectCustomer.MoveNext
                Next j
                
                If rsSelectCustomer.State = adStateOpen Then
                    rsSelectCustomer.Close
                End If
                If l = 6 Then
                    Exit For
                End If
            Next l
    Next i

Please note, that the code that I am using to fill the arrays is running fine, so I have not included it. Assume the City Names and Location Names coming from the arrays in the above code.

 

[HermanvLimbeek]

Replace i with trim(str(i)) when making the key for the node item. I've had the same problem and I think this was the solution.

Herman