Treeview - Ow, my brain (1 Viewer)

[JordanR]

Trying to setup a treeview and it's hurting my brain. So here's hoping people can help.

tblReviewers
Reviewer_ID
Reviewer_Name

tblCases
Case_ID
Case_Description
Reviewer (Stores the value from tblReviewers.Reviewer_Name)

I want a Treeview where the parent nodes area a reviewer's name and all associated cases are children under that node. Next local step, of course, would be that a user could click on a child node and have it open the form with that Case_Description.

I've tried modifying some of the sample codes that people have graciously posted and the very first error I get is "User definied type not defined" on a simple Dim dbLocal As Database.
Any help would be graciously appreciated.

 

[MarkK]

A few things...
If you are going the two table route, consider linking the tables on ReviewerID rather than reviewer name.

Your "User-defined type not defined" error is the you have not set a reference to DAO. Open a code window and on the main menu nav to Tools->References and check the item labelled "Microsoft DAO 3.6 Object Library", or some reasonable facsimilie.

Also, if you're using treeviews and your brain feels like melted cheese, you're on the right track.

 

[JordanR]

Good to know. Thanks. I'll keep plugging away at it.

 

[JordanR]

Ok, I'm not getting anywhere here. I've tried modifying several different codes that I've found and I keep getting stopped at the same spot.
Why in heaven's name would Access not like:

      Const strTableQueryName = "inc_Incident"
      Dim db As Database, rst As Recordset
      Set db = CurrentDb
      Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset, dbReadOnly)

Every single time I get a mismatch type error on the rst line. I just don't get it. Please help.

 

[MarkK]

Are you sure you're opening a DAO.Recordset? Check your references and make sure DAO has a higher priority than ADODB, or declare your rst as follows:

Dim rst As DAO.Recordset

 

[Mile-O]

Every single time I get a mismatch type error on the rst line. I just don't get it. Please help.

Have a read of this.

 

[JordanR]

I am continuously so incredibly impressed with this forum and this site. Thank you for the quick answer. I'm positive I'll have more as I muddle my way through this silly treeview experiment, but thank you for all your help!

 

[JordanR]

Ok, now things aren't giving me errors so I'm going in the right direction. But I'm back to my original question:

tblReviewers
Reviewer_ID
Reviewer_Name

tblCases
Case_ID
Case_Description
Reviewer (Stores the value from tblReviewers.Reviewer_Name)

I want a Treeview where the parent nodes area a reviewer's name and all associated cases are children under that node. Next local step, of course, would be that a user could click on a child node and have it open the form with that Case_Description.

Currently, my treeview is returning the reviewer EVERY time he shows up in the tblCases table. Obviously, I'm going for unique values here, but have no idea how to make that happen.
Plus I'm not sure how to go about making the treeview display hte Case_Description associated with each Reviewer.
Thanks.

 

[MarkK]

Here's a fast & dirty example tree. Requires a reference to mscomctllib.ocx to drive the tree control. Offers a few ideas on how you might proceed.

 

[JordanR]

Holy Crud! Thank you so much. That should be more than sufficient to get me started. That's really appreciated man. You made my Monday!

Just out of curiosity, once I have this up and running my next goal is to have a second main node for reporting. I assume I'd have to staticly define each child note under that, correct?

 

[MarkK]

You're welcome. I think treeviews are the coolest!
Except for root nodes, I'd always avoid hard coding nodes since your code gets out of hand almost immediately. Rather, see if you can leverage Parent-Child relationships that already exist in your tables, and use those relationships to control node creation. This is what 'ExampleTree' does.
Can you describe in greater detail what you have in mind for 'Reporting?' It might be pretty easy to plug it into the previous example.

 

[tonidavid5]

lagbolt

I'm currently trying to learn about implementation of treeview for a new project and thought of giving a go at the sample you attached here since I'm relativily green on VBA coding. I'm on A2K and cannot open it because it says it may have been created in a newer version of Access (XP?). Would you mind posting a version of that quick & dirty sample compatioble with A2K?

Thanks in advance!

Cheers

 

[JordanR]

Lagbolt,
Best way I can explain it:

Cases
   Reviewer 1
      Case 1
      Case 2
   Reviewer 2
      Case 1
Reporting
   By Date
      All Cases by Date
      Closed Cases by Date
   By Reviewer
      All Cases by Reviewer
      Closed Cases by Reviewer
