[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1146
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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