Find all parts in a heirarchy

[Spiftacu1ar]

Hello, I am new to both access and this forum. Over the past few weeks I have been teaching myself access to complete an assignment I have as a summer intern.



So far, i have been able to find answers by searching various forums such as this, but now I am really stumped. I don't even know where to begin.

I have a table called "Next" which contains 2 fields; PARTNO and NEXTASSMBLY

The next assembly is the next higher level that the corresponding part (partno) goes into. Both fields contain part numbers, as a "partno" could be an indivudual part or an assembly.

It is important to distinguish that NEXTASSMBLY is the part that PARTNO goes into, not a part that goes into PARTNO.

What I need to do is input a given partnumber (which will be entered in a form, and is presumably an assembly, not an individual part), and the query should list EVERY part that composes that assembly. In other words, every record that contains that given part number in the NEXTASSMBLY field.

At first this seemed simple; Make a query including both fields, and make the given part number (lets call it SEARCH) the criteria for the NEXTASSMBLY

This lists all the next lower level assemblies, which is great, but its not a c omplete list. I also need every part that those assemblies are made of, and so on.

An example list
[PARTNO][NEXTASSMBLY]
1 . . . . . (null) <---This is the top level assembly
2 . . . . . 1
3 . . . . . 2
4 . . . . . 1
5 . . . . . 2
6 . . . . . 2
7 . . . . . 3
8 . . . . . 5
9 . . . . . 7

If I want all parts composing PARTNO 1, it should output all numbers 2 - 9 (not just 2 and 4)
If I want all parts composing PARTNO 2, it should output 3, 5, 6, 7, 9 (not just (3,5 and 6)

Normal part numbers are not so simple, they include numbers, letters, and dashes. And, of course, there are a lot more parts, lol. Also, there is no one top level assembly. There are several products in this database. Each one has its own top level assembly.

How would I make a query (or function), to do what I need?

[Spiftacu1ar]

After spending a full day trying to get this done, i finally got this (unoptimized) code to work:
I know it's not the most efficient code, but then, I am not a programmer (Mech actually), so I'm happy it works. Its not too slow either. This was my first time using VBA, and definately my first function.

HOWEVER, any suggestions on how to make it better will still be appreciated.
In the end, I had to make 2 new tables, and the results go into a table, not a query. This is less thatn ideal, but it was the only way I could think to do it.

 
Function NextAs()
'variables here
Dim FormPartnoBox As String
Dim delSQL As String
Dim apndSQL As String
Dim subcritSQL As String
Dim subcritNullSQL As String
Dim i As Integer
Dim c As Integer
'variables end here
On Error GoTo NextAs_Err
'code starts here
    DoCmd.SetWarnings False
    'main table erase
    'once only
    delSQL = "DELETE NextResults.Results " & _
             "FROM NextResults;"
    'end once only here
 
    FormPartnoBox = "Forms![Switch]![Search Materials]"
    'loop here
    'main table apppend
    apndSQL = "INSERT INTO NextResults ( Results ) " & _
              "SELECT Next.PARTNO " & _
              "FROM [next] " & _
              "WHERE (((Next.NEXTASMBLY) Like " & FormPartnoBox & "));"
    DoCmd.RunSQL (delSQL)
    DoCmd.RunSQL (apndSQL)
 
    'for null test
    delSQL = "DELETE NextResultsNullTest.Results " & _
             "FROM NextResultsNullTest;"
    apndSQL = "INSERT INTO NextResultsNullTest ( Results ) " & _
             "SELECT Next.PARTNO " & _
             "FROM [next] " & _
             "WHERE (((Next.NEXTASMBLY) Like " & FormPartnoBox & "));"
    DoCmd.RunSQL (delSQL)
    DoCmd.RunSQL (apndSQL)
 
    delSQL = "DELETE NextResultsNullTest.Results " & _
             "FROM NextResultsNullTest " & _
             "WHERE NextResultsNullTest.Results IN ( " & _
             "SELECT NextResultsNullTest.Results " & _
             "FROM NextResultsNullTest LEFT JOIN NextResults ON NextResultsNullTest.[Results] = NextResults.[Results] " & _
             "WHERE (((NextResults.Results) Is Not Null)));"
    DoCmd.RunSQL (delSQL)
 
 
    subcritSQL = "IN (SELECT [NextResults].Results FROM [NextResults])"
    subcritNullSQL = "IN (SELECT [NextResultsNullTest].Results FROM [NextResultsNullTest])"
 
 
    'loop here
    i = 1
    c = 0
    Do While Not i = 0
 
        'main
        apndSQL = "INSERT INTO NextResults ( Results ) " & _
                  "SELECT DISTINCT [NextResultsNullTest].Results " & _
                  "FROM [NextResultsNullTest] " & _
                  "WHERE ([NextResultsNullTest].Results Is Not Null) ;"
        DoCmd.RunSQL (apndSQL)
 
        'for null
        apndSQL = "INSERT INTO NextResultsNullTest ( Results ) " & _
                  "SELECT Next.PARTNO " & _
                  "FROM [next] " & _
                  "WHERE (((Next.NEXTASMBLY) " & subcritSQL & ")) ;"
        DoCmd.RunSQL (apndSQL)
 
                'for null test
        delSQL = "DELETE NextResultsNullTest.Results " & _
                 "FROM NextResultsNullTest " & _
                 "WHERE NextResultsNullTest.Results IN ( " & _
                 "SELECT NextResultsNullTest.Results " & _
                 "FROM NextResultsNullTest LEFT JOIN NextResults ON NextResultsNullTest.[Results] = NextResults.[Results] " & _
                 "WHERE (((NextResults.Results) Is Not Null)));"
        DoCmd.RunSQL (delSQL)
        'if not null then i = 0 here (if dcount implemented... could be slow)
        If DCount("Results", "NextResultsNullTest") = 0 Then
            i = 0
        End If
        'i = i - 1
        c = c + 1
    Loop
    '''''''Debug tool:
    'MsgBox ("Loops performed: " & c)
 
    DoCmd.SetWarnings True
 
 
'code ends here
NextAs_Exit:
    Exit Function
NextAs_Err:
    MsgBox Error$
    Resume NextAs_Exit
End Function