etc.

Basically you'd click on "All Cases by Date" or any of the other reports and it would generate that report (which I've already created. I'm just looking for a way to access it from a treeview).

 

[MarkK]

Jordan:
No, sorry. The structure you propose here, though cool, isn't dictated by any existing data structure, so there's no fast & dirty way to do the job. To have a date range as a parent node for 'case reporting', for instance, is tricky since the data that creates the parent node, say 'case date', is in the child node. In this sense you're driving data backwards up the tree, and the tree wants to flow the other way.
It's possible, but it'll melt your brain.
Here's a fast & dirty alternate solution though, in A2K this time.
There's a case overview form on the root node, a continuous form, and if you apply filters to it, those filters are applied to a report.
Cheers,

 

[Keith Nichols]

Here's a fast & dirty example tree. Requires a reference to mscomctllib.ocx to drive the tree control. Offers a few ideas on how you might proceed.

Hi Lagbolt,

The Form_frm_Tree won't run for me. Do I need to source "mscomctllib.ocx from somewhere first? I am using Access 2000 / Windows XP Professional.

I'm very new to code, so if the answer is obvious, please fogive me.

The code stalls at this point in the debugger:

*************
Private Sub CreateTree(sql As String, parentKey As String)
Dim rst As DAO.Recordset
Dim nd As MSComctlLib.Node

'open an r-set, whose definition is not know at run-time
'but it must have the two fields: Key, and Text
Set rst = CurrentDb.OpenRecordset(sql)
With rst
'traverse all its records
Do While Not .EOF
'adding a node for every one
Set nd = tvw.Nodes.Add(parentKey, tvwChild, !tKey, !tText)
'if this is a reviewer, then add cases
If Left(!tKey, 1) = "r" Then

Regards,

Keith

 

[MarkK]

You mean you got an error on the Left() function? In a code window, click menu item Tools->References and change the priority of one of the references. Not sure why this happens or why this works.
Mark

 

[ddrew]

Here's a fast & dirty example tree. Requires a reference to mscomctllib.ocx to drive the tree control. Offers a few ideas on how you might proceed.

I would like to incorporate this treeview into my DB, but Im not sure how to go about it. I can figure out haow to adapt it and I know how to get it in to mu DB. I saw that yopu can have checkboxes but how do I assign the code to that checkbox to write it to a table

 

[MarkK]

The Treeview raises a NodeCheck event that you can handle, signature is as follows ...

Private Sub tvw_NodeCheck(ByVal Node As MSComctlLib.Node)
  '
End Sub

...so it gives you the node that was checked.
When you create your tree, put enough info into the tag or key field of each node so you can determine what table and what record that node was created from. Then you can send updates to that's node's status back to the source table.
Does that make sense?

 

[Endre]

Hello, Have been working with treeview and tree structures for many years now and always use it for my GUI's.

A while ago I created an access database that displays the tree structures and front end automatically based upon your tables structures and relationships. All you need to do is point to the database where the tables and relationships are stored (usually backend) and it does the rest for you.

All the data is now fully maintainable. You navigate through the front end tree structure and mantain data through the forms it automatically generates for you. 2 forms are generated per table - one for single detailed entry (i.e. the node you have selected), the other for multiple line items for mass maintenance (i.e the child nodes belonging to node you have selected) It works just like most people configure their Microsoft Outlook.

If I don't use this method, and am creating a treeview GUI "manually", I also have a standard method of development which I use every time (undocumented of course!). For example my nodes only read the level of data required and only load the levels that you see. i.e I don't load the entire structure in one go (though I could) - this makes it very dynamic. This also solves recursive problems as you can now have a recursive tree structure. I also use arrays to store node tag data - you can hold a lot more information against a node this way e.g. if you have several key figures that exactly define a node.

Attached is what Northwind can look like. If you post / mail your tables I can test it for you.....

 

[ddrew]

The Treeview raises a NodeCheck event that you can handle, signature is as follows ...

Private Sub tvw_NodeCheck(ByVal Node As MSComctlLib.Node)
  '
End Sub

...so it gives you the node that was checked.
When you create your tree, put enough info into the tag or key field of each node so you can determine what table and what record that node was created from. Then you can send updates to that's node's status back to the source table.
Does that make sense?

Thanks for your reply, I will give it a go at work today and see how I get on!