CreateQueryDef - problem

[Chunkus]

Hi

I'm trying to export the results of a filtered subform into excel. I can export the entire subform ok, but have not managed to export only the filtered records. I'm trying to get the code below to work, BUT when I run it I receive an error message:
3129
Invalid SQL Statement; Expected 'Delete', 'Insert', 'Procedure', 'Select', Or 'Update

Within the code below:
"MySubFrm.RecordSource" is returning the correct recordsource name (A query named: qryCmtsDetail)

Really not sure what I am doing wrong. Would appreciate any help . Thx for looking!!!!!

On Error GoTo errHandler
    Dim MyMainFrm As Form
    Dim MySubFrm As Form 
    Set MyMainFrm = Forms!frmCmtsDetail
    Set MySubFrm = MyMainFrm.Controls("ctrlCmtsDetail").Form

    Dim qdf As QueryDef
    DoCmd.DeleteObject acQuery, "qryTemp"
    MsgBox MySubFrm.RecordSource
    Set qdf = CurrentDb.CreateQueryDef("qryTemp", MySubFrm.RecordSource)
    MsgBox qdf
    DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, "C:\Commitments.xls", True

exitHandler:
Exit Sub

errHandler:
If Err.Number = 7874 Then
Resume Next
Else
MsgBox Err.Number & " - " & Err.Description
Resume exitHandler
End If

 

[PWise]

how about

dim sqlstr as string
sqlstr = CurrentDb.QueryDefs(MySubFrm.RecordSource).sql
Set qdf = CurrentDb.CreateQueryDef("qryTemp", sqlstr)

 

[Chunkus]

Thank you very much. Fixed abover error, but my code did not do what I wanted it to do!!!! It is still exporting everything, even though the subform is filtered.

The code below must be picking up the pre-filtered SQL.
sqlstr = CurrentDb.QueryDefs(MySubFrm.RecordSource).sql

How do I access the filtered SQL? Can I access the sql of the openrecordset?

Thx