Treeview assistance requested 2

[directorz]

Hello experts,

I’m stumbling with a treeview. I’ve created my first one and have it populated. I have difficulty in understanding how to associate the value that a node represents to a record…let me elaborate.

The db I’m working on focuses on periodic maintenance of cars. Maintenance schedules can be assigned to each car like preventive maintenance 'a'(PM-A) or preventive maintenance 'b' (PM-B) and so on. Each PM is broken down into intervals such as interval 1, interval 2 etc. So, for example, PM-A / Interval 1 might be items that occur every 3000 miles, PM-B might be items that occur every 6000 miles etc. This arrangement is represented by a treeview and it’s nodes:

PM-A
l
l
l---Interval 1
l
l---Interval 2
l
PM-B
l
l
l---Interval 1
l
l---Interval 2

While entering data in a form relative to a car, I can call the treeview. What I need to understand is how to associate or ’assign’ the value of a node, say interval 1, to that car. I would like to dblclick the node and have that value placed into the record of the car. Could someone assist.

Thank you

Directorz

 

[FancyPrairie]

Little unsure as to where you're having trouble. So here's a start (Assume the name of the TreeView is TreeView1) In the OnClick event add the following.

Private Sub TreeView1_Click()

Dim idx As Integer
Dim nod As Node

idx = TreeView1.SelectedItem.Index

Set nod = TreeView1.Nodes(idx)
Msgbox Nod.Key
Msgbox Nod.Tag

End Sub

 

[directorz]

FancyPrairie,

After (too many) hours, I've created a treeview using the following code adapted from Scott Barker's 2002 Power Programming;

Private Sub form_load()

Dim objCurrNode As Object '

Dim rstPMS As New ADODB.Recordset
Dim rstINTERVALS As New ADODB.Recordset
'-- Open the necessary recordset
rstPMS.Open "query1", CurrentProject.Connection
rstINTERVALS.Open "query3", CurrentProject.Connection

'--Loop through the categories
Do Until rstPMS.EOF

'--Add a top node (Category)
Set objCurrNode = Me!TreeView0.Nodes.Add(, , _
"Category" & CStr(rstPMS!CategoryCode), rstPMS!Description, 1)

'--Establish which image to use for an expanded branch
objCurrNode.ExpandedImage = 1

If Not rstINTERVALS.EOF Then

'--Add a branch to the current top node (Category)
Do While rstINTERVALS!CategoryCode = rstPMS!CategoryCode

Set objCurrNode = Me!TreeView0.Nodes.Add( _
"Category" & CStr(rstPMS!CategoryCode), 4, , rstINTERVALS!Title, 1) 'Could use tvwChild instead of 4 if reference is made

rstINTERVALS.MoveNext

If rstINTERVALS.EOF Then
Exit Do
End If

Loop
End If
rstPMS.MoveNext
Loop

Me!TreeView0.Refresh

End Sub

So, I have a tv. Viewing data is great but I'm looking for direction in making the the control functional. Currently, dbl clicking a node does nothing nor does a right click. I applied your sample and now get an "OK" on click of the node. How can I take a node like 'interval1' and say OK, I want to assign this interval 1 to a car(or I want interval 1 to be a part of the record of a car. Is there a properties of the node? I know this is as tough to envision as it is to describe with words. I did a debug.print Me!TreeView0.SelectedItem and get zeroes on all nodes clicked.
Directorz

 

[FancyPrairie]

1st declare a variable like this:

Dim objTreeView as TreeView

This declaration is just so that you will be able to see the properties and methods available to you (via objTreeView).

A node object contains several properties. The Add Method defines 2 of the properties: 1) Key; 2) Title. You can also define the tag property.

Consequently, in the onclick event you're only seeing "OK" because you have not defined the Key and Tag properties.

Try changing you code to this (Note that I added i to ensure that the key is unique).

Dim i as integer
....
....
i = 0
....
....
i = i + 1
Set objCurrNode = Me!TreeView0.Nodes.Add( _
"Category" & CStr(rstPMS!CategoryCode), 4,rstINTERVALS!Title & i , rstINTERVALS!Title, 1)

objCurrNode.Tag = "Whatever you want"

After you made the changes, you should see (upon clicking the node) the Title (concactenated with i) and the Tag ("Wherever you want").

