Hi,

I hope this is in the right section, it is form related but is i think more about design principles than an out right question of "how do i ..."

I wondered if anyone has any thoughts on the best way of populating treeview controls?

I'm about to embark on a re-development of a reporting system that currently has 20+ access database. My intention is to streamline this into one.

I'd like to use the treeview control to maintain the objects - may be forms that filter reports or reports by themselves - in a hierarchy that reflects the business but do it in such a way as to be as flexible as possible with regards to the potential number of nodes.

My initial idea is to use a property of the object[possibly its name though not ideally] to reflect it's postion in the hierarchy with a related table to clarify the textual element. ie.

Rpt_1_2_rpt_name
Frm_1_3_frm_name

The table would contain

1||Sales
2||Month End
3||Year End
n||text

Which would give

Sales [parent]

¬ Month End - Report Name [Child]
¬ Year End - Form Name [Child]

I could then do a poll of all the relevant objects in the database and generate the treeview against the table. I believe i could have n nodes using this method.

The limiting factor may be the amount of text available for the property i use to store the objects position. i.e. if report names are limited to x number of characters that would limit the potential number of nodes i could use.

I also believe i could add a facility that would quickly generate names for new objects based on selection of the place they would sit in the existing hierarchy from the tree view.

My primary concern is that if objects are moved or layers are added to the business hierarchy above the object then the objects name/property would need to be altered accordingly.

I suspect i could do this via the treeview control itself with some judicial use of VBA. I can't think of any system that would not require some form of manual or programmatic intervention in this case.

New nodes that reflect business entities rather than reports can be added to the treeview table as and when they are added to the control itself, again by VBA.


If anyone has any bright ideas, comments or constructive critisim about the principles I would love to hear about them.

Thanks for your time

Karma

After much messing around i've found a decent way of doing this.

You need two tables.

TBL_NODES
NODEID
NODEDESC

TBL_OBJECTS
OBJECTID
NODES
OBJECTREALNAME
OBJECTDISPLAYNAME

TBL_NODES is simply a list of all the nodes that you want to be displayed on the tree not including the names of the form and report nodes.

TBL_OBJECTS contains the nodes that refer to the reports and forms as well as a denormalised field containing a delimited list of the nodes that are parents of the object.

e.g

TBL_NODES
NODEID--NODEDESC
1--------SALES
2--------MONTHEND

TBL_OBJECTS

OBJECTID---NODES---OBJECTREALNAME------OBJECTDISPLAYNAME
1---------- |1|2|----FRM_MONTHENDSALES--MONTH END SALES FIGURES

When the tree is created i use a function to parse the TBL_OBJECTS.NODES field. - i used the | character to delimit the nodes. Each node is then selected from TBL_NODES and the tree is created. The final node uses the Field OBJECTREALNAME as it's key - the name of the form or report - and the OBJECTDISPLAYNAME as the text shown to the user. This means when the node of a report or form is clicked i can use some code to open up the object.

In this way i can create as many nodes on the tree as i like, i can also use a form or report in more than one branch of the tree and give it a different display name if the context is different- useful for dynamic reports. I can select a list of nodes in any order so i can use |1|2| or |2|1| which means adding new parent node above an existing node string is simple as it's not reliant on any order in TBL_NODES.

K