I have a reporting interface for users to create filtered reports. I need to find the best way to export the data in the filtered reports to excel.
An unbound form contains 5 combo boxes that a user can select to filter a report. The filtered report is generated with the on-click event of a button on the form.
DoCmd.OpenReport "rpt_Admits", acViewReport, , strWhere
The report opens with the correct filters applied and the user can click a button on the report header to "print report".
The users want to be able to export the filtered data to excel, so I need to add another button to the report header to "export data".
As a temporary solution, I am using this code to export the report in excel;
DoCmd.OutputTo acOutputReport, "rpt_Activity", acFormatXLS, , True
It works fine and gets the job done, but it's not great. Since my report has several grouping levels, exporting the report is not an ideal way to provide users spreadsheet of data for them to manipulate. Instead, I would like to export the filtered record source that was used to generate the report.
Can you advise me on the best way to do this? From my report, can I access the 'strWhere' variable I used on the previous unbound form? Or is there a better way to access the filtered record source?
Thanks for your help.