SQL syntax error only?

[mohobrien]

I have a form with several subforms.
Each subform has its' own associated table.
The subform tables are in a one to many relationship with the main form's table.
I'm doing a QBF from a single form with unbound text boxes.
The plan is to to create a filter for the key field that I plug into the main form. My list has multiple entries because of the subforms' multiple entries. All I'm tring to do is a GROUP BY.
I get an error 'Can't find table or query rstSelectByCriteria' after I perform the SQL. Is there a problem with the syntax or am I missing something fundamental in my approach?

Function glrQBF_DoHide(frm As Form)
    Dim varSQL As Variant
    Dim strParentForm As String
    Dim strFilterList As String
    Dim strSelectByCriteria As String
    Dim qdfSelectByCriteria As QueryDef
    Dim rstSelectByCriteria As Recordset
    Dim qdfSelectByKey As QueryDef
    Dim rstSelectByKey As Recordset
    Dim db As DAO.Database
    
    'Create the appropriate Where clause based on the fields with data in them
    varSQL = glrDoQBF(CStr(frm.Name), False)
    
    strSelectByCriteria = "SELECT ALLDISP.*, tblHolders.*, tblPreviousHolders.*, tblSubmission.*, tblHolders.Holder " & _
    "FROM ((ALLDISP LEFT JOIN tblHolders ON ALLDISP.[Disposition Number] = tblHolders.DispositionNumber) " & _
    "LEFT JOIN tblPreviousHolders ON ALLDISP.[Disposition Number] = tblPreviousHolders.DispositionNumber) " & _
    "LEFT JOIN tblSubmission ON ALLDISP.[Disposition Number] = tblSubmission.DispositionNumber " & _
    "WHERE " & varSQL & ""
    
    Set db = CurrentDb()
    
    Set qdfSelectByCriteria = db.CreateQueryDef("")
    With qdfSelectByCriteria
            .SQL = strSelectByCriteria
    Set rstSelectByCriteria = .OpenRecordset()
    End With
    
    With rstSelectByCriteria
            .MoveFirst
            Do While Not .EOF
            Debug.Print , .Fields(0)
            .MoveNext
            Loop
    End With
    'OK to here.....
    Set qdfSelectByKey = db.CreateQueryDef("")
    With qdfSelectByKey
            .SQL = "SELECT rstSelectByCriteria.ALLDISP.[Disposition Number] " & _
    "FROM rstSelectByCriteria " & _
    "GROUP BY rstSelectByCriteria.ALLDISP.[Disposition Number]"
            Set rstSelectByKey = .OpenRecordset()
    End With
   
    With rstSelectByKey
            .MoveFirst
            Do While Not .EOF
            Debug.Print , .Fields(0)
            .MoveNext
            Loop
    End With

    'Build the filter string.
    With rstSelectByKey
        Do While Not .EOF
            strFilterList = strFilterList & !Disposition_Number & ", "
            .MoveNext
        Loop
    End With
    rstSelectByKey.Close
    rstSelectByCriteria.Close
    'List should end up looking like this
    '(((ALLDISP.[Disposition Number])="CBS 3041")) OR (((ALLDISP.[Disposition Number])="CBS 3084")) OR (((ALLDISP.[Disposition Number])="CBS 3089"))
        
    'Get the name of the Parent form
    strParentForm = Left(CStr(frm.Name), Len(CStr(frm.Name)) - 4)
    'Open the Parent form filtered with the Where clause generated above. I can't get this to work with SubForms!
    'DoCmd.OpenForm strParentForm, acNormal, , varSQL
    
    'Make this *_QBF form invisible
    frm.Visible = False

End Function

 

[Pat Hartman]

Two things. You didn't include the code for glrDoQBF so we can't see what the rest of the query looks like and tblHolders.Holder is going to create a duplicate field in your query since all the fields of tblHolders are called out earlier.