Reference Form Recordset in Query

[idbr]

Hi,

I'm trying to reference a form and subform recordset in a query. The form recordset is changed dynamically to allow users to 'filter' for certain sets of records.

I want to be able to output the results of the filter to Excel, using a query/subquery, e.g. (air code)

SELECT tbl1.x, tbl2.y, tbl2.z FROM tbl1 JOIN tbl2 etc WHERE y IN (SELECT y FROM MySubform.Recordset).

I can't just use Excel's CopyFromRecordset as I need to include data from both the Form and it's Subform. (I'm happy to be proved wrong on this point if that's not the case )

Any suggestions gratefully received.

Thanks, Iain

 

[Remou]

How about using QueryDef to create or edit a query that can be used with TransferSpreadsheet?

 

[idbr]

Hi,

Managed to cobble together a process, thought I'd post it here in case it was useful to anyone else.

The solution I've come up with uses a base query and then restricts the output of this query by stitching together the criteria from both the form and subform recordsources.

Effectively this just recreates the relationship between the form and the subform in a query.

Here's how it works:

'---------------------------------------------------------------------------
    'Grab the query criteria and the join type from the two form's recordsources
    '---------------------------------------------------------------------------
    
    'Join type for query
    strJoinType = "INNER"
    If InStr(1, "LEFT", frm.RecordSource) <> 0 Then strJoinType = "LEFT"
    
    'Criteria for Company selections
    strCompanyCriteria = Right(frm.RecordSource, Len(frm.RecordSource) - InStr(1, frm.RecordSource, "WHERE") - 5)
    strContactCriteria = Right(sfrm.RecordSource, Len(sfrm.RecordSource) - InStr(1, sfrm.RecordSource, "WHERE") - 5)

    If IsNull(strContactCriteria) Then
    
        strCriteria = strCompanyCriteria
        
    Else
    
        strCriteria = "WHERE (" & Replace(strCompanyCriteria, ";", "") & _
                        " AND " & Replace(strContactCriteria, ";", "") & ")"
        
        
    End If

    'Grab the query and change the SQL
    Set qryDefOutput = CurrentDb.QueryDefs("qryFilterOutput")
    
    qryDefOutput.SQL = "SELECT DISTINCT tblCompanies.CompanyName, tblCompanies.Address1, " & _
                        "tblCompanies.Address2, tblCompanies.Town, tblCompanies.County, " & _
                        "tblCompanies.Postcode, tblCompanies.Switchboard, tblCompanies.Fax, " & _
                        "tblCompanies.WebAddress, tblIndustrySectors.IndustrySectorDescription, " & _
                        "tblContacts.Title, tblContacts.FirstName, tblContacts.Surname, tblContacts.DirectDial, " & _
                        "tblContacts.Email, tblContacts.JobTitle " & _
                        "FROM tblIndustrySectors INNER JOIN " & _
                        "(tblCompanies " & strJoinType & " JOIN tblContacts ON " & _
                        "tblCompanies.CompanyID = tblContacts.CompanyID) ON " & _
                        "tblIndustrySectors.IndustrySectorID = tblCompanies.IndustrySectorID " & _
                        strCriteria & ";"

This post is deliberately a bit skimpy, I'd be happy to provide further explanation if requested.

Cheers, Iain