Go From Docmd.openreport With Wherecondition To Docmd.transferspreadsheet, Access 201 (1 Viewer)

MKaprielian

New member
Local time
Today, 06:13
Joined
Feb 17, 2012
Messages
6
I have searched the forums and found some scenarios that seem similar to mine but I can't see how to apply them to my situation.
My forum search was "WhereCondition for DoCmd.TransferSpreadsheet"


I have code that calls the DoCmd.OpenReport in a loop passing different a different WhereCondition each time through the loop.

DoCmd.OpenReport str_ReportName, acViewPreview, , MyWhereCondition

This generates a nice collection of reports customized by my WhereCondition.

I now want to generate an Excel spreadsheet for each report as I loop through the long list of WhereCondition values.

Very unfortunately the DoCmd.TransferSpreadsheet does not have the ability for the WhereCondition that the DoCmd.OpenReport has.
This is very clearly stated on the MS site.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, TargetQuery, PathFileName

I'm looking for a simple way to create the Excel file from the loop where I call my DoCmd.OpenReport call.

One way I can think of to accomplish my task is to
--- get the current report's query (it is not built on the fly, it is in the list of Queries)
--- modify the Where clause to include my WhereCondition
--- Execute this modified query.

I've had no success with the above as I've only found code that seems to permanently alter the underlying query and even those attempts have not run cleanly for me


My idea seems simple in theory but I'm finding it difficult to figure out how to do it. Perhaps there is an alternative approach?

I find it hard to believe I'm the first person who needed to do this.

Mark
 

billmeye

Access Aficionado
Local time
Today, 06:13
Joined
Feb 20, 2010
Messages
542
You should set the criteria of your query and export that to your spreadsheet. You don't need the report.
 

MKaprielian

New member
Local time
Today, 06:13
Joined
Feb 17, 2012
Messages
6
I haven't been able to figure out how to grab the query and add the additional filtering info without altering the saved query. An example of doing this would be helpful.
 

billmeye

Access Aficionado
Local time
Today, 06:13
Joined
Feb 20, 2010
Messages
542
I'm thinking that you place basically the same Where statement your using for the report directly as the Criteria for that column in your query. Have you worked with query Criteria in the past? If not, if you could post your query used as the record source for your report along with your current MyWhereCondition I could help you.
 

MKaprielian

New member
Local time
Today, 06:13
Joined
Feb 17, 2012
Messages
6
Hi

I haven't done much with manipulating SQL in Access.

My query is:
SELECT qry_2800_Package_Rollup.*, qry_5000_Selected_Product_Codes.*
FROM qry_2800_Package_Rollup INNER JOIN qry_5000_Selected_Product_Codes ON qry_2800_Package_Rollup.PFC = qry_5000_Selected_Product_Codes.PFC_CODE;

My Where condition Looks like .
PFC_CODE = 154
In the above example, the value 154 is the item I change with each call.

Mark
 

billmeye

Access Aficionado
Local time
Today, 06:13
Joined
Feb 20, 2010
Messages
542
You can add the WHERE directly to your query:
Code:
SELECT qry_2800_Package_Rollup.*, qry_5000_Selected_Product_Codes.*
FROM qry_2800_Package_Rollup INNER JOIN qry_5000_Selected_Product_Codes ON qry_2800_Package_Rollup.PFC = qry_5000_Selected_Product_Codes.PFC_CODE WHERE (((qry_2800_Package_Rollup.PFC)=[Forms]![nameOfForm]![PFC_CODE]));
 

Users who are viewing this thread

Top Bottom