[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

treeview from Access

Hello Guys,
                   This is a challanging one. I am wandering if it's possible to somehow set up a treeview in such a way that it gets node values from access tables. Would it be possible to use the relational database concept? Here is the problem.
 
                       I have two tables , tab1 and tab2. tab1 has 3 records representing processes a,b,c with one field holding these names. like
                               process
                                   a
                                   b
                                   c



Tab2 has 9 records representing 9 subprocesses - a1,a2,a3 for a     , b1,b2,b3 for b, and c1,c2,c3 for c.
looks like
               process         subprocess
                   a                  a1
                   a                  a2
                   a                  a3
                   b                   b1
                   b                   b2
                   b                   b3
                   c                    c1
                   c                    c2
                   c                    c3

tab1 has one to many relationship with tab2 thru field "process".

The question is, is there a neat way of using that relashinship and the tables to show on a treeview how the processes and subprocesses are related .


Thank you,
Serge







0
Autofreak
Asked:
Autofreak
  • 6
  • 4
  • 3
  • +1
2 Solutions
 
PreeceCommented:
See if you can modify this:

Public Sub gsnLoadTreeView(tvX As TreeView, ImageList1 As ImageList)
    Screen.MousePointer = vbHourglass
    Dim oRS As ADODB.Recordset
    Dim sProc As String
    'Dim lRecCount As Long
    Dim sCatName As String
    Dim sItemName As String
    Dim sURL As String
    Dim sKey As String
    Dim lX As Long
    Dim nodX As Node
   
    If tvX.Nodes.Count > 1 Then tvX.Nodes.Clear
    tvX.LineStyle = tvwRootLines ' Linestyle 1
    tvX.Indentation = 2
    tvX.ImageList = ImageList1
   
   
    'sProc = "Select c.CatName, i.ItemName, i.ItemDescription, i.ItemLink from tblCategories c left join tblItems i on i.fkCatID = c.pkCatID group by c.CatName, i.ItemName"
    sProc = "Select c.CatName, i.ItemName, i.ItemDescr, i.ItemLink"
    sProc = sProc & " from tblCategories c"
    sProc = sProc & " left join tblItems i on i.fkCatID = c.pkCatID"
    sProc = sProc & " group by c.CatName, i.ItemName, i.ItemDescr, i.ItemLink"
    sProc = sProc & " order by c.CatName, i.ItemName, i.ItemDescr, i.ItemLink"

    Set oRS = oAdoAccess.gfGetRS(sProc)
   
    'lRecCount = oRS.RecordCount
    lX = 1
    With tvX
        '.Style = tvwTreelinesText ' Style 4.
        '.BorderStyle = vbFixedSingle
        sCatName = ""
        If Not oRS.BOF And Not oRS.EOF Then
            oRS.MoveFirst
            Do While Not oRS.EOF
                If sCatName <> oRS!Catname Then
                    sCatName = oRS!Catname
                    Set nodX = .Nodes.Add(, , "-cat-" & sCatName, sCatName, 1)
                    nodX.Tag = sCatName
                    Set nodX = Nothing
                End If
                sItemName = oFU.gfIsNullSomeChar(oRS!ItemName, "")
                sURL = oFU.gfIsNullSomeChar(oRS!ItemLink, "")
                If sItemName <> "" And sURL <> "" Then
                    Set nodX = .Nodes.Add("-cat-" & sCatName, tvwChild, sURL, sItemName & " - " & sURL, 2)
                    nodX.Tag = sItemName
                    Set nodX = Nothing
                End If
                lX = lX + 1
                oRS.MoveNext
            Loop
        End If
        '.Nodes.Item(2).EnsureVisible
    End With
   
    oRS.Close
    Set oRS = Nothing
   
End Sub
0
 
eddiejCommented:
Does it have to be a node control? or can it just look like one?

a
     a1
     a2
     a3
b
     b1
     b2
     b3
c  
    c1
    c2
    c3

i could do this in a list box, or textbox in a continuous form.
don't know about treeview, i am going to have to try that one of these days.
eddiej

0
 
AutofreakAuthor Commented:
               I don't know  eddiej,
 In reality I have about 10 main processes and 100 subprocesses, The beauty of the treeview is that I can  choose the whole set shoosing the main node or just a few smal subs conveniantly collapsing the nodes. If I coild see how other options work I would be able to  tell . Anyway, if you provide an alternative solution I will not make you wait and award fair points.


Serge
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
eddiejCommented:
How about that it works
1 list box lsttree 2 coulumns, value list as source type
2 command buttons cmdopenall, cmdclosesub
and put this in the forms code
This can only open of close the list, so you are probably better of with the treeview.
But this is still interesting, I am sure that I will be able to use this somewhere.
This doesn't seem like a very good answer to your question, but once I thought of it I had to try just to see if I could.
eddiej

Private Sub cmdCloseSub_Click()
Me.lstTree.RowSource = OpenProcess

End Sub

Private Sub cmdOpenall_Click()
Me.lstTree.RowSource = OpenSub

End Sub
Public Function OpenSub() As String

Dim rstProcess As ADODB.Recordset
Dim strProcessSql As String
Dim rstSubProcess As ADODB.Recordset
Dim strSubProcessSql As String
Dim pi As Integer
Dim si As Integer
Dim strrowsource As String

'open process rst
Set rstProcess = New ADODB.Recordset
strProcessSql = "SELECT tblProcess.strProcess FROM tblProcess ORDER BY tblProcess.strProcess;"
rstProcess.Open strProcessSql, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

'open subprocess rst
Set rstSubProcess = New ADODB.Recordset
strSubProcessSql = "SELECT tblProcess.strProcess, tblSubProcess.strSubProcess " & _
                    "FROM tblProcess INNER JOIN tblSubProcess ON " & _
                    "tblProcess.strProcess = tblSubProcess.strProcess " & _
                    "ORDER BY tblSubProcess.strSubProcess;"
rstSubProcess.Open strSubProcessSql, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

For pi = 1 To rstProcess.RecordCount
    strrowsource = strrowsource & rstProcess(0) & ";"
   
If Not rstSubProcess.EOF _
    Then
        If rstProcess(0) = rstSubProcess(0) _
            Then
                strrowsource = strrowsource & ";;"
                    Do Until rstProcess(0) <> rstSubProcess(0)
                        strrowsource = strrowsource & rstSubProcess(1) & ";;"
                        rstSubProcess.MoveNext
                        If rstSubProcess.EOF Then Exit Do
                    Loop
                    'remove 1 ;
                    strrowsource = Left(strrowsource, Len(strrowsource) - 1)
               
        End If
    Else
        'no more subprocedures but still need ;
        strrowsource = strrowsource & ";"
End If
      rstProcess.MoveNext
Next pi

rstProcess.Close
Set rstProcess = Nothing
rstSubProcess.Close
Set rstSubProcess = Nothing
OpenSub = strrowsource

End Function

Public Function OpenProcess() As String

Dim rstProcess As ADODB.Recordset
Dim strProcessSql As String
Dim pi As Integer
Dim strrowsource As String

'open process rst
Set rstProcess = New ADODB.Recordset
strProcessSql = "SELECT tblProcess.strProcess FROM tblProcess ORDER BY tblProcess.strProcess;"
rstProcess.Open strProcessSql, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
 
For pi = 1 To rstProcess.RecordCount
    strrowsource = strrowsource & rstProcess(0) & ";;"
    rstProcess.MoveNext
Next pi

rstProcess.Close
Set rstProcess = Nothing
OpenProcess = strrowsource
End Function
0
 
MavreichCommented:
Hi Autofreak,

This is example code only and I have tested it to see that it works.  I am going to assume that you are using Access2000 or above.

In this example I have 2 tables.  
tblProcess  and
tblSubProcess

tblProcess has a single field called ProcessName with each value being unique.
tblSubProcess has 2 fields called SubprocessName with each value being unique and ProcessName.  Their exists a one to many relationship between the tables.

1 form called Form1 with a treeview control called TreeCtl1.
This code is called from the On Load Event of the form.

Form Code:

Option Compare Database
Option Explicit

Private Sub Form_Load()
Call BuildBranch
End Sub

1 Module with the following code.

Module Code:

Option Compare Database
Option Explicit

Function BuildBranch(Optional varParentID As Variant)
'On Error GoTo errOnBuild

    'Declare varibles
    Dim oTree As TreeView, nodProcess As Node
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
    Dim MySql1 As String, MySql2 As String
    Dim intID As String, intID1 As String
    Dim strText As String
    Dim strKey As String
    Dim strKey1 As String, strKey2 As String
   
    Set db = CurrentDb()
    MySql1 = "SELECT tblProcess.* " _
            & "FROM tblProcess " _
            & "ORDER BY tblProcess.ProcessName;"
   
    Set rs1 = db.OpenRecordset(MySql1, dbOpenDynaset)

    Set oTree = Forms.Form1!TreeCtl1.Object

'*************** PROCESS BUILD STARTS HERE**************************

    '[Processes] Build RootNode first
    strText = "Processes"
    strKey = "RootNode"
    Set nodProcess = oTree.Nodes.Add(, , strKey, strText)
    'Call ChkImg(nodProcess)

    '[ProcessName Sub Node off Processes]
    'loop through the recordset, adding a node for each entry
    'as a child to the Posted Audits Node
    rs1.MoveFirst
    Do Until rs1.EOF
       
    'Set the text to be displayed and the key.  Note that the key
    'MUST BE UNIQUE for every node in the tree
    strText = rs1!ProcessName
    strKey1 = "a|" & rs1!ProcessName
    Set nodProcess = oTree.Nodes.Add("RootNode", tvwChild, strKey1, strText)
    'Call ChkImg(nodContract)

    '[Subprocess Sub Node off Posted Audits]
    'loop through the recordset, adding a node for each entry
    'as a child to the Process Node
    intID = rs1!ProcessName
   
    MySql2 = "SELECT * FROM tblSubProcess WHERE ProcessName = '" & intID & "' " _
             & "ORDER BY SubProcessName DESC;"
   
    Set rs2 = db.OpenRecordset(MySql2, dbOpenDynaset)
   
    rs2.MoveFirst
    Do Until rs2.EOF

    'Set the text to be displayed and the key.  Note that the key
    'MUST BE UNIQUE for every node in the tree
    strText = rs2!SubProcessName
    strKey2 = "b|" & rs2!SubProcessName

    Set nodProcess = oTree.Nodes.Add(strKey1, tvwChild, strKey2, strText)
    'Call ChkImg(nodContract)
   
        rs2.MoveNext
    Loop
        rs1.MoveNext
    Loop

   
   'Set Nodes to be expanded on build
    For Each nodProcess In oTree.Nodes
        If nodProcess.Key = "RootNode" Then
            nodProcess.Expanded = True
        End If
     Next
     
    rs1.Close
    rs2.Close
    db.Close

End Function

Hope this example gives you an idea on how to accomplish what you want.

Regards
Mavreich



0
 
MavreichCommented:
Hi Autofreak

Code correction for line
'[Subprocess Sub Node off Posted Audits]

Should read
'[Subprocess Sub Node off ProcessName]

Doesnot affect the running of the code but I like for the information to be accurate.

Regards
Mavreich
0
 
eddiejCommented:
That worked much better than what I had.

I did get one error
rst2 is empty if there process has no subprocesses

rs2.MoveNext
change to
if not rs2.eof then rs2.movenext

Thanks for the knowledge Mavereich
good luck autofreak
eddiej
0
 
AutofreakAuthor Commented:
Hello Guys,
                 Sorry, month start - always hectic. I am going to work on this soon.

Thanks a lot
Serge
0
 
AutofreakAuthor Commented:
Hi Mavreich ,
 Do you know why I may be getting "Type mismatch" on the following line. ?????????? Thank you!


Set oTree = Form3.TreeView1.Object
0
 
AutofreakAuthor Commented:
I got it!
0
 
MavreichCommented:
Hi Autofreak

Glad to hear from you.   I take it the error was a typo of a missing reference.  Let me know how it goes.

Regards
Mavreich
0
 
AutofreakAuthor Commented:
Hey Mavreich,
                    That's what I wanted, Thanksa lot! Excellent
 I would also like to ask you a related Q while we are on the topic.
 Listen, I want to ensure that there's no conflicts among the choces on the tree. That is, I would like to avoid overlaping of choices. Namely
 1  If a process is checked all its subs are checked, the check box itself is white.
 2  if a subprocess is checked, but there are other subs in the process that are unchecked , then the main process stays checked, the checkbox is gray.
3  if no subs are checked then the main process is also unchecked.

How do I realize that? Unfortunately I can't up the points but I could repost it ..

Thanks
Serge
0
 
MavreichCommented:
Hi Segre,

Sorry for the delay in responding,  I have been travelling abit lately.

I do not fully understand what you are trying to achieve.  An idea would be to post this as a new question.  Where des the checkbox come in?  Are you using it in the treeview control?
With a treeview control all node.key values in the tree must be unique.

I look forward to helping you resolve this
Mavreich
0
 
AutofreakAuthor Commented:
Got you, Great Thanks to All.
Eddiej,
             I am going with Mavreich' s code - it provided me with What I was looking for, however, speaking of your version,  I will, like you've said "use it somewhere" else. Thanks!

Serge
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now