Change Report Caption for Printing to Pdf

[ebarrera]

Sorry to have to repeat this question, but I have spent about a week trying to figure this one out. Here is what I need my database to do.

I created a database in Access 2000 and am currently working on it in Access 2002. The database generates agreements for each faculty member in our campus. Currently we print them to pdf manually and then printing it to paper to be mailed out. We are now moving towards emailing them and creating a logging system to track versions of the agreement. Below are the steps I need to accomplish and I am currently stuck at Step 3 (see below). Step 4 is another challenge, but I need to get over this hump first

Step 1: generate agreement data & assign agreement version to the data.
Step 2: compare data, if different, print the report and send data to log.
Step 3: print file to pdf, save file to a shared drive with a unique file name
Step 4: grab file and email to employee, payroll and department

I have created a formula within a query that creates the pdf file name. I need to somehow send that value to the report caption within the report. I have read a few postings here and online and cannot find a solution that matches my dilema. The report that I created is based on the query that holds the pdf file name. The query itself may have multiple lines of data of which each contains the same pdf file name. I figured this would be a problem when passing values to a report, so I decided to create a query that groups the data and send it to a temp table, giving me just one value of the pdf file name.

I noticed that the Me.Caption = "String Name" works but is only good for one file name. Does anyone know the vb code for the data value located in a table? Table Name = CurrentAgreementInfo, Field Name = "PdfFileName".

Any help is greatly appreciated...Thanks.

 

[HiTechCoach]

I use this free utility to create PDF's


Report To PDF


Here is an example doing what you want:

Sending Emails To A Group, One Person At A Time

 

[ebarrera]

Thanks HiTech Coach, I was able to change the printer and hard code the caption by assigning the caption to a string ("test") and it worked. Except that I want instead of the string I want the value tha is on the report. The text box on the report is called PdfFileName and the source is called PdfFileName. Since Reports!Faculty_Agreement.Caption grabs the value in the caption property. I assumed that Reports!Faculty_Agreement!PdfFileName.Value would get me the value I need. I was wrong. I am new to this VB thing and not sure the correct syntax....your help is greatly appreaciated. Thanks.

DoCmd.OpenReport "Faculty_Agreement", acViewDesign, , , acHidden
Reports!Faculty_Agreement.Caption = PdfFileName
DoCmd.Close acReport, "Faculty_Agreement", acSaveYes
DoCmd.OpenReport "Faculty_Agreement"

 

[HiTechCoach]

I assumed that Reports!Faculty_Agreement!PdfFileName.Value would get me the value I need.

That would only have a value with the report is printed or previewed, not in design view.

In your code you have the line:

Reports!Faculty_Agreement.Caption = [b]PdfFileName[/b]

Where do you DIM the variable PdfFileName and how does the variable get a value?


What PDF creation software are you using?

 

[ebarrera]

Thanks, it makes sense, the data cannot exist until you open the report.

I also thought of declaring a variable (as string) and passing the value to it, but do not know the syntax for this. I tried the following code and did not work

Dim newCaption as String

newCaption = Queries!Faculty Agreements - 09c Current Agreement Info!PdfFileName

DoCmd.OpenReport "Faculty_Agreement", acViewDesign, , , acHidden
Reports!Faculty_Agreement.Caption = newCaption
DoCmd.Close acReport, "Faculty_Agreement", acSaveYes

DoCmd.OpenReport "Faculty_Agreement"

 

[ebarrera]

Forgot to answer the 2nd question, I am using CutePDF. I change the printer thru the function called ChangeToCutePDF that was in the code that you referred me to in an earlier post. I actually preview the agreement before I decide to print to pdf.

We have a shared drive which contains folders for each department. At the moment I want to send it to the printjob folder, so that i can grab it an email it through code. Later, I plan to change it and automatically route the file to the appropriate department folder and then be able to find it and email it.