Dynamic Variables

[dgberry]

I have a couple of tables that are linked in a parent-child relationship. I want to be able to update the parent and child information and It could go down several levels, like in a bill of materials.




Can I create dynamic variables that will initialize as needed? I can use a "Level" variable as a counter to keep track of levels and increment and decriment it as it digs down and then back up.

[June7]

Certainly. All variables are dynamic. They can be set and reset as needed in code. Common application of dynamic variable is the counter in a loop structure.

[dgberry]

This is more like part1, part2, part3, etc... Where the first part of the var name is fixed and then new variables are created by using a counter to concatenate the fixed part with the variable part.

Maybe a dynamic array would work better.

[June7]

No, variable names cannot be dynamic in VBA. Yes, an array may be appropriate.

[dgberry]

Dang. Might be an option for future releases.

Thank you.

I might have to create a fixed number and then hope they do not go deeper than the number created.

[June7]

Don't hold your breath!

[Micron]

This is more like part1, part2, part3, etc... Where the first part of the var name is fixed and then new variables are created by using a counter to concatenate the fixed part with the variable part.

Maybe a dynamic array would work better.

What if the array name was the fixed part and you used the Join and Split functions to build or deconstruct the elements for the variable part? You could also alter the element values in an array by referencing their position.

[orange]

If you are dealing with bill of materials (hierarchy of structures)

Sample database and description here.

[CJ_London]

if you don't know how many levels of sub assemblies etc, you need to learn about binary trees and alternatives such as nested sets.

However these can be difficult to implement in sql code because they require a recursive action not available in access (or many other db's type). However you can have recursive code in vba which you can use to either build a bespoke sql query or to populate a recordset

[dgberry]

I already have the structure setup for the database. I am in the proccess of doing the cost and qty rollups using VBA. I've done recursive in a C++ class, looks like I get to learn in VBA.

[dgberry]

Binary trees? I've looked for that in Access help, but it wasn't too helpful.