Treeview up to 5 levels 1

[bartus991]

It took some time, but I'll have created a 2-level treeview, but now for the first time I would like to go 5-levels deep, but trying to add the third level it gives me an error.

Till now I have programmed:

Function tvwTicketSets_Fill()

On Error GoTo ErrorHandler

   Dim strMessage As String
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim intVBMsg As Integer
   Dim strQuery1 As String
   Dim strQuery2 As String
   Dim strQuery3 As String
   Dim nod As Object
   Dim strNode1Text As String
   Dim strNode2Text As String
   Dim strNode3Text As String
   Dim strVisibleText1 As String
   Dim strVisibleText2 As String
   Dim strVisibleText3 As String
   
   Set dbs = CurrentDb()
   strQuery1 = "Ticketset"
   strQuery2 = "TicketsetsPrijstypesForTvw"
   strQuery3 = "TicketsetsDagForTvw"
   
   With Me![tvwTicketsets]
      'Fill Level 1
      Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)

      Do Until rst.EOF
         Debug.Print "Adding Level 1 item: " & rst![TicketsetId]
         strNode1Text = StrConv("Level1 - " & rst![TicketsetId], _
            vbLowerCase)
         Debug.Print "Node 1 text: " & strNode1Text
         strVisibleText1 = rst![Titel]
         Debug.Print "Level 1 visible text: " & strVisibleText1
         Set nod = .Nodes.Add(Key:=strNode1Text, _
            Text:=strVisibleText1)
         nod.Expanded = True
         rst.MoveNext
      Loop
      rst.Close
           
      'Fill Level 2
      Set rst = dbs.OpenRecordset(strQuery2, dbOpenForwardOnly)

      Do Until rst.EOF
         Debug.Print "Adding Level 2 item: " & rst![TicketsetId]
         strNode1Text = StrConv("Level1 - " & rst![TicketsetId], vbLowerCase)
         Debug.Print "Node 1 text: "; strNode1Text
         strNode2Text = StrConv("Level2 - " & rst![Ticketset_PrijstypesId] & " - " _
            & rst![Titel], vbLowerCase)
         Debug.Print "Node 2 text: " & strNode2Text
         strVisibleText2 = rst![Titel]
         Debug.Print "Visible text: " & strVisibleText2
         .Nodes.Add relative:=strNode1Text, _
            relationship:=tvwChild, _
            Key:=strNode2Text, _
            Text:=strVisibleText2
         rst.MoveNext
      Loop
      rst.Close
      
      'Fill Level 3
      Set rst = dbs.OpenRecordset(strQuery3, dbOpenForwardOnly)

      Do Until rst.EOF
         Debug.Print "Adding Level 3 item: " & rst![Ticketset_PrijstypesId]
         strNode2Text = StrConv("Level2 - " & rst![Ticketset_PrijstypesId], vbLowerCase)
         Debug.Print "Node 2 text: "; strNode2Text
         strNode3Text = StrConv("Level3 - " & rst![Ticketset_GeldigOpDagId] & " - " _
            & rst![Titel], vbLowerCase)
         Debug.Print "Node 3 text: " & strNode3Text
         strVisibleText3 = rst![Titel]
         Debug.Print "Visible text: " & strVisibleText3
         .Nodes.Add relative:=strNode2Text, _
            relationship:=tvwChild, _
            Key:=strNode3Text, _
            Text:=strVisibleText3
         rst.MoveNext
      Loop
      rst.Close
      
   End With
   dbs.Close

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   Select Case Err.Number
      Case 35601
         'Element not found
         strMessage = "Possible Causes: You selected a table/query" _
            & " for a child level which does not correspond to a value" _
            & " from its parent level."
         intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly + _
            vbExclamation, "Run-time Error: " & Err.Number)
      Case 35602
         'Key is not unique in collection
         strMessage = "Possible Causes: You selected a non-unique" _
            & " field to link levels."
         intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly + _
            vbExclamation, "Run-time Error: " & Err.Number)
      Case Else
         intVBMsg = MsgBox(Error$ & "@@", vbOKOnly + _
            vbExclamation, "Run-time Error: " & Err.Number)
   End Select
   Resume ErrorHandlerExit

End Function

After opening the form, it gives me error: 35601
When I change level 3 as follows:

      'Fill Level 3
      Set rst = dbs.OpenRecordset(strQuery3, dbOpenForwardOnly)

      Do Until rst.EOF
         Debug.Print "Adding Level 3 item: " & rst![Ticketset_PrijstypesId]
         strNode2Text = StrConv("Level2 - " & rst![Ticketset_PrijstypesId], vbLowerCase)
         Debug.Print "Node 2 text: "; strNode2Text
         strNode3Text = StrConv("Level3 - " & rst![Ticketset_GeldigOpDagId] & " - " _
            & rst![Titel], vbLowerCase)
         Debug.Print "Node 3 text: " & strNode3Text
         strVisibleText3 = rst![Titel]
         Debug.Print "Visible text: " & strVisibleText3
         .Nodes.Add relative:=strNode1Text, _
            relationship:=tvwChild, _
            Key:=strNode3Text, _
            Text:=strVisibleText3
         rst.MoveNext
      Loop
      rst.Close

It won't give me any error, but then it will add the third level as a secondlevel. I know that is is because of: .Nodes.Add relative:=strNode1Text. But can't figure out why it won't work for the third level.

After adding this one, I still have to add 2 more.

The treeview will be used for editing a Price Set, a complex set of tables, but with a treeview it will be simple to change etc.

Hopefully someone can help me further.

 

[MajP]

I use treeviews a lot. And I have built a custom class so I can load and manipulate any tree view with about 4 lines of code. All the functionality is contained in the class.

I have found that by far the easiest way to do this is to use a union query because the query is always the same regardless if you have 1 or 100 levels. You need to make a self referencing query. Basically each query will always be the same:
a unique ID
a parent ID
some text to display
and an idetifier to show which table the record is from

Take a look at the individual queries and then the union query. If I want to add more levels I just add more to the union query.

http://www.4shared.com/file/164410582/7753dbae/Generic_TreeView_v11.html

With this code I can make a treeview in any database for as many levels in about 5 minutes a level.

 

[bartus991]

Thanks this works really great!!

Is it also possible to make an Add New Nod.
The first nod on the tree view states: Add New which will add a new record.

The other nodes have to be alphabeticly sorted and used to search up the selected record for modify

 

[MajP]

Unless the tree is really big, I just reload it.
Something like
1)Click a button or the tree to add a node
2)Pop open a form to enter a record
Requery
3)Reload the tree (since it comes from a query the new record will sort correctly)

The other way gets more involved
1)Click on something to enter a record
2)Save the record but also get the new information including a unique ID
3) Figure out where in the tree to put it
4) Figure out the Parent Node
5)Create a node as a child if it is a child or a root node

There is a getSelectedNode function. That returns the selected node. So if you want to click on a node and add a new record as a child to that node you have the information.

You can use the Nodes.add method to add a node. I did not make it a method in my class because there is no way to make this generic to link to a table.

However, if you can explain in a little more detail how you want it to work, I can explain how to do it.

 

[bartus991]

After adding a new record the treeview will be reloaded. But at this moment I have the following treeview:

- Normal
- Child
- Student
- Senior

After clicking on one of above nodes the form will go to the appropriate record. This works great. On this form there is also a button: Add New, which will go to a new record. After saving, or clicking on the treeview, the treeview will be reloaded. But I want to delete this button and add a Add New to the treeview, which gives you the following nodes:

- Add New
- Normal
- Child
- Student
- Senior
Only the first node will go to a new record, all the other nodes will still be used as a record selector, or in some cases as a filter.

I now that it exists but do not know if this is possible in an access treeview.

 

[MajP]

Load the tree then call this to add a new node at the top.

Public Sub makeAddNewNode()
  Dim firstNode As Node
  Dim newNode As Node
  'get the top node of your tree
  Set firstNode = tvw.Nodes(1)
  'add a new node above the first node
  Set newNode = tvw.Nodes.Add(firstNode, tvwFirst, "AddNewNode", "Add New")
  newNode.Bold = True
End Sub

Then in your node click event something like:

 If Me.selectedNode.Key = "AddNewNode" Then
   '"pop open some form to add new Record. Then reload form"
   Else
    '"Use the node click event to do something like open a form to edit this node.  The selected node key is: " & Me.selectedNode.Key
   End If

See demo

http://www.4shared.com/file/233728794/34e5b70a/Generic_TreeView_v11_with_Add_.html

 

