asked on

Treeview setup

Uncle!! I have tried and tried to figure this out and I can't. I am building a database that tracks data on investigations. Attached is a database that has the table below and has the data in seperate tables. I am trying to get the tree to look like below. Note that under the HealthCareProviderNumber I would like it to return the name and not the number. I also need to be able to click on the different nodes and get their values. Thanks so much!!!

CaseNumber      HealthCareProviderNo      AssociationCatagory      PersonProviderName
Case 1      Provider-1      Provider      Local Hospital
Case 1      Provider-1      Witness      Sam Sleeze
Case 1      Provider-1      Subject      Jane Auditor
Case 1      Provider-2      Provider      A-One Clinic
Case 1      Provider-2      Witness      Imma Crook
Case 2      Provider-3      Provider      MedStop
Case 2      Provider-3      Witness      Dewey Cheatum


TREEVIEW
Case 1
      Local Hospital
            Witness
                  Sam Sleeze
            Subject
                  Jane Auditor
      A-One Clinic
            Witness
                  Imma Crook
Case 2
      MedStop
            Witness
                  Dewey Cheatum

Private Sub Form_Load()
Dim db As DAO.Database, rs As DAO.Recordset

Dim sSQL As String

Set db = CurrentDb

Set objfTree = Me.axtreeview.Object
objfTree.Nodes.Clear

    
    sSQL = "Select tblcases.CaseNumber From tblcases"
    
    Set rs = db.OpenRecordset(sSQL, dbOpenForwardOnly)
    
     Do Until rs.EOF
        Me!axtreeview.Nodes.Add , , rs!CaseNumber, rs!CaseNumber
        rs.MoveNext
    Loop
    rs.Close
    
 'fill Provider
 
    Set rs = db.OpenRecordset("tblProviders", dbOpenForwardOnly)
    Do Until rs.EOF
        strProvider = StrConv("o" & rs!HealthCareProviderNo, vbLowerCase)
        Me!axtreeview.Nodes.Add rs!CaseNumber.Value, tvwChild, strProvider, _
           rs!HealthCareProviderNo
        rs.MoveNext
    Loop
    rs.Close
    

End Sub

Open in new window

TreeView-Puzzle.accdb
SnapShot.png

Dale Fye

I'll try to take a look at your data this evening and get back to you. But basically, you have to nest these recordset loops within each other; not one after the next.

Start out with rsCase, where you select all of the case numbers.
Create a Loop to loop through the case numbers and add the Case# node to the tree

Then, inside that loop, create a new recordset (rsProviders) where it captures the Provider# and the Provider Name with a where clause that matches the current case # and where the AssociationCategory = "Provider"
Create a loop to loop through these records and add the ProviderName to the node. The problem you will have here is that each nodes key value must be unique, so you will not be able to use the ProviderName as the key. I usually use the ID (autonumber field) value associated with each record, prefaced by a letter code (A for root level, B for next, C for next, ...), but you don't show an ID value associated with your records, so I'm not sure whether you just want to manually generate that or not. Personally, I'd add the ID field and make it my tables PrimaryKey.

Inside that loop, and nested under the provider node, you need to add Witness and Subject nodes, and for each of these you need to generate another recordset to identify those individuals, so that you can loop through those names to add those nodes.

Ideally, you would have case data so that when you click on the Case1 node, you could display case specific data to the right of the treeview. If you clicked on the Provider, it would find that record in the Providers table and display information like address, phone #, points-of-contact, and any other pertinent information about the provider. Then, when you click on the Witness or Subject name, it would display information pertinent to that individual.

Sharon

ASKER

Thanks so much!! I can create loops, I just can't seem to create the nodes. Is there a way to generate the tree just from tblInvestigativeData in the database?

Dale Fye

Yes, that is what I would recommend, although if I were creating this database the fields in that table would be:

ID- Autonumber
CaseID - Long Integer: linked to CaseID in tbl_Cases
ProviderID - Long Integer: linked to ProviderID in tbl_Providers. Might need something else in here to identify the provider sequence (if that is important).
AssociationID - Long Integer: linked to AssociationID in tblAssociations which would contain at least one text field for AssociationCategory with values (Provider, Witness, Subject)
PersonID - LongInteger: linked to PersonID in tblPeople, which would contain basic information about each person, mainly a contact information table.