You should be able to go from there.

 

[directorz]

FancyPrairie,
Way over my head, please pardon my inexperience. Could you clarify where I am to modify.

Thank you

Directorz

 

[FancyPrairie]

It's kind of hard to show you when I don't have the layout of your tables. So I've developed a procedure that will display the name of your database, list all tables within the database and all fields within each of the tables. When you select one of the fields, the program will tell you the unique key assigned to that field, the title of the field and display the contents of the tag property (which happens to be the code for the field type (i.e. string, double, etc.) Note that nothing is displayed if the user select the database node or the table node.

Also, note that each node is assigned a unique key. Since several tables may have a field with the same name, I append a number to the key to force it to be unique.

The results of the tree would look something like this:

YourDatabaseName.mdb
Table1
Field1
Field2
Table2
Field1
Field2
Field3

To make it work, copy and paste the two procedures below. In the OnOpen event of the form, call BuildTreeSimple. In the OnClick event of TreeView0 add the the code shown.

Warning! When I tested my code, my treeview control was named TreeView1. I noticed yours was TreeView0, so I tried to change it, but may have missed one or 2.

Function BuildTreeSimple()

    Dim cnn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    
    Dim nodDatabase As Node               'Parent node
    Dim nodTable As Node              'Current node
    Dim nodField As Node              'Current node
    
    Dim k As Integer                    'Working variable
    Dim j As Integer
    
    Dim strKey As String                'Unique key for tree structure
    Dim strDbsName As String
    Dim strTitle As String
    
'****************
'*  Initialize  *
'****************

    On Error GoTo ErrHandler
    
    Set cnn = New ADODB.Connection
    Set cat = New Catalog
    
    j = 0
    k = 0
    
    strDbsName = CurrentProject.Name
    cat.ActiveConnection = CurrentProject.Connection
        
'*****************************
'*  Create Parent (Database) *
'*****************************

    strKey = "Database=[" & strDbsName & "]"
    strTitle = strDbsName
    
    Set nodDatabase = TreeView0.Nodes.Add(, , strKey, strTitle)
    
    nodDatabase.Image = 1
    nodDatabase.Tag = "This is the parent: " & strDbsName
    
    '********************************
    '*  Create Child Node (Tables)  *
    '********************************

    For Each tbl In cat.Tables

        k = k + 1
        strKey = "Table=[" & tbl.Name & "]" & k
        strTitle = tbl.Name
        
        Set nodTable = TreeView0.Nodes.Add(nodDatabase, tvwChild, strKey, strTitle)
        nodTable.Image = 1
        nodTable.Tag = "This is the table [" & tbl.Name & "]"
    
        '**************************************
        '*  Create child-child node (Fields)  *
        '**************************************
        
        For Each col In tbl.Columns
            j = j + 1
            strKey = "Field=[" & col.Name & "]" & j
            strTitle = col.Name
            
            Set nodField = TreeView0.Nodes.Add(nodTable, tvwChild, strKey, strTitle)
            nodField.Image = 1
            nodField.Tag = "Field Type = " & col.Type
        Next
    Next

ExitProcedure:

    Exit Function
    
ErrHandler:

    MsgBox "BuildTreeSimple: " & Err.Number & vbCrLf & Err.Description
    Resume ExitProcedure

End Function

OnClick Event

Private Sub TreeView0_Click()

    Dim idx As Integer
    Dim nod As Node
    
    idx = TreeView0.SelectedItem.Index
    
    If (Left$(TreeView0.Nodes(idx).Key, 6) = "Field=") Then
        Set nod = TreeView0.Nodes(idx)
        MsgBox "Key: " & nod.Key
        MsgBox "Title: " & nod
        MsgBox "Tag: " & nod.Tag
    End If
    
End Sub

Now I know this doesn't answer you question, but it may give you a start as to where and how things are stored. As you can see, based on what you stored in the Key, Title, or Tag property will help you in updating like you want. For Example, suppose the tag property contains what you need, then you might right a SQL statement (within the Onclick event) to do something like this:

Docmd.RunSQL "Update..." & nod.Tag & "..."

 

[directorz]

FancyPrairie,
I've uploaded a sample file which should clarify my issue. Using Internet Explorer, go to Geocities.com. The login username is directorz2002 and and the password is ticket, everything lower case. The filename is specialdb. Save it to your hard drive, open it and check out the treeview and table arrangement. It's quite a different arrangement than

