Export Filtered (Sub)Form

[mjmact]

I can't figure out how to do this, or even if it can actually be done. I have a form setup with some controls and a sub-form setup as a datasheet. The controls let the user filter the data on the datasheet sub-form (as well as allowing them to do other things). Once the choose what they want as the filters, and the datasheet has had the filters applied accordingly, I would like to allow the user to export the contents of the datasheet subform in its filtered condition (i.e. only export what is displayed with the filters active) Is there a good way to do this? Any help is greatly appreciated.
Thanks

 

[VicRauch]

What it sounds like to me is you have a query (SQL string) that is used for the RecordSource of the subform. You then build a filter to apply to the subform based on the contents of those text boxes on the main form. Therefore, what you basically have is a query (the record source) that you can add a WHERE clause to same as the filter for the subform, that you can then use as an SQL string to export the data you want.

Once you get the SQL string you want to export the data, you will then need to put that string into a query object so you can use that query object name in the Table Name field of the TransferSpreadsheet operation. To create a new query within VBA code you will need to be sure that the Microsoft 3.6 DAO object Library is referenced. Within the VBA editor, use the menu Tools, option References. If you don't see DAO then scroll down until you find it, put a check mark in the box, and then close the References dialog box.

Now the code will look something like this. Be sure to use your own names.
Dim MyQuerydef as DAO.QueryDef

Set MyQueryDef = Currentdb.CreateQueryDef("MyQueryName", "SELECT * FROM Table1;")

You can either put the SQL in as I did, or reference a variable name you have already used to build the SQL into.

 

[merce33]

Vic:

What you posted is the exact situation that I am in. Actually, even though my code is messy, it turns out that i tried to do exactly what you instructed in your post.
But I am having a tough time adding the SQL for the Filter and OrderBy clauses to the SQL of my already existing subform RecordSource (a query).
I noticed your post is much simpler... but how do you get the Filter and OrderBy clauses into "Table1"?

Private Sub lblHeaderItem3_Click()
'On Error GoTo HandleError

    Dim db As DAO.Database
    Dim frm As Form             'reference to the NavPaneFields subform
    Dim qry As DAO.QueryDef     'reference to the NavPaneFields subform recordSource
    'new SQL and original SQL strings. need the original because it was saving the new SQL
    Dim strSQL, strOriginalSQL As String
    'portions of the SQL string containing the strings in the filter & orderBy properties
    'of the NavPaneFields subform
    Dim WHEREclause, OrderByClause As String
    
    Set db = CurrentDb
    Set frm = Forms!frmMainFunctions.NavPaneFields.Form
    Set qry = db.QueryDefs(frm.RecordSource)
    
    strOriginalSQL = qry.SQL
    strSQL = qry.SQL
    
    'build the SQL WHERE clause from the NavPaneFields subform's Filter property
    'if Filter clause is empty do nothing, if not, then strip the query name from the fields
    WHEREclause = IIf(IsNull(frm.Filter) Or Len(frm.Filter) = 0, "", Replace(frm.Filter, qry.Name & ".", ""))
    'add the filter contents to a valid SQL where clause
    WHEREclause = "WHERE " & IIf(Len(WHEREclause) = 0, "", WHEREclause & " AND ")
    'build the SQL ORDERBY clause from the NavPaneFields subform's OrderBy property
    'if no order by, finish the SQL by adding semi colon, if not add ORDERBY from form then semi-colon
    OrderByClause = IIf(IsNull(frm.OrderBy) Or Len(frm.OrderBy) = 0, ";", ", " & frm.OrderBy & ";")
    
    'insert the new clauses into the RecordSource query's SQL
    'replace old where clause with newly constructed one
    strSQL = Replace(strSQL, "WHERE ", WHEREclause)
    'add the order by clause on to the end
    strSQL = Replace(strSQL, ";", OrderByClause)
    
    qry.SQL = strSQL
    
    'export to excel
    DoCmd.OutputTo acOutputForm, qry.Name, acFormatXLS, , True
    'restore original SQL to the query so that it is left unmodified
    qry.SQL = strOriginalSQL
    
ExitSub:
    'On Error Resume Next
    qry.Close
    db.Close
    Set db = Nothing
    Set frm = Nothing
    Set qry = Nothing
    Exit Sub

HandleError:
    MsgBox Err.Description
    GoTo ExitSub
    
End Sub

 

[merce33]

sorry, copy & paste error in my previous post.

DoCmd.OutputTo acOutputForm

should read:

DoCmd.OutputTo acOutputQuery