Solved Continuous Sub forms
- Thread starter KitaYama
- Start date
- Local time
- Today, 14:42
- Joined
- Sep 12, 2006
- Messages
- 15,956
@MajP
Are you storing the node information in a table. I assume @KitaYama means the maximum depth of the tree.
Also, I assume this is a hierarchical tree, and not a balanced tree. In that case, when you add a new node you could store the depth of that node, being simply one more than it's parent, and you could then easily get the maximum depth of the whole tree by using the depth field, rather than having to iterate the whole tree.
If we have a balanced tree, where adding a new node potentially needs reallocation of parents into different buckets, if you will, then there's more work.
Are you storing the node information in a table. I assume @KitaYama means the maximum depth of the tree.
Also, I assume this is a hierarchical tree, and not a balanced tree. In that case, when you add a new node you could store the depth of that node, being simply one more than it's parent, and you could then easily get the maximum depth of the whole tree by using the depth field, rather than having to iterate the whole tree.
If we have a balanced tree, where adding a new node potentially needs reallocation of parents into different buckets, if you will, then there's more work.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 09:42
- Joined
- May 21, 2018
- Messages
- 8,991
@gemma-the-husky
This can be done, but I will explain why doing this is problematic, unreliable/ambiguous, and not very useful.
I tend to load thee types of trees.
1. Single table that is self referencing. This means the number of possible levels is unknown and can continue to grow. (Systems and subsystems, BOM,Family/bilogoical Trees, organizations, etc.)
2. Related child tables like you would have in subforms (sub-sub forms). (Customer - Orders - OrderDetails). In order to build this you will know ahead of time the maximum potential level of the tree which is simply the number of tables involved. I guess this is not really a "Tree" in traditional sense.
3. A combination that includes related tables and one or more self referencing table.
The below is case 2 where there are three tables involved.
In this case it just easier and more full proof to do other methods. I cannot think of a generic case where this would be useful.
In this case it will be known ahead of time how many possible levels you could have. Unfortunately that does not mean how many levels are actually populated. You have three tables involved thus three is the max. But there may be times where there is not yet any branch with 3 levels. If you are populating the tree maybe there is no information yet in the order details table. It is just far more likely that you care what type of node it is (Customer, Order, Order Details) vs is it the last of the potential nodes. The Identifier stored in the tag tells what type of node. Any code you write would probably be specific to that database so this would be very reliable.
Secondly, even if there are records in all tables, I do not load the whole table or read through it. When I load, I load the top level Nodes (and one more) only. They get dynamically loaded when I expand a node. The next level is loaded (and so on). This makes loading big trees far faster. However there is an option to load the whole thing.
Since often I do not always span all the data to load the tree (and do not want to) I would have to separately write another method that spans the data (at the table level) and dynamically determine the longest branch. Again that fails if all the potential tables are not yet populated.
The single self referencing table below is Case 1.
The potential levels are dynamic and unknown.
The only way to determine the longest branch is to span all the data recursively. Again, I often do not do that and only load the next level down when click on it. Again I could write another method to span the data for the purpose of determining the longest branch, but I cannot see what use that would be. But even doing this would be problematic. Spanning a large self referencing table can be very expensive and time consuming. You could do it once on load, but what happens if you add, move, or delete a node in the tree or at the table level. You would have to potentially span again or write some pretty involved code to determine if you need to re-span again.
However there are lots of helpful methods already to answer similar questions.
1. Determine what table a node comes from
2. Determine all the children of a node
3. Determine level of a given node
4. Determine all the descendants of a node (hierrarchy)
Descendants: So for a given node you can determine the depth of the branch below by determining that Edison Cole is the highest level.
Now there are many cases where I do span the entire data and store information about each and every node. For example if each node had cost data in it may roll it up and store in a table to then build a form / report.
IMO it could be expensive, unreliable / ambiguous, and not real useful.
This can be done, but I will explain why doing this is problematic, unreliable/ambiguous, and not very useful.
I tend to load thee types of trees.
1. Single table that is self referencing. This means the number of possible levels is unknown and can continue to grow. (Systems and subsystems, BOM,Family/bilogoical Trees, organizations, etc.)
2. Related child tables like you would have in subforms (sub-sub forms). (Customer - Orders - OrderDetails). In order to build this you will know ahead of time the maximum potential level of the tree which is simply the number of tables involved. I guess this is not really a "Tree" in traditional sense.
3. A combination that includes related tables and one or more self referencing table.
The below is case 2 where there are three tables involved.
In this case it just easier and more full proof to do other methods. I cannot think of a generic case where this would be useful.
In this case it will be known ahead of time how many possible levels you could have. Unfortunately that does not mean how many levels are actually populated. You have three tables involved thus three is the max. But there may be times where there is not yet any branch with 3 levels. If you are populating the tree maybe there is no information yet in the order details table. It is just far more likely that you care what type of node it is (Customer, Order, Order Details) vs is it the last of the potential nodes. The Identifier stored in the tag tells what type of node. Any code you write would probably be specific to that database so this would be very reliable.
Secondly, even if there are records in all tables, I do not load the whole table or read through it. When I load, I load the top level Nodes (and one more) only. They get dynamically loaded when I expand a node. The next level is loaded (and so on). This makes loading big trees far faster. However there is an option to load the whole thing.
Since often I do not always span all the data to load the tree (and do not want to) I would have to separately write another method that spans the data (at the table level) and dynamically determine the longest branch. Again that fails if all the potential tables are not yet populated.
The single self referencing table below is Case 1.
The potential levels are dynamic and unknown.
The only way to determine the longest branch is to span all the data recursively. Again, I often do not do that and only load the next level down when click on it. Again I could write another method to span the data for the purpose of determining the longest branch, but I cannot see what use that would be. But even doing this would be problematic. Spanning a large self referencing table can be very expensive and time consuming. You could do it once on load, but what happens if you add, move, or delete a node in the tree or at the table level. You would have to potentially span again or write some pretty involved code to determine if you need to re-span again.
However there are lots of helpful methods already to answer similar questions.
1. Determine what table a node comes from
2. Determine all the children of a node
3. Determine level of a given node
4. Determine all the descendants of a node (hierrarchy)
Descendants: So for a given node you can determine the depth of the branch below by determining that Edison Cole is the highest level.
Now there are many cases where I do span the entire data and store information about each and every node. For example if each node had cost data in it may roll it up and store in a table to then build a form / report.
IMO it could be expensive, unreliable / ambiguous, and not real useful.
Last edited:
- Local time
- Today, 14:42
- Joined
- Sep 12, 2006
- Messages
- 15,956
What I meant was when you store the node for say "Edison Cole", you know that you are inserting that after Dan Rowe, depth 9, and you could store the depth value of 10 for Edison Cole when you insert that node.
This can become slightly more fiddly if say Darren Nader depth 7, could be promoted to level 6, and then all his sub-tree needs renumbering. Also if Darren Nader left, and was replaced by Violet Turner, in which case Violet Turner becomes depth 7 rather than 8, and her entire sub tree also need renumbering.
It's especially difficult if it's a search tree, say, that needs to be balanced, because any change to a node might need the whole tree to be rebalanced at a higher level.
I would have thought it worth doing if you need to be able to identify the maximum depth, rather than having to traverse the whole thing every time - especially if the tree structure is static, and doesn't need to automatically rebalance, and especially if it's a massive tree with many thousands of elements traversing the whole tree.
Maybe it depends why @KitaYama needs to know the depth.
This can become slightly more fiddly if say Darren Nader depth 7, could be promoted to level 6, and then all his sub-tree needs renumbering. Also if Darren Nader left, and was replaced by Violet Turner, in which case Violet Turner becomes depth 7 rather than 8, and her entire sub tree also need renumbering.
It's especially difficult if it's a search tree, say, that needs to be balanced, because any change to a node might need the whole tree to be rebalanced at a higher level.
I would have thought it worth doing if you need to be able to identify the maximum depth, rather than having to traverse the whole thing every time - especially if the tree structure is static, and doesn't need to automatically rebalance, and especially if it's a massive tree with many thousands of elements traversing the whole tree.
Maybe it depends why @KitaYama needs to know the depth.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 09:42
- Joined
- May 21, 2018
- Messages
- 8,991
That is the problem because most of those nodes are never loaded. They only get loaded when you manually expand the branch. So edison cole exist in the data, but only Linda Powlowki (and the top level) gets loaded into the tree.
Even if I loaded every node (defeating the benefits of not loading all nodes), I could calculate the table depth but then it is only as good as a record is moved, deleted, and added and may have to respan the whole tree. In drag and drop you can reshape the branches very quickly.
However I already can dynamically tell you the level of every node so there is no need to store that.
The below demo has 10,000 potential nodes if you expand the tree view, But I only load 18 nodes. 9 top level nodes and 1 more node below each visible node so that I get a plus sign. In truth if I loaded every node at start up it would take a ridiculous amount of time and likely crash the treeview.
Again it can be done but for a potential cost. not much utility, and ambiguous meaning.
So the only reliable way to get the longest branch depth (not necessarily the possible depth) is to respan the data dynamically. You could count the records in the recordset and alert the user on size "do you really want to do this?" After a couple thousand records this can start taking a lot of time to do this dynamically. You cannot do it once if you add, edit, delete, and move nodes/records.
@isladogs Sorry I forgot to report back what you suggested.
At present, I'm using @MajP 's suggested subdataSheet and in some cases linked subforms to texboxes and comboboxes on main form.
So my problem is solved. Just wanted to say that unfortunately I wasn't able to follow your instruction. Maybe I'm doing something wrong.
Thanks will give it a try as soon as I’m back to my desk.
isladogs
MVP / VIP
- Local time
- Today, 14:42
- Joined
- Jan 14, 2017
- Messages
- 18,559
You can use continuous forms in the footer or header section without issue and in certain circumstances in the detail as well.
As @Minty stated, the message in your screenshot only applies to placing a continuous subform in the detail section & only then when the main form also has records in the detail section. In that situation, the message is totally logical. Otherwise, it should not appear.
There have been several requests to the Access team to fix the message but we're still waiting.
This is an example of a simulated split form with both the main form records and the subform in the detail section.
For this to work there must be no master/child fields linked
Form view
Design view
The subform could also be in the footer as I suggested in post #9 or in a tab control (or navigation control)
For more info, see Simulated Split form (isladogs.co.uk)
As @Minty stated, the message in your screenshot only applies to placing a continuous subform in the detail section & only then when the main form also has records in the detail section. In that situation, the message is totally logical. Otherwise, it should not appear.
There have been several requests to the Access team to fix the message but we're still waiting.
This is an example of a simulated split form with both the main form records and the subform in the detail section.
For this to work there must be no master/child fields linked
Form view
Design view
The subform could also be in the footer as I suggested in post #9 or in a tab control (or navigation control)
For more info, see Simulated Split form (isladogs.co.uk)
Last edited:
Maybe I wasn’t clear enough in discribing my situation. I was referring to having continuous sub form in a continuous main form with parent/child links. the main form in your images seem to be single form. And split forms are absolutely a different discussion.
Anyhow I’ll check @minty’s suggestion and simply ignore the error to see the result first thing in the morning.
Thanks
- Local time
- Today, 14:42
- Joined
- Sep 12, 2006
- Messages
- 15,956
It's only a matter of requerying the "subform" in the current event of the main form
So you could actually have an unbound container form, and put both continuous forms inside the container, and use invisible (visible if it helps) fields to store the key values from the parent subform, and then either link those to the child subform, or use them as criteria in the query for the child subform.
Then when you change the main subform, in the current event you set the linking values, and then requery the child subform.
So you could actually have an unbound container form, and put both continuous forms inside the container, and use invisible (visible if it helps) fields to store the key values from the parent subform, and then either link those to the child subform, or use them as criteria in the query for the child subform.
Then when you change the main subform, in the current event you set the linking values, and then requery the child subform.
Unless I'm not understanding something, I don't see any reason for not simply adding a feature to edit/add records from the report/subreport arrangement, I mean, it's trivial Access stuff.
Besides, this was your initial request:
For which a report/subreport is the easiest.
Your thread now has reached a point where three additional topics have emerged:
1. Main topic: nesting a list inside another list, simple nesting depth.
2. Synchronizing subforms, but Access doesn't let you nest a subform control inside a continous form
3. Tree views: for nesting lists to different depths, which adds unnecessary complexity for a simple list inside list concept.
4. Split views: for showing the same record in two different views, it's not nesting
Each of these concepts are ideal for specific things. Care to explain what I am missing?
Peace.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 09:42
- Joined
- May 21, 2018
- Messages
- 8,991
To show what that could look like, you can have a main form with a report in a subform control. This would give the Grouped nested view. This could be synched to another subform for your edits. Then you can navigate the report, but edit in the subform.
My original question was I was looking for a post by MajP, where he had used nested subdatasheets . He, himself gave me the link and I marked the thread as solved. Then we went into a treeview discussion to see if it helps better.
Later, others were kind enough to help and share their ideas, where we went to using sub reports and split forms and other solutions.
I simply thought it not being polite to leave some solutions un-replied.
As I said earlier, my original problem is solved and I used what MajP had shown in the link in #3.
I really appreciate all who shared their experience.
Million thanks.
cheers.
Last edited:
