Exporting filtered form data

[matey56]

Hi all,
I have a form with a search box at the top that filters the records while you type. A user has requested an export of the filtered form data to Excel. I used the ExportWithFormatting Macro and it works fine, but it also exports the search box and the text that's typed into it. How can I do this via DBA instead?

[ranman256]

you need a query with the criteria to export.
build the criteria, then write it to an existing query, then export it.

Public Sub btnReport_Click()
Dim sSql As String, sWhere As String
Dim qdf As querydef
Const kQRY = "qsFormFilter"    'here is the query we use ...in the report too

sWhere = "1=1"


    'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value


 'use it to filer the form records
if sWhere = "1=1" then
  me.filterOn = false
else
  me.filter = sWhere
  me.filterOn = true
endif



    'or 'BUILD the query from the 'where'
Set qdf = currentdb.querydefs(kQRY)
qdf.Sql = "SELECT * FROM tblCompany WHERE " & sWhere
qdf.Close


   'open the query or report here!
DOCMD.openquery kQRY

'export
vFile = "c:\temp\MyData.xlsx"
DoCmd.TransferSpreadsheet acExport,  acSpreadsheetTypeExcel12xls, kQRY, vFile, True,"qry1"

end sub

[matey56]

Sorry, what do you mean by build criteria? I'm an extreme novice.