• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1159
  • Last Modified:

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

0
merdeka
Asked:
merdeka
  • 4
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
merdekaAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
merdekaAuthor Commented:
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
0
 
merdekaAuthor Commented:
Hi LSMConsultiing,

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

Thanks,
merdeka
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry, got lost in the shuffle.

Each Node in the treeview must have a unique Key ... from what you're saying, you'll have duplicate Key values, but on different Roots ... even this is no acceptable. You'll need to rework your code to insure uniqueness ... you could add a simple random value, or you could use an "intelligent" value (like perhaps a Nodecount value or something). It would seem to me that a combination of the two recordsets would make a unique, but I'm not sure what sort of data you're working with.
0
 
merdekaAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now