Access to PDF with a twist 2

[Legions]

Dear Tek-Tips community,

I have a problem, and wish that someone could direct me in the right direction.

I have 500 page report. Each page has one Sales Rep on it. This report is pretty plain-jane in Ms-Access 2000.

I have Adobe Acrobat PDF v7 on my machine - I have send the 500 page report via printing to a false printer easily. It saves all 500 pages into one PDF.

The problem is that - they want to split the report so that each page is it's own PDF and it creates each one using the REP ID (Found on report).

I'd like to automate this process - as having someone go in and making a PDF for each page manually and then naming it correctly is a pretty long process.

Any help would be greatly appreciated.

- L the IS Manager

 

[Spenney]

I have used the PDF and Mail Class Library for Access from ACG Soft to simplify the type of process you describe.

Check out...

http://ourworld.compuserve.com/homepages/attac-cg/

 

[Legions]

Thanks, I looked over the web site it's pretty good thus far. Was hoping I was over looking a simple process to accomplish this. They only charge $55 for the standard version.

- L

 

[kamfl610]

Why don't you write a macro that goes through every rep in a table and creates the report for only that rep and then have it export it out with the reps name and today's date and then each one would have their own pdf report. So the only thing the person would do is click on the macro and run the process.

 

[Legions]

Ok -

So the Primary Query that feeds the mega report has 20 fields to it and goes to the report itself.

I made a 2nd query off the report feeding query that lists all 500 reps / date combo. Within this query I made a Report Title column to build the name of each report I want generated.

Rep12345_15869.pdf
Rep49340_15869.PDF
Rep20394_23934.PDF

So both queries have 500+ entries.. the report shows page 1- 528. Each Rep has one page to themselves (it's a summary).

The Access -> PDF link above is nice - but it's a bit more then I think I need to this.

I'd like to pursue the macro route. Would the user have to select each Rep as the filter 500+ times? So it prints that rep's page makes a PDF?

Or is there a way to run the Macro for EACH line of the 2nd query.

Thanks,

- L

 

[BoxHead]

L

I think I've got a solution.

I created a report called Sales_Report. I didn't have a table of sales people so I used a table with 40 customers and in the page setup, selected 'Specific Prnter' and chose PDF. On the printer property tab, I deselected the four checkboxes for 'asking for filename' and 'show PDF results' etc. so that I wouldn't get 40 dialog box prompts. I'm guessing you don't want 528 prompts either.

On the click event of a button on my test form, I declared a string variable myCurName and opened a recordset (SELECT DISINCT customer name....) to pull the names of the customers. Moving through the recordset resets myCurName with each Customer name and uses that as a filter for the report. The code also renames "Sales_Report" with the name "Sales_Report_" & the customer name and the current date in a YYYYMMDD format. It then opens the report and prints it while keeping it hidden, closes the report, renames the report back to "Sales_Report" and goes on to the next name to do it again.

It takes about 12 seconds to run this for the 40 reports.

It took some monkeying around with the names because I had commas and dots and apostrophes so I ended up using a series of replace functions. There's got to be a better way to do that but this worked for me.

Also, I'd imagine that when you run these reports, you'd like to have them saved in a folder with the date, but I was unable to print them anywhere but My Documents and then had to drag them into a specific folder. It's better than 528 save prompts but someone may be able to crack that nut for you.

Here's the code:

Private Sub c1_Click()
Dim myDb1 As DAO.Database, myRst1 As DAO.Recordset, myFile As String, myCurName As String

DoCmd.Close acReport, "Sales_Report"

Set myDb1 = CurrentDb
Set myRst1 = myDb1.OpenRecordset("SELECT DISTINCT tblcust.cName FROM tblCust;", dbOpenDynaset)
myRst1.MoveFirst



Do While Not myRst1.EOF
myCurName = Replace(myRst1.Fields(0), ",", "")
myCurName = Replace(myCurName, "'", "")
myCurName = Replace(myCurName, ".", "")

myFile = myCurName & "_" & Year(Date) _
& Format(Month(Date), "mm") & Format(Day(Date), "dd")

DoCmd.Rename "Sales_Report_ " & myFile, acReport, "Sales_Report"
DoCmd.OpenReport "Sales_Report_ " & myFile, 0, , "[cName] = " & "'" & myCurName & "'", acHidden
DoCmd.Close acReport, "Sales_Report_ " & myFile, acSaveYes
DoCmd.Rename "Sales_Report", acReport, "Sales_Report_ " & myFile
DoCmd.Close acReport, "Sales_Report", acSaveYes
myRst1.MoveNext
Loop

End Sub

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[Legions]

Thank you John! Appreciate the code.