Recursive Report!

[Rawb]

Hey all! Just a bit ago I figured out how to "tier" Records in a Table so that the Table knows that certain Records are "children" of another Record. Basically, you just reference the ID of the immediate parent Record in the same table.

RecordID|ParentID|Description
1|0|Parent Record
2|1|Child Record 1
3|1|Child Record 2
4|2|Sub-child Record 1.1
5|2|Sub-child Record 1.2
6|3|Sub-child Record 2.1
7|3|Sub-child Record 2.2
8|3|Sub-child Record 2.3
9|7|Sub-sub-child Record 2.2.1

So now I have a new problem: displaying that data in a Report!

Basically, I want to "tab in" child records, like so:

• Parent Record
  
  • Child Record 1
    
    • Sub-child Record 1.1
    • Sub-child Record 1.2
    
    
  • Child Record 2
    
    • Sub-child Record 2.1
    • Sub-child Record 2.2
      
      • Sub-sub-child 2.2.1
      
      
    • Sub-child Record 2.3
    
    
  
  

Is there a way to do this when I don't know how many levels deep the Records go?

[RuralGuy]

Perhaps this link will suggest some ideas: http://allenbrowne.com/ser-59.html

[Rawb]

If I understand the code, all this does is provide you with an array (ok, a linked list/collection) representing the full parent/child relationship between records.

While I'll definitely need to do something like this to collect the data, it still doesn't seem to help me display it in a Report.

I went through the code pretty fast though, maybe I missed something?

[RuralGuy]

There is no native procedure to recurse through a table that could be n levels deep. You would have to code it yourself of maybe use a method to fill a table with your values after recursion.

[Rawb]

Ah, gotcha.

Is there a method to generate a Report based entirely on VB code and without any actual Table/Query? Or would I need to find a way to dump the collection into a dynamically generated Table?

[RuralGuy]

I would say this is a case for using a temporary table. I put my temporary tables in a separate dynamically created backEnd and delete the BE when done. It avoids the bloat.

[Rawb]

Oooo, that's a good idea!

I'll look into doing it that way then. If I run into any problems, I'll post back here

Thanks!

[RuralGuy]

Good luck and have fun.