Sharon

ASKER

Yes, I agree with the IDs. However, the organization I am working for creates a CaseId using a case type prefex and then adding the date. I guess that should really be the CaseName instead of CaseID. Same with ProviderID and ProviderName.

Sharon

ASKER

I have added fields and tables as you suggested above into a new database. I really apperciate your help!!!!
TreeView-Puzzle-v-2.0.accdb

Dale Fye

OK, I had a chance to look at this, and think you probably need to do some work on your table structure. We can do this in a separate question. Attached is my modification to your database, based on your current structure.

1. You should add the MSCOMCTL.OCX reference to your project. To do this, go to Tools, References, click the Browse button, change the combo box to include the OCX extension, then select MSCOMCTL.OCX from the list. If the list isn't already pointing to the C:\Windows\System32\ folder, point it there first.

2. Category is spelled with an "e", not an "a". Don't you just wish there was a good Access Spell Checker?

3. Need to be consistent across table with field naming. You have [CaseNumber] in tblCases and [Case Number] in tblInvestigativeData. Personally, I prefer without the spaces, or to use _ in-lieu-of a space.

The code spends a lot of time concatenating values to come up with node keys, which, if you had a better table structure you would not need to do. I strongly recommend that you spend some time defining what the application needs to do, and then designing a normalized data structure, which will make working with your data much simplier. As an example, I don't understand why your tblInvestigativeData contains the [Health Care Provider No] field. What does that field have to do with subjects or witnesses?
TreeView-Puzzle.accdb

Sharon

ASKER

Thanks so much!!! You are right about the normalization. Attached is a version that is more normalized. I took the Provider info out of the tblInvestigationData. The Provider is the second level. See below:

This project is for an organization that doesn't have the funding to buy an off the shelf software that doesn't quite meet their needs. They want to have a SQL Server backend with an Access frontend. I am going to build it in Access first and then migrate the backend. There are hundreds and hundreds of cases and up to 100 people will be hitting it at one time. They are aware that Access/SQL Server is not an ideal platform to build this on.

When an Investigator opens up the database and logs in, the frmInvestigatorsCases will open and show only that Investigator's cases based on userid. I haven't worked with a treeview before and my concern is the it will take a long time to load. How can I help you get more points for helping me on this. Thanks again!!

Case1
Local Hospital
Witness
Sam Sleeze
Subject
Jane Auditor
A-One
Witness
Imma Crook
Case2
MedStop
Witness
Dewey Cheatum

TreeView-Puzzle-Normalized.accdb

Dale Fye

don't worry about additional points.

I enjoy working with trees, although they can sometimes be a headache, and if you try to load too much at once, it can take a while.

One thing you can do is turn screen updating off while it is loading the tree (Docmd.EchoOn False). But if you do this, you must have an error handler and turn screen updating back on in the error handler and at the end of the tree build process.

Another option is to not load the node data until it is needed. So, you would start by only loading the case#s belonging to that investigator (or for a manager, possibly all cases, or you might start with a combo box to allow them to select the appropriate investigator). There would be no plus signs to indicate data at a lower level. In the node_Click event (not all the events are listed in the trees property dialog, you can see the entire list by selecting the tree in the VB IDE control dropdown and then expanding the control events dropdown). You would display information about the case, and then load the other nodes that are associated with that case.

Sharon

ASKER

Thanks for your thouights. The code you sent works great. But..... I normalized the tables and I can't rewrite the code to make it work. Other than normalizing the tables, the Provider needs to be the second level. I have tried all kinds of things and can't get it to work. Will you take a look? The file is attached. Thank you, thank you...,

Case1
Local Hospital
Witness
Sam Sleeze
Subject
Jane Auditor
A-One
Witness
Imma Crook
Case2
MedStop
Witness
Dewey Cheatum
TreeView-Puzzle-Normalized.accdb

Dale Fye

cannot get to it until this evening, but will be glad to take a look.

Sharon

ASKER

I very much appreciate it!!

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharon

ASKER

Works like a charm!!!! Thanks so much.