Help with Nested DoCmd statements

[ma t]

Hello,
I am not sure how to accomplish my goal-
I have some code that creates queries based on a value in a checkbox.
So, depending on that value, the queries may or may not exist.
I need to take those queries (if the exist) and create one union query.

This code (cobbled together from bits and pieces I found in forums like this one) creates the first query beautifully, but it won't union the second query.

Private Sub cmdSubmit_Click()

    Dim blnQueryExists As Boolean
    Dim cat As New ADOX.Catalog
    Dim cmd As New ADODB.Command
    Dim qry As ADOX.View
          
    blnQueryExists = False
    Set cat.ActiveConnection = CurrentProject.Connection
    For Each qry In cat.Views
        If qry.Name = "qryAgencySelectQuery" Then
        cmd.CommandText = "SELECT qryAgencySelectQuery.PID, qryAgencySelectQuery.RECID, ""Agency"" AS [Table] FROM qryAgencySelectQuery"
        cat.Views.Append "qrySelectTotal", cmd
    Exit For

        If qry.Name = "qryProgramSelectQuery" Then
        cmd.CommandText = "UNION SELECT qryProgramSelectQuery.PID, qryProgramSelectQuery.RECID, ""ProgStat"" AS [Table] FROM qryProgramSelectQuery"
        cat.Views.Append "qrySelectTotal", cmd
    Exit For
    
    End If
    End If
    Next qry
    'End If
    End Sub

Obviously, you can tell that I don't know what I am doing, and I humbly admit that. All help is greatly appreciated!

 

[JHB]

Because you exit the For Next statement before you get down to the Union query, (marked with red text).
You've to build a nested For Next loop, if you have found the first query, then search for the second query.

Hello,

Private Sub cmdSubmit_Click()

    Dim blnQueryExists As Boolean
    Dim cat As New ADOX.Catalog
    Dim cmd As New ADODB.Command
    Dim qry As ADOX.View
          
    blnQueryExists = False
    Set cat.ActiveConnection = CurrentProject.Connection
    For Each qry In cat.Views
        If qry.Name = "qryAgencySelectQuery" Then
        cmd.CommandText = "SELECT qryAgencySelectQuery.PID, qryAgencySelectQuery.RECID, ""Agency"" AS [Table] FROM qryAgencySelectQuery"
        cat.Views.Append "qrySelectTotal", cmd
  [SIZE=4][B][COLOR=Red]  Exit For[/COLOR][/B][/SIZE]

        If qry.Name = "qryProgramSelectQuery" Then
        cmd.CommandText = "UNION SELECT qryProgramSelectQuery.PID, qryProgramSelectQuery.RECID, ""ProgStat"" AS [Table] FROM qryProgramSelectQuery"
        cat.Views.Append "qrySelectTotal", cmd
    Exit For
    
    End If
    End If
    Next qry
    'End If
    End Sub

Obviously, you can tell that I don't know what I am doing, and I humbly admit that. All help is greatly appreciated!

 

[ma t]

I tried that, but then the first section cat.Views.Append "qrySelectTotal", cmd line gets a yellow highlight with an error.

Thank you,

 

[Mihail]

Use this in order to see if the SQL string is well done.
Then adapt to your technology or create a query directly from this SQL.

Private Sub cmdSubmit_Click()

Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
          
'    blnQueryExists = False
    Set cat.ActiveConnection = CurrentProject.Connection

Dim SQL As String
    For Each qry In cat.Views
        If qry.Name = "qryAgencySelectQuery" Then
            If SQL <> "" Then
                SQL = SQL & " UNION "
            End If
            SQL = SQL & "SELECT qryAgencySelectQuery.PID, qryAgencySelectQuery.RECID, ""Agency"" AS [Table] FROM qryAgencySelectQuery"
'        cmd.CommandText = "SELECT qryAgencySelectQuery.PID, qryAgencySelectQuery.RECID, ""Agency"" AS [Table] FROM qryAgencySelectQuery"
'        cat.Views.Append "qrySelectTotal", cmd
        End If
            
        If qry.Name = "qryProgramSelectQuery" Then
            If SQL <> "" Then
                SQL = SQL & " UNION "
            End If
            SQL = SQL & "SELECT qryProgramSelectQuery.PID, qryProgramSelectQuery.RECID, ""ProgStat"" AS [Table] FROM qryProgramSelectQuery"
'        cmd.CommandText = "UNION SELECT qryProgramSelectQuery.PID, qryProgramSelectQuery.RECID, ""ProgStat"" AS [Table] FROM qryProgramSelectQuery"
'        cat.Views.Append "qrySelectTotal", cmd
        End If
    Next qry
    
    Debug.Print SQL
    
End Sub

 

[JHB]

I tried that, but then the first section cat.Views.Append "qrySelectTotal", cmd line gets a yellow highlight with an error.

Personally I've no good experiences with ADOX (views) therefore I write, if everything works well, then the nested For Next, must look like this:

Private Sub cmdSubmit_Click()

  Dim blnQueryExists As Boolean
  Dim cat As New ADOX.Catalog
  Dim cmd As New ADODB.Command
  Dim qry As ADOX.View, qryNo2 As ADOX.View
  blnQueryExists = False
  Set cat.ActiveConnection = CurrentProject.Connection
  For Each qry In cat.Views
    If qry.Name = "qryAgencySelectQuery" Then
      cmd.CommandText = "SELECT qryAgencySelectQuery.PID, qryAgencySelectQuery.RECID, ""Agency"" AS [Table] FROM qryAgencySelectQuery"
      cat.Views.Append "qrySelectTotal", cmd
      For Each qryNo2 In cat.Views
        If qry.Name = "qryProgramSelectQuery" Then
          cmd.CommandText = "UNION SELECT qryProgramSelectQuery.PID, qryProgramSelectQuery.RECID, ""ProgStat"" AS [Table] FROM qryProgramSelectQuery"
          cat.Views.Append "qrySelectTotal", cmd
        End If
      Next qryNo2
      Exit For
    End If
  Next qry
End Sub