building tree views from a table and user interface design

[obiron]

Hi guys,



first post here....

I have 47 business units to report on. For some reports these units need to be aggregated together but the aggregations are different for different report types. I am fine with creating a table to hold the parent-child relationship:

ID,
AggregateTypeID,
ParentUnitID,
ChildUnitID,
sortOrder....


What I need is some guidance on the best way to present this information and to graphically build the trees. In a perfect world, I would choose an aggregation type and then present the tree as is as a series of folders and drag and drop each unit into its group folder so that it works like moving folders around in windows explorer left hand pane.

Can I do this in Access 2007?. The database is actually a set of MSSQL2000 linked tables (soon to be MSSQL2005)

acceptable constraints are:
The root of each node is a business unit (i.e. all BUs report into a single BU)
each node only goes one level deep (recursive navigation is therefore not required)

[alansidman]

Scroll to the bottom of this link and there is a tutorial on building Tree Views in Access.

http://www.datapigtechnologies.com/AccessMain.htm

Alan

[obiron]

Alan,

Thanks for that, very helpful in terms of the OCX controls that need to be added to the project. The demo takes a database and builds a tree view table from the current hierachy. I want to do it the other way round.
I have 47 top level nodes and want to convert some of them to child nodes. I can do the VBA to build the table, but how would you go about the drag and drop process a bit like windows explorer.

Can I drag form elements around the form in view mode using mousedown?
If I can, can i measure the XY coordinates on mouseup and compare them to other form elements. If so then I can store the order in the database.

Or am I making it too complicated?

[alansidman]

This is beyond my knowledge base. I knew about the site and hoped it would be helpful. Perhaps, someone with experience in this area will chime in. Good luck.