need to code a macro to run an Access db update and store output?

[BCS@AU]

I am trying to write code so that I can have a scheduled task run a macro to
perform the following steps:

1. Run DB update
2. Print a file to Adobe PDF Writer
3. Save the Adobe PDF file in a directory sequentially numbered (using the
date in the mmddyyyy format as the file name)

 

[Steve Schapel]

BCS,

It is not really clear what you want, I'm afraid. What does "Run DB
update" mean? And when you say "print a file", presumably you are
referring to an Access report?

 

[BCS@AU]

Sorry for the lack of information.

Macro should:

1. Run 3 access reports sequentially.
2. Save each report.
3. Print each report using the Adobe PDF Writer (therefore storing the file
on a shared drive space with a file name similar to
g:\\XXXXfolder\YYYYfolder\reportnamemmddyyyy.pdf)

Does that clear up my question any?

 

[Steve Schapel]

BCS,

Because of the variable naming requirement for the PDF files, this will
not be easy with a macro, and you will be better to use a VBA procedure
to do the "work", typing it as a Function rather than a Sub, and then
use a RunCode action in your macro to run the function.

In the design view of each report, go to the File|PageSetup menu, and
select Specific Printer and nominate your PDF driver. Most PDF writers
allow the option to specify a predetermined file name for the PDF output
file. I would assume the Adobe one provides for this. I think the best
approach is to output the PDF for each report, one at a time, and then
rename the file. Something like this...

Public Function PrintPDF()
Dim OutputFileName As String
Dim SaveFileName As String
Dim ReportName As String
OutputFileName = "G:\XXXXfolder\PreDetermined.pdf"
ReportName = "FirstReport"
DoCmd.OpenReport ReportName
SaveFileName = "G:\XXXXfolder\" & ReportName &
Format(Date,"mmddyyyy") & ".pdf"
Name OutputFileName As SaveFileName
ReportName = "SecondReport"
DoCmd.OpenReport ReportName
SaveFileName = "G:\XXXXfolder\" & ReportName &
Format(Date,"mmddyyyy") & ".pdf"
Name OutputFileName As SaveFileName
ReportName = "ThirdReport"
DoCmd.OpenReport ReportName
SaveFileName = "G:\XXXXfolder\" & ReportName &
Format(Date,"mmddyyyy") & ".pdf"
Name OutputFileName As SaveFileName
End Function

 

[BCS@AU]

Hmmm.. Guess I am much to much of a novice to handle this one. I can't make
it work and the more I try, the more confused I become.

Could you explain.......

"typing it as a Function rather than a Sub, and then use a RunCode action in
your macro to run the function."

How am I supposed to do this?

 

[BCS@AU]

OK... This is what I have.......

Public Function PrintPDF()
Dim OutputFileName As String
Dim SaveFileName As String
Dim ReportName As String
OutputFileName = "G:\Service Centers & Auxiliaries\Service Center
Applications\Daily Reports Folder\firstreport.pdf"
ReportName = "GIDChngs_Separations"
DoCmd.OpenReport ReportName
SaveFileName = "G:\Service Centers & Auxiliaries\Service Center
Applications\Daily Reports Folder\" & ReportName & Format(Date, "mmddyyyy") &
".pdf"
Name OutputFileName As SaveFileName
ReportName = "GIDChngs_Transfers"
DoCmd.OpenReport ReportName
SaveFileName = "G:\Service Centers & Auxiliaries\Service Center
Applications\Daily Reports Folder\" & ReportName & Format(Date, "mmddyyyy") &
".pdf"
Name OutputFileName As SaveFileName
ReportName = "Possible_Retirees"
DoCmd.OpenReport ReportName
SaveFileName = "G:\Service Centers & Auxiliaries\Service Center
Applications\Daily Reports Folder\" & ReportName & Format(Date, "mmddyyyy") &
".pdf"
Name OutputFileName As SaveFileName
End Function


It works 'til it gets to the second "Name OutputFileName As SaveFileName"
then I get an error code 53. My mistake is problably elementary, but I can't
figure it out. Please help me.

Thanks

 

[Steve Schapel]

BCS,

This assumes that your PDF writer is outputting the report to G:\Service
Centers & Auxiliaries\Service Center Applications\Daily Reports
Folder\firstreport.pdf, which therefore I guess would be what it is set
up to do by default. This is managed within the PDF writer software,
and as far as I know can't be controlled from within your Access
application. The only other thing I can think of that might be a
problem here is that the process of outputting the PDF file takes some
time, and it is possible that your code is trying to rename the file
before its creation has been completed. Sorry, I can't give a
difinitive answer here. I think you might need to experiment a bit, by
commenting out various sections of the code, for example to see whether
the firstreport.pdf is in fact being created.