One query for report and Excel

  • Thread starter Thread starter Leif
  • Start date Start date
L

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
 
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.
 
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
 
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.
 
Back
Top