asked on

Treeview error 35602 key is not unique

I have a number of treeviews on a form and all work except the first one (error 35602). The treeview that doesn't work has 5 levels (refer to code snippet). The result of the first node's query is:
Code_L1 Desc_L1 (these are field names)
AORESYS a123456
0000ANC b123456

The result of the second node's query is:
W01_Code_L1 Code_L1 Desc_L1 (these are field names)
0000ANC 0000000 N/A
AORESYS 0000000 N/A

When the treeview runs the result is:
AORSYS - a123456
0000000 - NA
and the error message

The result I want is:
0000ANC - b123456
0000000 - NA
AORSYS - a123456
0000000 - NA

The level 2 codes are both 0000000 but they are attached to different level 1 nodes.

Thanks for any help you can provide, I have been working on this for some time.

merdeka

Dim db As Database
 Set db = CurrentDb
 
 Me!lstSelectedWork.Requery
 
 Set rs01 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS01.code_L1, tbl_WBS01.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN ((((tbl_WBS01 INNER JOIN tbl_WBS02 ON " & _
"tbl_WBS01.code_L1 = tbl_WBS02.w01code_L1) INNER JOIN tbl_WBS03 ON " & _
"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON " & _
"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
"((tbl_Phase.Status)<>""C""))")
 
  
  
 With tvWBS0.Nodes
  
  
  
  While rs01.EOF = False
      .Add , , "a" & rs01!code_L1, rs01!code_L1 & " - " & rs01!Desc_L1
   Set rs02 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS02.w01code_L1, tbl_WBS02.code_L1, tbl_WBS02.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN (((tbl_WBS02 INNER JOIN tbl_WBS03 ON " & _
"tbl_WBS02.code_L1 = tbl_WBS03.w02code_L1) INNER JOIN tbl_WBS04 ON tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) " & _
"INNER JOIN tbl_WBS05 ON tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) ON " & _
"tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) INNER JOIN " & _
"(tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
 
     
     While rs02.EOF = False
      .Add "a" & rs01!code_L1, tvwChild, _
           "b" & rs02!w01code_L1 & rs02!code_L1, rs02!code_L1 & " - " & rs02!Desc_L1
      Set rs03 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS03.w01code_L1, tbl_WBS03.code_L1, tbl_WBS03.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN ((tbl_WBS03 INNER JOIN tbl_WBS04 ON " & _
"tbl_WBS03.code_L1 = tbl_WBS04.w03code_L1) INNER JOIN tbl_WBS05 ON " & _
"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
 
 
        
        While rs03.EOF = False
      .Add "b" & rs02!w01code_L1 & rs02!code_L1, tvwChild, _
           "c" & rs03!code_L1, rs03!code_L1 & " - " & rs03!Desc_L1
      Set rs04 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS04.w01code_L1, tbl_WBS04.code_L1, tbl_WBS04.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN (tbl_WBS04 INNER JOIN tbl_WBS05 ON " & _
"tbl_WBS04.code_L1 = tbl_WBS05.w04code_L1) ON tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) " & _
"INNER JOIN (tbl_Phase INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
        
        
        While rs04.EOF = False
      .Add "c" & rs03!code_L1, tvwChild, _
           "d" & rs04!code_L1, rs04!code_L1 & " - " & rs04!Desc_L1
      Set rs05 = db.OpenRecordset _
("SELECT DISTINCT tbl_WBS05.w01code_L1, tbl_WBS05.code_L1, tbl_WBS05.desc_L1 " & _
"FROM (tbl_EmpResourceType INNER JOIN tbl_WBS05 ON " & _
"tbl_EmpResourceType.w05_projcode = tbl_WBS05.code_L1) INNER JOIN (tbl_Phase " & _
"INNER JOIN tbl_WBS24 ON tbl_Phase.Code_L1 = tbl_WBS24.PhaseCode) ON " & _
"tbl_EmpResourceType.ResourceType = tbl_WBS24.procurementTypeID " & _
"WHERE (((tbl_EmpResourceType.PersonCode)=""" & [Forms]![MyTimesheet]![txtUsername] & """) AND " & _
"((tbl_Phase.Status)<>""C""))", dbOpenDynaset)
 
        
      While rs05.EOF = False
        .Add "d" & rs04!code_L1, tvwChild, _
          "e" & rs05!code_L1, Mid(rs05!code_L1, 29, 10) & " * " & rs05!Desc_L1
        rs05.MoveNext
          
               Wend
              rs04.MoveNext
              rs05.Close
          Wend
          rs03.MoveNext
          rs04.Close
      Wend
      rs02.MoveNext
      rs03.Close
    Wend
    rs01.MoveNext
    rs02.Close
  Wend
  rs01.Close
 End With

Open in new window

Scott McDaniel (EE MVE )

Your Nodes Key values must be unique ... that what the error is telling you. You can prepend a value to the Key to indicate the "level" of that key. Note that this has nothing to do with the Text value of your node, which can display exactly the same thing many times ... it only relates to the Key value that you add for each Node.

merdeka

ASKER

Hi LSMConsulting,

Thanks for your reply. I had appended the letter 'a' for level 1 to the Code_L1, 'b' for level 2 to the level 2 Code_L1, etc. but it doesn't work.

The RS02 code is actually:
While rs02.EOF = False
.Add "a" & rs01!code_L1, tvwChild, _
"b" & rs02!code_L1, rs02!code_L1 & " - " & rs02!Desc_L1

but the result is the same either way. I assume the "a" and "b" in the above code is what you are referring to.

merdeka

Scott McDaniel (EE MVE )

Yes ... if that's the case, then you apparently duplicate values in the rs02!code_L1 field ... is this a Numeric field? You might try surrounding that with CStr() to see if it helps ...

What Treeview are you using?

merdeka

ASKER

Hi LSMConsulting,

I am using MSComctlLib.TreeCtrl.2. activeX control provided in Access.

rs02!code_L1 is a text field. The rs02!code_L1 field values are the same in this case but they fall under different rs01!code_L1 values as noted in the original question. I would think that in a treeview this would be a common occurrence. Maybe there is something wrong with the way I have linked the two query values (between rs01 and rs02)?

Thanks,
merdeka

merdeka

ASKER

Hi LSMConsultiing,

Any other thoughts on this, I received an email from Experst Exchange regarding this open question.

Thanks,
merdeka

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

merdeka

ASKER

Hi LSMConsulting,

That's OK, I have lots of other things to do when I'm not working on this. I will have a look at it, it seems to me this might be the soluion.

Thanks,
merdeka