Results 1 to 3 of 3
  1. #1
    aebstract is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    19

    SQL Select + Loop through results in VBA

    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!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you use your favorite search engine and look for "Database Bill of materials" or "Hierarchical database".
    Here's a link to an article of Hierarchical structure.

  3. #3
    aebstract is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    19
    I will give that link a read through when I get a chance. I am familiar with the hierarchy and that portion of it and could completely program what I am wanting in a few other languages but vba really gets me confused, I guess since I'm diving fairly deep in without any previous knowledge. It does look like that link uses the vba coding to achieve similar results that I am trying to achieve. Thanks for the link. (btw I've done a lot of searches for various things but probably wasn't searching for quite the right thing) Thanks again, hopefully I can work through it with this help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Wrong results with SELECT TOP
    By Claudio in forum Queries
    Replies: 13
    Last Post: 03-10-2019, 08:59 PM
  2. Replies: 6
    Last Post: 12-01-2016, 03:40 PM
  3. Replies: 5
    Last Post: 05-14-2014, 01:17 PM
  4. Select Query returning no Results
    By Rhemo in forum Access
    Replies: 2
    Last Post: 09-15-2012, 04:11 AM
  5. Replies: 4
    Last Post: 10-12-2010, 02:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums