We help IT Professionals succeed at work.

exporting data and filtered data from a subform

Fordraiders used Ask the Experts™
exporting data from subform

I have used this code in 2010 and 2003  to export a subform and even if the subform had filtered records, it ONLY exported the filtered data ?

Now it does not in Office 365 ?

DoCmd.DeleteObject acQuery, "qryTemp"
     Set qdf = CurrentDb.CreateQueryDef("qryTemp", Me.All_ExtendedPaymentTerms_subform.Form.RecordSource)
     DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, "C:\Users\" & Racfid & "\Desktop\All_Extended.xls", True
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Cannot say I've ever attempted to do that, but since the filtering performed on the subform, outside the recordsource, is not a part of the recordsource itself, I can hardly believe that would work.

I also don't know why your are deleting the query, and not simply changing its sql string.  You might try:

strSQL = Me.All_ExtendedPaymentTerms_subform.Form.RecordSource

'Generally, your Recordsource is either going to be a table, a saved query, or a SELECT statement
'If it is the latter, then we want to write a new SQL statement that wraps that SELECT statement in ( ) as a subquery
'If one of the former, we just want to wrap the name of the table or query in brackets.
if instr(strSQL, "SELECT") = 1 then
    strSQL = "SELECT SQ.* FROM (" & strSQL & ") as SQ "
    strSQL = "SELECT * FROM [" & strSQL & "] "

'Then apply the filter on the subform, if there is one.
if me.all_ExtendedPaymentTerms_subform.form.Filter <> "" AND _
   me.all_ExtendedPaymentTerms_subform.form.FilterOn = true then
    strSQL = strSQL & "WHERE " & me.all_ExtendedPaymentTerms_subform.form.Filter
end if

currentdb.querydefs("qryTemp").SQL = strSQL

Open in new window


This worked also:
If Me.Dirty Then Me.Dirty = False

Dim rsClone As DAO.Recordset
'Set rsClone = Me.All_ExtendedPaymentTerms_subform.Form.RecordsetClone
If Me.TabCtl0.Value = 0 Then
    Set rsClone = Me.All_ExtendedPaymentTerms_subform.Form.RecordsetClone
End If
If Me.TabCtl0.Value = 1 Then
End If
If Me.TabCtl0.Value = 2 Then
End If
If Me.TabCtl0.Value = 3 Then
End If

If rsClone.EOF Then
MsgBox "No records found."
Set rsClone = Nothing
Exit Sub
End If

    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
        .ActiveSheet.Range("A2").CopyFromRecordset rsClone
        For i = 1 To rsClone.Fields.Count
            xlApp.ActiveSheet.Cells(1, i).Value = rsClone.Fields(i - 1).Name
        Next i
    End With


thanks dale
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

glad  I could help.