We help IT Professionals succeed at work.

exporting data and filtered data from a subform

Fordraiders
Fordraiders used Ask the Experts™
on
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
Comment
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
Commented:
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 "
Else 
    strSQL = "SELECT * FROM [" & strSQL & "] "
endif

'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

Author

Commented:
Dale,
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
    Me.National_Account_View_subform.Form.RecordsetClone
End If
If Me.TabCtl0.Value = 2 Then
  Me.Government_View_subform.Form.RecordsetClone
End If
If Me.TabCtl0.Value = 3 Then
   Me.Commercial_Manufacturing_View_subform.Form.RecordsetClone
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
        .Workbooks.Add
        .Sheets("Sheet1").Select
       
        .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
        xlApp.Cells.EntireColumn.AutoFit
    End With

Author

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

Commented:
glad  I could help.