Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 14 votes, 5.00 average. Display Modes
Old 07-01-2013, 09:37 AM   #1
MKaprielian
Newly Registered User
 
Join Date: Feb 2012
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
MKaprielian is on a distinguished road
Go From Docmd.openreport With Wherecondition To Docmd.transferspreadsheet, Access 201

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

MKaprielian is offline   Reply With Quote
Old 07-01-2013, 10:36 AM   #2
billmeye
Access Aficionado
 
Join Date: Feb 2010
Location: New Haven CT, USA
Posts: 542
Thanks: 0
Thanked 119 Times in 116 Posts
billmeye is on a distinguished road
Re: Go From Docmd.openreport With Wherecondition To Docmd.transferspreadsheet, Access

You should set the criteria of your query and export that to your spreadsheet. You don't need the report.
billmeye is offline   Reply With Quote
Old 07-01-2013, 10:42 AM   #3
MKaprielian
Newly Registered User
 
Join Date: Feb 2012
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
MKaprielian is on a distinguished road
Re: Go From Docmd.openreport With Wherecondition To Docmd.transferspreadsheet, Access

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.

MKaprielian is offline   Reply With Quote
Old 07-01-2013, 03:47 PM   #4
billmeye
Access Aficionado
 
Join Date: Feb 2010
Location: New Haven CT, USA
Posts: 542
Thanks: 0
Thanked 119 Times in 116 Posts
billmeye is on a distinguished road
Re: Go From Docmd.openreport With Wherecondition To Docmd.transferspreadsheet, Access

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.
billmeye is offline   Reply With Quote
Old 07-01-2013, 05:43 PM   #5
MKaprielian
Newly Registered User
 
Join Date: Feb 2012
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
MKaprielian is on a distinguished road
Re: Go From Docmd.openreport With Wherecondition To Docmd.transferspreadsheet, Access

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
MKaprielian is offline   Reply With Quote
Old 07-02-2013, 05:43 AM   #6
billmeye
Access Aficionado
 
Join Date: Feb 2010
Location: New Haven CT, USA
Posts: 542
Thanks: 0
Thanked 119 Times in 116 Posts
billmeye is on a distinguished road
Re: Go From Docmd.openreport With Wherecondition To Docmd.transferspreadsheet, Access

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]));

billmeye is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using OpenArgs in Access 2010 DoCmd.OpenReport pt60 Reports 7 05-15-2013 09:12 AM
DoCmd.OpenForm and WhereCondition alpapak Forms 7 07-08-2012 03:05 PM
Runtime error 2046 on doCmd.gotoRecord after doCmd.openReport Alexie Forms 3 06-03-2010 02:58 AM
docmd.outputto vs docmd.transferspreadsheet like Reports 0 11-08-2006 10:28 PM
Access quitting on DoCmd.OpenReport Peter Rallings Modules & VBA 3 03-11-2003 10:34 AM




All times are GMT -8. The time now is 06:20 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World