[bartus991]

First of all thanks for your post. I have deleted my previous code for the tree and implemented yours.

Now I have 4 more questions.
The first:
In your example database your node key exists out of the identifier and the Id of the table, for example CustALFI.
How can I simply retrieve the table key ALFI. Do I have to add a column to the query or is there a simple way to split both values.

Second:
Each form will have a different treeview, which is simple to load. But is it possible to programm the node click event per form and not in a module. As each form have a different click event, some times it will open a new window or it will used as a filter for a subform.

Thirth:
Can you simple programm the click event per level. For example the first level won't have any event, only opens the childlevel. But the second level will retrieve the selected record.

Fourth:
I think you code will offer some new enhanched options. Is it possible to create on the main form a button, and depending on the programmed options the treeview will change. For example, a button with a textbox next to is that states Customers, the treeview will use your QryCustomers, and the textbox that stats Orders will use the QryOrders for the treeview.

 

[MajP]

Take a look at the methods and properties of the class. Some day I will add comments.

The first:
In your example database your node key exists out of the identifier and the Id of the table, for example CustALFI.
How can I simply retrieve the table key ALFI. Do I have to add a column to the query or is there a simple way to split both values.

There is a method called "getNodeIdentifier", and getNodePK and that is the purpose.

Each form will have a different treeview, which is simple to load. But is it possible to programm the node click event per form and not in a module. As each form have a different click event, some times it will open a new window or it will used as a filter for a subform.

They way I do it is in the class module I call a procedure in a standard module. I pass the standard procedure probably some values from the selected node( PK, Table, etc)

in the node click I would put simply
Call commonNodeClick (....)

in your standard module
public sub commonNodeClick (nodePK, nodeIdentifer,NodeLevel....)
select case screen.ActiveForm
case "frmOne"
do something
case "frmTwo"
....

Can you simple programm the click event per level. For example the first level won't have any event, only opens the childlevel. But the second level will retrieve the selected record.

Again already done. Look at the getSelectedNodeLevel. Pass this as a parameter to your common event

...
public sub commonNodeClick (nodePK, nodeLevel, nodeIdentifier)
....
case "frmOne"
select case nodeLevel
case 1
do something
case 2
.....

Fourth:
I think you code will offer some new enhanched options. Is it possible to create on the main form a button, and depending on the programmed options the treeview will change. For example, a button with a textbox next to is that states Customers, the treeview will use your QryCustomers, and the textbox that stats Orders will use the QryOrders for the treeview.

Yes I have a demo using one form where I demonstrate 5 differnt trees. Click the button and initialize the tree using a different query. It is one line of code.

 

[bartus991]

Thanks for your reply, can you please provide me the line of code or the mentioned demo using 5 different trees.

I ams trying to programm the treeview actions as you mentioned above, an I'm testing the following code:

Public Sub commonNodeClick(nodePK, nodeIdentifer, NodeLevel)
    
    Select Case Screen.ActiveForm.Name
    
    Case "tbl_Prijstype"
        If nodePK = "AddNewNode" Then
          DoCmd.RunCommand acCmdRecordsGoToNew
        Else
          DoCmd.FindRecord "PrijstypeId =" & nodePK
        End If
    End Select
End Sub

But in both cases I get the error that the action is unavailble.

 

[MajP]

http://www.4shared.com/file/236183722/27a298ee/TreeView_v12.html

Here this demo show a common click event. This is probably a much cleaner way to do it. The goal with a class module is never to have to modify the code within the class. In this example I push the reference to the tvw form out to a standard module and do not have to worry about passing parameters. Then the user can just modify the commonNodeClick event to meet their needs.

This Demo only shows switching between two trees, but obviously I could add as many as I want. Just need more self referencing queries.

 

[bartus991]

Thanks, for the demo. I have altered the VB code as descibed above.

But It can still not use the following VB code in the commonNodeClick event:

 DoCmd.RunCommand acCmdRecordsGoToNew

or

DoCmd.FindRecord "PrijstypeId =" & nodePK

Is their any other way to accomplish this as Access is giving me an error message that this action is not available at the moment.

 

[MajP]

use the gotrecord aclast, but ensure that you have focus on the form/subform or you will get the "not available ..". Obviously the subform does not have focus because this is firing when the Tree has focus. Therefore the error.