Simulating MS Project Type Hierarchical Views of data (1 Viewer)

[ak_ls]

I have few applications in MS Excel using VBA. These applications simulate Hierarchical Views of the data with following characteristics.

1) Indented values in a Column simulating a tree view of rows
2) Group level rows have formulas to sum or other calculations
3) Inrease or decrease to indenting of column values and shrinking or expending of group level rows behaves like group level tasks in MS Project.
4) Rows structure with data and formulas can be copied or inserted or moved.
5) Inserting of new row may change a row from simple to group type.
6) .....


I have to migrate these MS Excel based applications to Access adp project with SQL Server as database.

I am looking for the Form Controls ( or combination of Controls) that can be used in Access adp to meet these requirements.

Thanks
Ashok

 

[Pat Hartman]

Take a look at the TreeView control.

 

[jal]

I've never used the TreeView, but I have some notes in my notebook because a poster named Lagbolt provided an example a while back. Here are my notes (sorry, they are very sketchy).

Click the "More Tools" area of the toolbox.
Scroll down to Microsoft TreeView Control Version 6
Drop it onto your form.
Name it TreeViewDropped

Put this line at the top of your code:

Private WithEvents treeView1 As MSComctlLib.TreeView 'Global variable

And in your Form_Load do this:

Set treeView1 = Me.TreeViewDropped.Object

(Use simialar code to create an image list - begin with ImageListDropped).

To add a node, use the Text property (the fourth parameter)

treeView1.Nodes.Clear

treeView1.Nodes.Add , , , "This is the root node."

To add a child node to a particular parent node

Dim childNode as MSComctlLib.Node

set childNode = treeView1.Nodes.Add(parentNode,tvwChild,, "Child node"

The third parameter is apparently a dictionary-key - you can retrieve a node by its key, if you specify a key for each node.

set childNode = treeView1.Nodes.Add(parentNode,tvwChild,"Pretend this is a key", "Child node"

For example to retrieve a node by its key
treeview1.Nodes(stringKey)

The fifth parameter is the image index

set childNode = treeView1.Nodes.Add(parentNode,tvwChild,"Pretend this is a key", "Child node", 3

A node has children

parentNode.Children

When you drop the imageList on the form, you can right-click it and choose

ImageList Control Object

A small window will appear, with a button for browsing to your images.

 

[ak_ls]

Hi jal,

I undersatnd that Three View control can create hierarchical views of one column data.

How custom columns be added to a Tree View Control?
How to simulate the group like task for tree view nodes?


Ashok

 

[Banana]

Are you looking for something more like a spreadsheet as a data entry form? (I've not used Projects, so only have a vague notion of what you're looking for)

 

[jal]

Hi jal,

I undersatnd that Three View control can create hierarchical views of one column data.

How custom columns be added to a Tree View Control?
How to simulate the group like task for tree view nodes?


Ashok

Sorry, I misunderstood. I thought you wanted to merely display a summary of grouped rows and columns - didn't realize you actually needed tabular format.

I've heard the Microsoft flex grid control (it's free) can show hierarchical data, though I've never used it.

 

[jal]

I have few applications in MS Excel using VBA. These applications simulate Hierarchical Views of the data with following characteristics.

1) Indented values in a Column simulating a tree view of rows
2) Group level rows have formulas to sum or other calculations
3) Inrease or decrease to indenting of column values and shrinking or expending of group level rows behaves like group level tasks in MS Project.
4) Rows structure with data and formulas can be copied or inserted or moved.
5) Inserting of new row may change a row from simple to group type.
6) .....


I have to migrate these MS Excel based applications to Access adp project with SQL Server as database.

I am looking for the Form Controls ( or combination of Controls) that can be used in Access adp to meet these requirements.

Thanks
Ashok

The more I look at this list, the more complicated it appears. It almost sounds like you need to port the entire functionality of Excel to an Access form. That's asking a heck of a lot (why would we need Excel if Access already does all that stuff?). You might be better off trying to find a way to actually host a real Excel sheet within an Access control.

 

[Banana]

Assuming jal interpreted your post correctly (as I'm still not sure exactly what you want)

Actually, it's possible to display an Excel chart inside Access, though you'd be now working with a spreadsheet and will have issue with importing the data and ensuring they're still correctly normalized.

But if you need to enforce some kind of rule while having a spreadsheet-like entry form, I posted a demo in the Sample Database Forum.

 

[ak_ls]

jal / Banana,

Please think in terms of MS Project's Tasks collection and task objects as being displayed in Gantt Chart view with provision to change values in cells i.e. fields. Just think how it can be simulated on an Access form.

I have used MSFlexGrid for simple jobs. It requires enormous coding to change values in cells as you have to overlap a textbox or other control over the cells in MSFlexGrid. However, if it can display hierarchical views as you have mentioned, it may be useful in other situations.

Using MSExcel Control on Access Form does not meet the requirement as it still remains a single user application.

Ashok

 

[Banana]

Sounds like my sample I mentioned earlier will be what you may be looking...

The sample forum is over there.. The sample I referred to is the third one on the list, which you can definitely adapt to your needs.

 

[jal]

Sounds like my sample I mentioned earlier will be what you may be looking...

The sample forum is over there.. The sample I referred to is the third one on the list, which you can definitely adapt to your needs.

I went to the sample forum. Then I tried to click on your download "link" but the link seems to be broken.

 

[Banana]

How odd. It downloaded just fine for me. However, I did notice that my post said "Attachment pending approval", which may be restricting the download. I'll report this to the mods. In meanwhile, here it is...

 

[jal]

The original link is working fine now. Thanks.

 

[ak_ls]

To make things more clear, attaching a sample view of the ExcelSheet.

This sample view provides functionalities similar to MS Project i.e. arranging data in hierachical view having group tasks and simple tasks, inserting, deleting tasks/group tasks, shift a task or group of tasks to right or left, ability to change values in cells, group level tasks showing calculation summaries, as a result of task shift a group level task becomes a simple task or a simple task becomes a group level task, complex calculations for start/finish dates etc.


Ashok

 

[jal]

To make things more clear, attaching a sample view of the ExcelSheet.

This sample view provides functionalities similar to MS Project i.e. arranging data in hierachical view having group tasks and simple tasks, inserting, deleting tasks/group tasks, shift a task or group of tasks to right or left, ability to change values in cells, group level tasks showing calculation summaries, as a result of task shift a group level task becomes a simple task or a simple task becomes a group level task, complex calculations for start/finish dates etc.


Ashok

The only thought that comes to mind is that if you have a form (DataSheet view) and you set up the table relations using the Relationships graphical view, there will be expandable plus signs showing the related records. If you use this form as a subform (i.e. a control placed on a larger form), the larger form should also have room for any other info you need to display.

 

[Banana]

Jal's right- Subdatasheet would certainly fit the bill here, definitely. BTW, to be more precise, you'd create a form with datasheet view, then add a subform with a datasheet view as well to get that plus thing in form view.

Also, be aware that you can only have one subdatasheet per level, but you can nest up to 8 (?) subdatasheet.

HTH.