title: VBA Access - Show queries results in subform for each element in another subform
body: <p>Right now I've a Subform1 (SFUCOM) which lists a mixed variety of codes of machines and sets from a same Order-Content (U-COM) table filtered by selecting an Order from a combobox. Clicking on each of these shown codes (Me.ctrlSUB) a second Subform2 (SFUMCH) lists these machines/sets content (parts) and other things through this onclick event:</p>
  <pre><code>Private Sub ctrlSUB_Click()
  
  Dim SQLMCH As String
  Dim SQLRSN As String
  Dim SQLSET As String
  Dim SQLOTH As String
        
  SQLMCH = &quot;SELECT [U-MCH].IDSUB, [U-MCH].Qty, LNK.Allegato &quot; _
         &amp; &quot;FROM LNK RIGHT JOIN [U-MCH] ON LNK.IDREC = [U-MCH].IDSUB &quot; _
         &amp; &quot;WHERE ((([U-MCH].IDSUP) ALike '%&quot; &amp; Me.ctrlSUB.Text &amp; &quot;%') AND ((LNK.IDREC)=[U-MCH].[IDSUB]))&quot;
         
  SQLRSN = &quot;SELECT [U-RSN].IDSUB, [U-RSN].Qty, LNK.Allegato &quot; _
         &amp; &quot;FROM LNK RIGHT JOIN [U-RSN] ON LNK.IDREC = [U-RSN].IDSUB &quot; _
         &amp; &quot;WHERE ((([U-RSN].IDSUP) ALike '%&quot; &amp; Me.ctrlSUB.Text &amp; &quot;%') AND ((LNK.IDREC)=[U-RSN].[IDSUB]))&quot;
         
  SQLSET = &quot;SELECT [U-SET].IDSUB, [U-SET].Qty, LNK.Allegato &quot; _
         &amp; &quot;FROM LNK RIGHT JOIN [U-SET] ON LNK.IDREC = [U-SET].IDSUB &quot; _
         &amp; &quot;WHERE ((([U-SET].IDSUP) ALike '%&quot; &amp; Me.ctrlSUB.Text &amp; &quot;%') AND ((LNK.IDREC)=[U-SET].[IDSUB]))&quot;
  
  SQLOTH = &quot;SELECT [U-COM].IDSUB, [U-COM].Qty, LNK.Allegato &quot; _
         &amp; &quot;FROM LNK RIGHT JOIN [U-COM] ON LNK.IDREC = [U-COM].IDSUB &quot; _
         &amp; &quot;WHERE (((LNK.IDREC) ALike '%&quot; &amp; Me.ctrlSUB.Text &amp; &quot;%'))&quot;
            
   If (Left(Me.ctrlSUB.Text, 3) = &quot;MCH&quot;) Then
     Forms![Q-COM]!SFUMCH.Form.RecordSource = SQLMCH
  
  ElseIf (Left(Me.ctrlSUB.Text, 3) = &quot;RSN&quot;) Then 
     Forms![Q-COM]!SFUMCH.Form.RecordSource = SQLRSN
     
  ElseIf (Left(Me.ctrlSUB.Text, 3) = &quot;SET%&quot;) Then
     Forms![Q-COM]!SFUMCH.Form.RecordSource = SQLSET
     
  Else
     Forms![Q-COM]!SFUMCH.Form.RecordSource = SQLOTH
     
   End If
     
     Forms![Q-COM]!SFUMCH.Form.Requery
  
  
  End Sub
  </code></pre>
  <p>And it works like a charm, simply love it.</p>
  <p>Now the PAINFUL PART: I would really like to have in Subform1 a button in order to list in Subform2 ALL the parts of ALL Machines/Sets/etc. listed in Subform1. I've already found a looks-like-working-solution to avoid all of these IF...ELSEIF... with a single dynamic query, something like this:</p>
  <pre><code>Dim codTAB As String
  Dim SQLGOD As String
  Dim extcod As String
  
   If (Left(Me.ctrlSUB.Value, 3) = &quot;MCH&quot;) Then
    codTAB = &quot;[U-MCH]&quot;
   ElseIf (Left(Me.ctrlSUB.Value, 3) = &quot;RSN&quot;) Then
    codTAB = &quot;[U-RSN]&quot;
   ElseIf (Left(Me.ctrlSUB.Value, 3) = &quot;SET&quot;) Then
    codTAB = &quot;[U-SET]&quot;
   Else
    codTAB = &quot;[U-COM]&quot;
  
  End If
      
  SQLGOD = &quot;SELECT  (&quot; &amp; codTAB &amp; &quot;.&quot; &amp; &quot;IDSUB&quot; &amp; &quot;), (&quot; &amp; codTAB &amp; &quot;.&quot; &amp; Qty &amp; &quot;), LNK.Allegato &quot; _
         &amp; &quot;FROM LNK RIGHT JOIN (&quot; &amp; codTAB &amp; &quot;) ON LNK.IDREC = (&quot; &amp; codTAB &amp; &quot;.&quot; &amp; &quot;IDSUB&quot; &amp; &quot;) &quot; _
         &amp; &quot;WHERE (((&quot; &amp; codTAB &amp; &quot;.&quot; &amp; &quot;IDSUP&quot; &amp; &quot;) ALike '%&quot; &amp; Me.ctrlSUB.Text &amp; &quot;%') &quot; _
         &amp; &quot; AND ((LNK.IDREC)= (&quot; &amp; codTAB &amp; &quot;.&quot; &amp; &quot;IDSUB&quot; &amp; &quot;)))&quot;
         
     Forms![Q-COM]!SFUMCH.Form.RecordSource = SQLGOD
     Forms![Q-COM]!SFUMCH.Form.Requery
  </code></pre>
  <p>But I'm honestly all over the place on how to set the recordsource as a sort of &quot;union&quot; of all the results for each of the records in Subform1.</p>
  <p>Any feedback or suggestion is more than welcome</p>
  <hr />
  <p>EDIT AND ADDITIONAL INFOS:
  There are 4 different tables U-COM, U-MCH, U-RSN, AND U-SET because an order contain in U-COM MCH/RSN/SET and spare parts, U-MCH/U-RSN/U-SET contain loose parts, sort of matrioska. Something like this:</p>
  <pre><code>[U_COM]
  Order    IDSUP   | [U_MCH]/RSN/SET
  Content  IDSUB  -&gt; IDSUP            |  [LNK]
           Qty     | IDSUB    Parts  -&gt;  IDREC
                    Qty              |  Allegato (attachment/Linked filepaths)
  </code></pre>
  <p>So Subform1 (SFUCOM) has as recordsource U-COM filtered by the Order combobox, while Subform2 (SUMCH) has as recordsource the first code (ctrlSUB_Click)</p>