YourDatabaseName.mdb
Table1
Field1
Field2
Table2
Field1
Field2
Field3

Here I believe you will see the goal. You may have to open the tv form twice to see it correctly.

Thank you,

Directorz

 

[FancyPrairie]

Finally had a chance to look at it. I'm still unclear on how you have the "car" thing laid out. None of your tables refer to a car. But, assume you have a table called tblCar, containing the following fields:

strCar Text (50 chars)
TitleNo Text (15 chars)
Title Text (15 chars)

Here's how the Form_Load event should look:

Every programmer has their own way (style) of coding. I always have to set it up my way so I can see it easier, hence the change to the style.

Private Sub Form_Load()
    
'********************************
'*  Declaration Specifications  *
'********************************

    Dim nodCat As Node              'Node for Category
    Dim nodCatCode As Node          'Node for Category Code
    
    Dim i As Integer                'Working variable
    
    Dim strKey As String            'Unique ID (key) for the node
    Dim strTitle As String          'Title (name shown on tree)
    
    Dim rstPMS As New ADODB.Recordset
    Dim rstINTERVALS As New ADODB.Recordset
    
'***********************************
'*  Open the necessary recordsets  *
'***********************************

    On Error GoTo ErrHandler
    
    rstPMS.Open "query1", CurrentProject.Connection
    rstINTERVALS.Open "query3", CurrentProject.Connection
    
'*********************************
'*  Loop through the categories  *
'*********************************

    i = 0
    
    Do Until rstPMS.EOF
    
        '**********************************************
        '*  Build Category Node (i.e. A-PM, B-PM...)  *
        '**********************************************
        
        strKey = "Category=" & CStr(rstPMS!CategoryCode)            'Uniquely identifies the node
        strTitle = rstPMS!Description                               'Title shown up on Tree View
        
        Set nodCat = TreeView0.Nodes.Add(, , strKey, strTitle, 1)
        nodCat.ExpandedImage = 1
    
        '******************************************************************
        '*  Loop to build Interval Node (i.e. Interval 1, Interval 2...)  *
        '******************************************************************
        
        If Not rstINTERVALS.EOF Then
    
            Do While rstINTERVALS!CategoryCode = rstPMS!CategoryCode
        
                i = i + 1
                strKey = "Code=" & rstINTERVALS!title & "_" & i     'Uniquely identifies the node
                strTitle = rstINTERVALS!title                       'Title shown up on the tree
                               
                Set nodCatCode = TreeView0.Nodes.Add(nodCat, 4, strKey, strTitle, 1)   'Could use tvwChild instead of 4 if reference is made
            
                rstINTERVALS.MoveNext
            
                If rstINTERVALS.EOF Then Exit Do

            
            Loop
        End If
        
        rstPMS.MoveNext
    Loop
         
'********************
'*  Exit Procedure  *
'********************
 
ExitProcedure:

    Exit Sub
    
'****************************
'*  Error Recovery Section  *
'****************************

ErrHandler:

    MsgBox "(Form_Load) Err.Number = " & Err.Number & vbCrLf & Err.Description
    Resume ExitProcedure
    
 End Sub

Here's how to handle the double-click event

Private Sub TreeView0_DblClick()

'********************************
'*  Declaration Specifications  *
'********************************

    Dim idx As Integer
    Dim nod As Node
    Dim strCar As String
    
'****************
'*  Initialize  *
'****************

    On Error GoTo ErrHandler
    
    idx = TreeView0.SelectedItem.Index          'Item user selected
    
    Set nod = TreeView0.Nodes(idx)              'Easier to refer to it as nod rather than TreeView0.Nodes... also a little quicker reponse time
    
'***********************************************************************
'*  Did the user double-click on the correct node (i.e. Interval...)?  *
'***********************************************************************

    If (Left$(nod.Key, 5) = "Code=") Then
        MsgBox nod.Parent
        MsgBox nod
        DoCmd.RunSQL "Insert Into tblCar (strCar, TitleNo, Title) values ('" & strCar & "','" & nod.Parent & "','" & nod & "')"
    End If
    
'********************
'*  Exit Procedure  *
'********************
 
ExitProcedure:

    Exit Sub
    
