I know what I need to do but don't know how to.

[EVGData]

Ok, so I have a DB and I have a client that has fields she needs to edit on a weekly basis. After closing the DB, I have it export A PDF File into their Dropbox folder. The only downside is, it goes by years. So there is a PDF of data for every year they have been open. 2009- current. When 2016 started I had to have them send me the database and I had to add another line to the exit VBA so it would add this year to the export list. My wonderful question for all you helpful people is this:

If there a way to have a loop in the VBA where it exports report "Date Report - 2009" Then goes back through the code and adds one number and then exports "Date Export - 2010"?



This is my code...It works, just having to go in every year to add a new line of code will get old. TIA

DoCmd.OutputTo acOutputReport, "DateExport2009", acFormatPDF, "C:\Dropbox\Database\Exported Files\Master Data - 2009.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "DateExport2010", acFormatPDF, "C:\Dropbox\Database\Exported Files\Master Data - 2010.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "DateExport2011", acFormatPDF, "C:\Dropbox\Database\Exported Files\Master Data - 2011.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "DateExport2012", acFormatPDF, "C:\Dropbox\Database\Exported Files\Master Data - 2012.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "DateExport2013", acFormatPDF, "C:\Dropbox\Database\Exported Files\Master Data - 2013.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "DateExport2014", acFormatPDF, "C:\Dropbox\Database\Exported Files\Master Data - 2014.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "DateExport2015", acFormatPDF, "C:\Dropbox\Database\Exported Files\Master Data - 2015.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "DateExport2016", acFormatPDF, "C:\Dropbox\Database\Exported Files\Master Data - 2016.pdf", False, , , acExportQualityPrint

You will notice it is exporting a report...the ONLY difference between those reports are the dates on them. (ie. Only records from 2016) Would it be easier to have ONE report and have some VBA run dates in 2009, output a report, then run a report for 2010, then output that report? Otherwise Every year I am making a new report with only the dates changing, then adding that new line of code to the Exit button on the DB.

Thank so much!
-Joel

[JoeM]

OK. Here is how I would do it, first do the following:

Create a query that has all the data you need for the report, but has not assigned any Year criteria to it (so it will return data for all years). Let's call this: ExportQuery1

Create a second query based on ExportQuery1 that returns all fields from the first query, and has year criteria in it. Let's call this: ExportQuery2
Change the view on this query to SQL View, and copy the SQL code for future reference.

Name your report DateExport, and use ExportQuery2 as the data source for this report.

So, we only have this one report, and we are going to use VBA to change the year criteria and file export name. The VBA code will look like this:

    Dim mySQLCode As String
    Dim myYear As Long
    Dim myFileName As String
    
'   Loop through all years to current date
    For myYear = 2009 To Year(Date)
    
'       Build SQL code
        mySQLCode = "SELECT [ExportQuery1].* FROM [ExportQuery] WHERE [YearField]=" & myYear
    
'       Assign SQL code to query
        CurrentDb.QueryDefs("ExportQuery2").SQL = mySQLCode
    
'       Build export file name
        myFileName = "C:\Dropbox\Database\Exported Files\Master Data - " & myYear & ".pdf"
        
'       Export report
        DoCmd.OutputTo acOutputReport, "DateExport", acFormatPDF, myFileName, False, , , acExportQualityPrint

    Next myYear

So, if you see the line called "Build SQL Code", this is the part that builds the SQL code based on the year. This is the part that we want to make look like the SQL code you saved up above. So edit this line so that it looks the same as what you saved, right up to the part of the year (we will be using a variable in the VBA code instead of hard-coding it).

So with this set-up, you only need two queries and one report to run and export these reports for all years.

[redbull]

DateYr = 2009 
Do until DateYr = 2017
DoCmd.OutputTo acOutputReport, "DateExport2009", acFormatPDF, "C:\Dropbox\Database\Exported Files\Master Data - " & DateYr & ".pdf", False, , , cExportQualityPrint
DateYr = DateYr + 1
Loop

Or

CurrentYr = Format(date, "yyyy")
DateYr = 2009 
Do until DateYr = CurrentYr
DoCmd.OutputTo acOutputReport, "DateExport2009", acFormatPDF, "C:\Dropbox\Database\Exported Files\Master Data - " & DateYr & ".pdf", False, , , cExportQualityPrint
DateYr = DateYr + 1
Loop