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

merdekaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.