'****************************
'*  Error Recovery Section  *
'****************************

ErrHandler:

    MsgBox "(TreeView0_DblClick) Err.Number = " & Err.Number & vbCrLf & Err.Description
    Resume ExitProcedure
    
End Sub

By the way, I believe you could do what you want with 1 query rather than two. Also, take advantage of Access. That is, when defining a field in a table, enter as much info as possible (i.e. Description, caption, lookup...this info will be copied over to the form, reports, etc). Finally, at the beginning of the module, always include the statement "Option Explicit" forcing you to declare all variables. This will save you time debugging your code (i.e. misspelled a variable name)

 

[directorz]

FancyPrairie,
Check the upload agian, my error. Yes, there is another table for vehicles which is where my problem lies....assigning node values to a vehicle. I will look at your stuff tonite...appreciate your valuable time.

Directorz

 

[directorz]

FancyPrairie,
Started running this morning. I'm getting 'Compile error: user defined type not defined. In the Declarations Specs of the Form_Load, nodCat As Node is highlited

Directorz

 

[FancyPrairie]

You will need to set a reference to "Microsoft Windows Common Controls 6.0". If it doesn't show up in the list then browse for it at c:\Windows\System32\MSCOMCTL.ocx.

Let me if you understand what's going on. If you can't figure it out I will download the "new" version of your db.

 

[directorz]

Yes I understand and have made the reference. Your assumption is correct. An additional table is in the mix...TableVehicles. Here, info is entered relative to vehicles ie; Dept., VIN, etc. So lets say that TableVehicles has been populated with info about vehicle #901. After that occurs, at some point, a user will want to set up PM's (or assign PM's) for to that vehicle . That's where I'm stuck.

Directorz

 

[directorz]

Hold the phone, I think I need to make some modifications to tablevehicles

 

[FancyPrairie]

What you need is a one-to-many relationship (if I understand you correctly). That is, one vehicle has 0 or more PMs assigned to it. Consequently, you would need a form (recordsource equals TableVehicles) and a subform (recordsource equals new table). Now the Treeview looks nice but would get a little more complicated to do. You might want to rethink it. Instead of using the Treeview, use comboboxes. It would look something like this:

main form (form view - only 1 record shown at a time however user can switch to datasheet view)
Vehicle ID: 907
Dept: IS Dept
blah blah blah

Subform (Datasheet view - 2 records shown here
pm1 interval 1 (this is the value in a combobox)
pm1 interval 2 (this is the value in a combobox)

 

[directorz]

That is correct, 1 vehicle can have 1 or more PM's assigned to it, but not more than 1 interval per PM. Thus, Car'A' can have PM1 / interval 1 but not PM1 / Interval 1 and 2. Additional intervals needed would be handled by adding another PM and interval like PM7 / interval 1. Make sense?

Also, I would store info about the PM's in the PM schedule table rather than the vehicle table. Your arrangement seems to work...I need to explore a bit more

Directorz

 

[directorz]

FancyPrairie,
I re-worked some things here. I see how the values of nodes are assigned. When I attempt to append to the pmschedule table, I get a response " Can't append, Access set 1 field to null due to a type conversion failure." The only field I believe is suspect is the ID field. Yet ID and UnitID are both number types. Where else should I be looking.

Directorz

 

[FancyPrairie]

I believe the problem is that UnitID is one of your primary keys. A primary key can not be null. You're going to have to set it to something. If you don't care what it is, define it in the table as AutoNumber (then you don't have to worry about setting it)

 

[directorz]

FancyPrairie,
I've uploaded a modified version of where I'm at. Included is a simple form and modified coding. I've reviewed and tested and do not see why the table PMSchedule is not being updated. Field data types are the same and I'm not duplicating a value in a unique index field. Go into FrmVehicles and try to add a PM...you'll see where I'm at. Please have a look when you have time

Thank you
Directorz

 

[directorz]

One clarification.. we are using "ID" from the vehicles table, associating that with PM info from the PMInterval table and storing the info in the PMSchedule table.

 

[PHV]

In TreeView0_DblClick() I see at least 3 problems:
The variable UnitID is Dim'ed as String
The variable UnitID is never assigned to the TableVehicles.ID value needed for the relation.
The field UnitID is inserted surrounded with single quotes

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244