One query for report and Excel

[Leif]

I've developed a report request form from which I can specify my filter
criteria and request a preview report or output as Excel. To provide a
filter for my report query I simply refer to the fields from the report
request form. That works fine.

To generate an Excel file I try to open the query as a recordset so that I
can write the information to Excel. However, since the query has references
to the report request form the recordset will not open.

I tried changing my query to add parameters to the query then open the query
for Excel by providing values to the parameters. This works OK.

However, this solution requires me to maintain two queries, one for the
report and one for Excel, returning the same data. Is there a solution so
that I only need to maintain one query instead of two?

Thanks,
Leif

 

[Allen Browne]

The way I normally approach this is to leave the criteria out of the query
(so it returns all records), and use a form where the user can enter their
criteria and click a button to open the report. The button's Click event
builds the WhereCondition to use with OpenReport.

The form has another button for exporting to Excel. This button builds the
same WhereCondition, concatenates it into a complete SQL string, and assigns
it to the SQL property of a query that's just for exports. So:
Dim strSql As String
Dim strWhere As String
Dim strFile As String
strFile = "C:\SomeFolder\SomeFile.xls"
strWhere = "(SomeField = 99) AND (AnotherField = ""xx"")"
strSql = "SELECT * FROM Table1 WHERE " & strWhere & _
" ORDER BY SomeField;"
CurrentDb.QueryDefs("qry4Export").SQL = strSql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, _
"qry4Export", strFile

I find that approach to be easier than walking a recordset, and more
flexible than using parameters in the query.

 

[Leif]

Hi Allen,

Thanks for your help. Your suggestion does work fine. However, one problem
I have with using a where string is in generating a snap report file.

I did not mention this in the OP, but I also allow the user to generate a
Snap report. I use the OutputTo action for the docmd. It is:

DoCmd.OutputTo acOutputReport, "CriticalAlarmsSchedule",
acFormatSNP, gSnapFile, True

After removing the conditions from the query my Snap report shows all
records. The OutputTo does not have a where option. How do you handle this
type of case?

Regards,
Leif

 

[Allen Browne]

Use the Open event of the report to apply the filter:

Private Sub Report_Open(Cancel As Integer)
Dim frm As Form
Dim strWhere As String
If CurrentProject.AllForms("Form1") Then
Set frm = Forms("Form1")
strWhere = ... 'build the filter string from the items on
the form
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

That approach works for OutputTo etc.