Solved

Treeview setup

Posted on 2010-09-22
13
415 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Sasha42
  • 7
  • 6
13 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33737086
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.
0
 

Author Comment

by:Sasha42
ID: 33737285
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?

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33737750
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.
0
 

Author Comment

by:Sasha42
ID: 33738496
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.
0
 

Author Comment

by:Sasha42
ID: 33745541
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33746178
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
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Sasha42
ID: 33748330
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33750545
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.
0
 

Author Comment

by:Sasha42
ID: 33754317
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33754349
cannot get to it until this evening, but will be glad to take a look.
0
 

Author Comment

by:Sasha42
ID: 33754537
I very much appreciate it!!
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 33755657
Actually found some time during lunch to look at this.

1.  I like the use of autonumber fields for the ID values for each table.  although this requires you to create queries to see the appropriate data, it is the best way to go  (although there are some purists that would argue for a natural key).

2.  tblProviders should not have CaseID or Case#.  This table should only have information that describes the provider.  Your investigative data table will provide the necessary linkage to CaseID and Case#

3.  You have the right idea with tblPeople.  Only include the information that is pertinent to that person.  You might also have tblPeopleAlias, tblPeoplePhone, tblPeopleFamily if you need to track any of that information (this configuration would allow you to track multiple phone numbers without adding multiple fields to the tblPeople).




TreeView-Puzzle2.accdb
0
 

Author Comment

by:Sasha42
ID: 33764502
Works like a charm!!!! Thanks so much.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now