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

    Use query list to create an array with vba and display in detail of report

    I know my initial setup may be a little off in where my queries are ran and whatnot, but I'm just building this up and figuring it out. I have my report's record source set to this query:



    Code:
    SELECT dbo_MIMOH.buildItem, dbo_MIMOH.bomItem, dbo_MIMOH.bomRevFROM dbo_MIMOH
    WHERE dbo_MIMOH.moStat = 1;
    This grabs all open Manufacturing Orders from the database. For each result, I run it through this query:

    Code:
    SELECT MIBOMD.bomItem, MIBOMD.bomRev, MIBOMD.partId, MIITEM.revId, MIBOMD.qty,
    MIBOMD2.partId AS partId2, MIITEM2.revId AS revId2, MIBOMD2.qty AS qty2,
    MIBOMD3.partId AS partId3, MIITEM3.revId AS revId3, MIBOMD3.qty AS qty3,
    MIBOMD4.partId AS partId4, MIITEM4.revId AS revId4, MIBOMD4.qty AS qty4,
    MIBOMD5.partId AS partId5, MIITEM5.revId AS revId5, MIBOMD5.qty AS qty5
    
    
    FROM MIBOMD 
    LEFT JOIN MIITEM ON MIITEM.itemId = MIBOMD.partId
    LEFT JOIN MIBOMD AS MIBOMD2 ON MIBOMD2.bomItem = MIBOMD.partId AND MIBOMD2.bomRev = MIITEM.revId
    LEFT JOIN MIITEM AS MIITEM2 ON MIITEM2.itemId = MIBOMD2.partId
    LEFT JOIN MIBOMD AS MIBOMD3 ON MIBOMD3.bomItem = MIBOMD2.partId AND MIBOMD3.bomRev = MIITEM2.revId
    LEFT JOIN MIITEM AS MIITEM3 ON MIITEM3.itemId = MIBOMD3.partId
    LEFT JOIN MIBOMD AS MIBOMD4 ON MIBOMD4.bomItem = MIBOMD3.partId AND MIBOMD4.bomRev = MIITEM3.revId
    LEFT JOIN MIITEM AS MIITEM4 ON MIITEM4.itemId = MIBOMD4.partId
    LEFT JOIN MIBOMD AS MIBOMD5 ON MIBOMD5.bomItem = MIBOMD4.partId AND MIBOMD5.bomRev = MIITEM4.revId
    LEFT JOIN MIITEM AS MIITEM5 ON MIITEM5.itemId = MIBOMD5.partId
    
    
    WHERE MIBOMD.bomItem = '2263144'
    Replace the number 2263144 with the textbox from the report's detail tab and it runs per result of the first query. I have it set to a specific number and syntax is a little off because this is being worked on in SQL Server Management Studio currently, which is where I copied the code from. This second query gives me results that look like this:



    Now, I want to use vba to loop through these results and grab the last non NULL value for partid# and set it in an array. Then I can group like items in the array and sort them how I would like. After that I want to display the results of the array, after formatting, on my report. I'm not sure how to approach the last portion of this or where the queries and whatnot need to go for this to work out correctly.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    it looks like you're trying too hard to complicate this.
    Access will report all this for you , in a simple report with no programming.
    use a group and show all the sub records under it.

  3. #3
    aebstract is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    19
    Quote Originally Posted by ranman256 View Post
    it looks like you're trying too hard to complicate this.
    Access will report all this for you , in a simple report with no programming.
    use a group and show all the sub records under it.
    I probably am complicating it, but not sure what you mean by use a group and show all the sub records under it.

  4. #4
    aebstract is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    19
    Have taken several days off from this and about to get back in to it, could still use some tips on where to go with it.

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

Similar Threads

  1. Create Array
    By jo15765 in forum Programming
    Replies: 9
    Last Post: 11-09-2016, 12:55 PM
  2. Replies: 5
    Last Post: 10-04-2015, 05:23 PM
  3. Replies: 1
    Last Post: 02-18-2013, 02:09 PM
  4. Replies: 3
    Last Post: 02-17-2013, 09:20 PM
  5. Access Report: List Detail Horizontally
    By rad1182 in forum Access
    Replies: 1
    Last Post: 01-11-2012, 04:24 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