Export the filtered datasheet issue

[Sandy1992]

Hi all,

I have a form named "Search"

In it their is a combobox "cboEmp", which is used to filter the data and display in datasheet.

Now, the filter is working fantastic. The problem persists when Export button comes into picture.

On clicking Export, the filtered data should be exported to the excel.

what exactly is happening is, the data are getting exported in excel , BUT not the current filtered data.

to get the current filtered data I have to close the form and reopen it and then only the data will be fetched, which should not happen.

I don't know where I am going wrong.

Please check and suggest. :)

'This is the code I am using under OnClick event of Export button

 DoCmd.OutputTo acOutputForm, "Tasks SubForm", acFormatXLS, "Tbl1XLS.xls", True

[zmbd]

please post the code used to set the form's filtering from the combobox too.

thnx
z

[Sandy1992]

Hi z,

Thanks for atleast replying :)

The code on AfterUpdate event of the combobox is as shown:

Private Sub cboEmployee_AfterUpdate()
 Dim Myemp As String
    Myemp = "select * from Tasks where ([EmployeeId] =" & Me.cboEmployee & ")"
    Me.Tasks_subform4.Form.RecordSource = Myemp
    Me.Tasks_subform4.Requery  
End Sub

Please note: I am getting all the data exported in Excel.

Also, the code in AfterUpdate() is working fine, as it is filtering the data as per the change.

[zmbd]

"tasks_subform4"
vs
"Tasks Subform"

What is the difference between these? Do you have one form named "Tasks Subform" and another named "tasks_subform4"?

If this had been a simple filter, then I had a ready answer for you; however, in this case I'll have to do a little digging.

[jforbes]

I really don't know what will happen if you try to output a filtered Subform. It sounds problematic as DoCmd.OutputTo is quite finicky as it is, at least for me.

If you are still stuck on this, I would recommend a slightly different approach. I usually export to PDF. So I mocked this up from some code I use, to export to Excel and it seems to be working. This approach would require you to build a Report to export:

Private Sub Command0_Click()
 
    Dim sReportName As String
    Dim sWhere As String
 
    sReportName = "OrderHistory"
 
    ' Define Where Clause
    If Len(Me.txtStart.Value) > 0 Then sWhere = sWhere & " AND [CreatedDate]>=#" & Me.txtStart.Value & "#"
    If Len(Me.txtEnd.Value) > 0 Then sWhere = sWhere & " AND [CreateDate]<=#" & Me.txtEnd.Value & "#"
    If Len(sWhere) > 0 Then sWhere = Right(sWhere, Len(sWhere) - 5)
 
    ' Export Report
    DoCmd.OpenReport sReportName, acViewPreview, , sWhere, acHidden
    DoCmd.OutputTo acOutputReport, sReportName, acFormatXLS, "C:\Temp\TestExport.xls", True
    DoCmd.Close acReport, sReportName
End Sub