I have the general idea of what I want to do, but I'm struggling to understand how to implement it in to VBA. I'm working with BOMs (Bill of Materials) and trying to loop through the entire BOM through several SQL queries, though they will all be the same query run depending on a few other things.
My base query as of now:
Code:
SELECT dbo_MIBOMD.partId, dbo_MIBOMH.bomItem, dbo_MIBOMH.ecoDocPath, dbo_MIITEM.revId
FROM ((dbo_MIBOMD)
LEFT JOIN dbo_MIBOMH on dbo_MIBOMH.bomItem = dbo_MIBOMD.partId)
LEFT JOIN dbo_MIITEM on dbo_MIITEM.itemId = dbo_MIBOMD.partId
WHERE (((dbo_mibomd.bomItem) = "2229174" and (dbo_mibomd.bomRev) = "A"));
This returns the results I need to be able to determine if I need to run the query again on a different bomItem or stop with current resultset. If this query returns a NULL value for 'dbo_MIBOMH.bomItem', then it should not loop through that specific item. If it is NOT NULL, then it should run the same query again using the new bomItem and revId. Once this entire script is complete, I want to be left with a list (array) of items and their corresponding "ecoDocPath". The ecoDocPath is what I will be using to print out all drawings necessary for the given bomItem.
So as a quick example:
2229174 (or whatever item it is) will be pulled from the report initially to start the process.
This item has 4 items under it, so the above query results in 4 rows of information and none of the rows and NULL.
Next step is to run the above code and checks for each of those 4 items. If there is an item or multiple items that return NOT NULL, then it needs to continue this process throughout the entire tree until it runs in to a NULL. Then move on to the next item.
If 2229174 returns 4 values and the first is null but the other 3 are not, then I need to run the query on the last 3 and not the 1st. Essentially looping through ALL items of the parent bomItem without missing any on the way down and skipping over and NULL values as anytime you hit one, you are at the base level and must go to the next item.
I will still be working on this and trying to figure it out but I figured I would go ahead and start a help topic because I am really lost at the moment.
Thanks!