Vba for Access Recursive Question

Carlos Fernandez
Carlos Fernandez used Ask the Experts™
on
Dear Friends, I need a VBA Access function that has two Array Arguments, the first array is a list of Items:

List of Items
The second array is a product structure, like this one:

Product Structure
So you have to do some calculations:

Calculations
And the final result, should be this one:

Result
Kindly note I have marked in Yellow the values that were grouped/sum

I hope you can help me. I have been fighting with this one for weeks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Have a look at the following article:

https://www.experts-exchange.com/articles/19/Expanding-a-Hierarchical-Data-Structure.html

 Which has a sample database containing code showing you how to do a bill of materials explosion  using a recursive call .

Jim
Carlos FernandezIndustrial Engineer

Author

Commented:
Hi Jim, this is a good example, my problem is to once I have the data into recordset or arrays, handle it inside the function with arrays and make it recursive, and have a result as an array that can be sent back into a table, if needed. Thanks. Carlos
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Carlos,

 Access has no way directly of writing an array out to a record set or table (there is no corresponding function to GetRows()).   Given that, it’s best if you simply write the output right to a table  and I would skip using arrays entirely.    

  I can’t see where using arrays is going to gain you anything.  You’d be better off using a collection or a dictionary class rather than using arrays.

    But even with those,   I’m not sure it would be worth the overhead in setting them up, performing your operations, and then reading everything out to a table when done. Reading and writing from tables directly is pretty fast if things are properly indexed.  JET also manages its cache pretty well.

But in regards to your question, making a recursive call to a function is no different from the example even though it is using tables.   At the places where it works with tables, you would just work with your array.

Jim
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
I'll suggest the nested set model instead of the (classic) hierarchical model.
Queries might be more comple, but you'll have less of them, and far less VBA code (since queries can handle most of the job).

Take a look at this article:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
Carlos FernandezIndustrial Engineer

Author

Commented:
Hi Jim,
kindly find attached the code I made before know your approach, I was able to getrows from tables using a recordset and then to an Array, my problem comes when I want to start looping for grandchild ensambles. Best regards. Carlos
Code.txt
Carlos FernandezIndustrial Engineer

Author

Commented:
Thanks Fabrice for your approach. Carlos
John TsioumprisSoftware & Systems Engineer

Commented:
Can you give some more info on your issue...
Arrays can be easily passed as variant argument...something like that :

Private Sub SomeArrayHandling(AnArray As Variant)
Dim ArrayCounter As Integer
If Not isArrayEmpty(AnArray ) Then
For ArrayCounter = LBound(AnArray ) To UBound(AnArray )
'Do some work...if necessary recursion
Next ArrayCounter
End If
End Sub

Open in new window


and the helper function
Public Function isArrayEmpty(parArray As Variant) As Boolean
'Returns false if not an array or dynamic array that has not been initialised (ReDim) or has been erased (Erase)

    If IsArray(parArray) = False Then isArrayEmpty = True
    On Error Resume Next
    If UBound(parArray) < LBound(parArray) Then isArrayEmpty = True: Exit Function Else: isArrayEmpty = False

End Function

Open in new window

Carlos FernandezIndustrial Engineer

Author

Commented:
Thanks John
Carlos FernandezIndustrial Engineer

Author

Commented:
Jim Dettman has helped me in stablish a simpler proceeding that will help me in the development the main solution, although it stablish the core algorithm necessary to solve my initial question it has not resolved the complete problem of the initial question, I think the help from Jim placed a big step toward the solution.

Honestly speaking I am new at EE, and I expected a full algorithm for my initial question. I guess I have to work harder, in develop my capabilities. But the help from the expert Jim was crucial.

Best regards
Carlos
Carlos FernandezIndustrial Engineer

Author

Commented:
Thanks for your extremely valuable help, please keep sharing your knowledge.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial