Is there an Access control for hierarchical data (folder trees)?

[Guest]

I have data in a table, where each row includes a "parent" pointer. This
allows me to build a hierarchy (in this case of product categories). I want
to display this data as a "tree" similar to the way windows display folders
in explorer. The user should be able to expand and collapse elements --
thereby drilling down the tree. Is there an existing component that would
help me to do this?

 

[Albert D.Kallal]

There is not a native control in ms-access.

You can use an activeX control, and the contorl is called "treeview"

there is info here:

ACC2000: How To Fill a TreeView Control Recursively
http://support.microsoft.com/?id=209891

ACC2000: Example Using TreeView Control Drag-and-Drop Capabilities
http://support.microsoft.com/?id=209898

ACC2000: How to Migrate from Data Outline Control to TreeView Control
http://support.microsoft.com/?id=209927


However, if you use a activeX control, then you need to supply a installer
routine to correctly install and register the control. this install feature
is not part of regular ms-access.

 

[Guest]

I built an application to do something very similar to what you describe.
It does not display a tree but it enables you to drill down and drill up
each leaf of the tree.
You can also add leaves and new levels.
It was surprisingy tricky to develop (took about 3 weeks) because handling
that type of 'linked list' is not easy in a SQL query.
If you want more details, let me know.

Dorian

 

[David C. Holley]

Check out the Access ActiveX TreeView Control. Implementing a basic view
of heiracrhical(sp) data is quite simple.

 

[David C. Holley]

The code below will load up the TreeView to display the following nodes...

RETURN TO ACCOUNT INVOICING
MENU OPTIONS
Find Client
Find Master Account
Find Transfer
2004
2005
2006
ARCHIVE
ABC Company
Invoice 1
Invoice 2
Invoice 3
Invoice 4

The code to display multiple companies & their invoices isn't too
difficult to understand, but a bit difficult for me to post here.

Sub loadTreeView_Archive(frmName As Form, lngAccountID As Long)

Dim accountHeaderNode As Node

frmName.ExplorerPane.Nodes.Clear
Set treeHeaderNode = frmName.ExplorerPane.Nodes.Add(, , , "Return
to Account Invoicing", "backArrow")
treeHeaderNode.Bold = True
treeHeaderNode.Key = "07MENU:BACK"
treeHeaderNode.Tag = "menuNode"

Call loadTreeView_Menu(frmName)

Set listHeader = frmName.ExplorerPane.Nodes.Add(, , , "Archive",
"archive")
listHeader.Key = "06N/A:NoAction"
listHeader.Tag = "noAction"
listHeader.Expanded = True

strAccountName = DLookup("txtMasterAccountCompanyName",
"tblMasterAccounts", "lngMasterAccountId = " & lngAccountID)
Set accountHeaderNode = frmName.ExplorerPane.Nodes.Add(listHeader,
tvwChild, , strAccountName, "AccountNode")
accountHeaderNode.Key = "09MASTER" & lngAccountID
accountHeaderNode.Expanded = True

Call loadInvoicesForAccount(frmName, lngAccountID, accountHeaderNode)

End Sub

Sub loadInvoicesForAccount(frmName As Form, lngAccountID As Long,
accountHeaderNode As Node)

'Get the SQL statement for the .OpenRecordSet method here
'Its pretty standard so not included in this newsgroup post
strSQL =
getAccountsToBeInvoicedBySQL("(tblMasterAccounts.lngMasterAccountID = "
& lngAccountID & ") AND tblInvoiceHeaders.txtStatus='Paid'")

Debug.Print strSQL

'Loop Through the Record Source and create 1 node for each Invoice
attached to the account
Set rsSource = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
While Not rsSource.EOF
Set newInvoice =
frmName.ExplorerPane.Nodes.Add(accountHeaderNode, tvwChild, ,
rsSource!txtDescription, "InvoicePaid")
With newInvoice
.Key = "10INVOICE:" & rsSource!dblInvoiceBatchNumber
.Tag = "invoice"
End With
rsSource.MoveNext
Wend

End Sub

Sub loadTreeView_Menu(frmName As Form)

Set treeHeaderNode = frmName.ExplorerPane.Nodes.Add(, , , "Menu
Options", "listHeader")
treeHeaderNode.Key = "08NOACT:TREEHEADERNODE"
treeHeaderNode.Expanded = True
treeHeaderNode.Bold = True

'MARK - Added for CL Conversion
Set actionNode = frmName.ExplorerPane.Nodes.Add(treeHeaderNode,
tvwChild, , "Find Client", "find")
actionNode.Key = "07MENU:FindClientAccount"
actionNode.Tag = "menuNode"

'MARK - Added for CL Conversion
Set actionNode = frmName.ExplorerPane.Nodes.Add(treeHeaderNode,
tvwChild, , "Find Master Account", "find")
actionNode.Key = "07MENU:FindMasterAccount"
actionNode.Tag = "menuNode"

Set actionNode = frmName.ExplorerPane.Nodes.Add(treeHeaderNode,
tvwChild, , "Find Transfer", "find")
actionNode.Key = "08NOACT:FindTransport"
actionNode.Tag = "menuNode"
actionNode.Expanded = True

strSQL = ""
strSQL = strSQL & "SELECT DISTINCT DatePart('yyyy',[dteDate])
AS exprYear FROM tblTransports "
strSQL = strSQL & "ORDER BY DatePart('yyyy',[dteDate]);"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
If rs.EOF = True Then
frmName.ExplorerPane.Nodes.Remove actionNode.Key
Else
While Not rs.EOF
Set childNode =
frmName.ExplorerPane.Nodes.Add(actionNode, tvwChild, , rs!exprYear & "
Transfers", "reservations")
childNode.Key = "07FIND:" & rs!exprYear
childNode.Tag = "menuNode"
rs.MoveNext
Wend
End If
Set rs = Nothing
End Sub