# MS Access 2007-10(output file)



## mattheworsak (Aug 22, 2011)

Ok so i have created a report in Access. Im currently saving the report daily to the file location below(on a sharepoint site):

\\cs.eis.afmc.af.mil/sites/1133/Robins/HVM Integrator/Quality Analyst/QA Tracker Reports/Discrepancy Reports/Daily Discrepancies

I now have created a button to automatically save the file to that location, however i want it to change the file name to correspond with the actual current date. Below is the file name i currently use. Im not particualar on how the name gets saved as long as the current date is in there.

QAA Report(Today3-22-12) 

If not i have to go back in and manually:facepalm: rename the file. Is there any way to do this? Attached is a screen shot of my current macro settings for the button i made to save the file to a certain location. Hope all this makes sense.


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Instead of your button running a macro you would let it run an event procedure. Then write a VBA procedure with something like:

```
Private Sub Command0_Click()
    Dim outputFName As String
    outputFName = "\\cs.eis.afmc.af.mil\sites\1133\Robins\HVM Integrator\Quality Analyst\QA Tracker Reports\Discrepancy Reports\Daily Discrepancies\QAA Report(" & Format(Date, "dd-mm-yy") & ").pdf"
    DoCmd.OutputTo acOutputReport, "Table1", acFormatPDF, outputFName, False, , , acExportQualityPrint
End Sub
```
1. Where Command0 is the name of your button.

2. \\cs.eis.afmc.af.mil\sites\1133\Robins\HVM Integrator\Quality Analyst\QA Tracker Reports\Discrepancy Reports\Daily Discrepancies\ is the location where you want to save the file

3. QAA Report(dd-mm-yyyy).pdf is the name of the file

4. Table1 is the name of the report


----------



## mattheworsak (Aug 22, 2011)

That makes perfect sense. Thanks! Now how would i make it so one button will save 6 different files to 6 different locations?


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Just duplicate the file location variable and run the Docmd.Output 6 times.
Eg:

```
Private Sub Command0_Click()
    Dim outputFName1 As String
    Dim outputFName2 As String
    Dim outputFName3 As String
    Dim outputFName4 As String
    Dim outputFName5 As String
    Dim outputFName6 As String

    outputFName1 = "location\filename1(" & Format(Date, "dd-mm-yy") & ").pdf"
    outputFName2 = "location\filename2(" & Format(Date, "dd-mm-yy") & ").pdf"
    outputFName3 = "location\filename3(" & Format(Date, "dd-mm-yy") & ").pdf"
    outputFName4 = "location\filename4(" & Format(Date, "dd-mm-yy") & ").pdf"
    outputFName5 = "location\filename5(" & Format(Date, "dd-mm-yy") & ").pdf"
    outputFName6 = "location\filename6(" & Format(Date, "dd-mm-yy") & ").pdf"

    DoCmd.OutputTo acOutputReport, "Table1", acFormatPDF, outputFName1, False, , , acExportQualityPrint
    DoCmd.OutputTo acOutputReport, "Table1", acFormatPDF, outputFName2, False, , , acExportQualityPrint
    DoCmd.OutputTo acOutputReport, "Table1", acFormatPDF, outputFName3, False, , , acExportQualityPrint
    DoCmd.OutputTo acOutputReport, "Table1", acFormatPDF, outputFName4, False, , , acExportQualityPrint
    DoCmd.OutputTo acOutputReport, "Table1", acFormatPDF, outputFName5, False, , , acExportQualityPrint
    DoCmd.OutputTo acOutputReport, "Table1", acFormatPDF, outputFName6, False, , , acExportQualityPrint
End Sub
```


----------



## mattheworsak (Aug 22, 2011)

Works GREAT! Now that works for Reports. How to export a "form" because i have several pivotcharts that i need to save in the same location. Would i just put "acOutputForm"?


----------



## AlbertMC2 (Jul 15, 2010)

Yes thats right, See:
DoCmd.OutputTo Method (Access)
And the output object Types you can use:
AcOutputObjectType Enumeration


----------



## mattheworsak (Aug 22, 2011)

This worked amazing! Now how would i set it to save at a specific time so it dont have to click a button manually? Hourly, daily, weekly, monthly?


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Your best bet is to create a macro that runs whatever code you want.
Then create a shortcut to Access with the command line option to run that macro.
Then create a scheduled task in windows to run that shortcut at certain times.

What you can do is press Alt+F11 to open the VB Editor
Insert -> Module -> type in your VBA code: (but note the change to "*Function*")
Eg:

```
Function myMacro()
    Dim outputFName1 As String
    
    outputFName1 = "location\filename1(" & Format(Date, "dd-mm-yy") & ").pdf"
    
    DoCmd.OutputTo acOutputReport, "Table1", acFormatPDF, outputFName1, False, , , acExportQualityPrint
    
End Function
```
Once done save the module as runReports (or what ever):
Then back in Access, on the ribbon bar go to Create tab -> Other group -> Macro
Action = RunCode
Function = myMacro()
save the macro as Macro1 (or what ever you want.)

Then the easiest way to create a scheduled task is to click on your newly created macro and drag it to the desktop or to a folder. This will create a shortcut to the macro (called a .MAM file)
Then in Control Panel -> scheduled tasks create a new task to run this macro shortcut file at specific times/dates.


----------



## mattheworsak (Aug 22, 2011)

ok so now im tryin to create a button that generates an emil to send multiple reports. However how can i attach more then one report to the same email? Here is wat ive got to send the one:

Private Sub Command133_Click()
On Error GoTo Command133_Click_Err
 DoCmd.SendObject acReport, "QAA Report(Findings Today)", "PDFFormat(*.pdf)", "[email protected]", "", "", "QAA EOS Apr-11-2012", "Worked on the following today: - - - - - Matthew J. Orsak Quality Assurance Analyst (QAA) M1 Support Services C-130 Integrator Support Bldg 20121, RAFB", True, ""

Command133_Click_Exit:
Exit Sub
Command133_Click_Err:
MsgBox Error$
Resume Command133_Click_Exit
End Sub


----------



## AlbertMC2 (Jul 15, 2010)

You cannot do it with sendobject the way you want.
But there are workarounds.
Run your reports as you have been doing in the previous posts and save them to files on the hard drive then follow this site's suggestion which will attach the reports saved to one email.


----------

