One report producing multiple unique PDF files

[mehrhardt]

My situtation:
I'm not very good at Access. I've created a report (R_SalesHist) listing 1000 customers sales history. Each customer's sales history fits nicely on a single page. When I run R_SalesHist I need it to produce a PDF file for each customer number at the page break. The PDF file names need to be uniquely named as "CustomerNumber ".PDF. So I'll have 1000 PDF files when the process runs. I do have a small budget and could buy a package if there is one that keeps me from having to write code.

My questions:
I have downloaded CUTEPDF to have the print to PDF ability. I know you can run a query to pick each customer number then run the report for that customer number - but not how. Can some one show me an example macro/code to do this please?

CUTEPDF uses the report name R_SalesHist as the file name for the PDF it creates. If I run the report multiple times how do I get the PDF files uniquely named as "CustomerNumber ".PDF?

[BradHodge]

I'm assuming you have a command button on a form that opens your report?

If this is the case, you could try adding this code to the command button...

Dim strDate as string
Dim strCustomer as string
strDate= Format(Date, "mm-dd-yyyy")
strCustomer= Forms![YOUR FORM NAME]![CUSTOMER FIELD NAME] _
' This should put the customer's number into a string
 
 
If MsgBox("Do you want to save this report to your PC?", vbYesNo) = vbYes _
'Check to see if saving copy of report is desired.
 
DoCmd.OutputTo acOutputReport, "R_SalesHist", acFormatPDF, "C:\SalesHistory\strCustomer_" & strDate & ".PDF" _ 
'If so, export to PC.
 
MsgBox ("Your report has been saved in C:\SalesHistory.")     'Alerts User to where report is saved.
 
End If

NOW... All that said, I have not done this using PDF format. If CUTEPDF will allow you to do this, then the above should work. However, if it won't, you could use the same code above except replace "PDF" with "RTF" or "SNP"

Hope this helps,
Brad.

[mehrhardt]

I'm the only one running the report. So, I was just going to run the report. I didn't know I needed a button on a form. I'll try creating the form and button if that is the easy way. CUTEPDF prompts for a folder and file name is that an issue? Is there a PDF creator you would recommend.

Thanks for the help and the quick response

[puppydogbuddy]

I'm the only one running the report. So, I was just going to run the report. I didn't know I needed a button on a form. I'll try creating the form and button if that is the easy way. CUTEPDF prompts for a folder and file name is that an issue? Is there a PDF creator you would recommend.

Thanks for the help and the quick response

See this link for Tony Toews' (Microsoft MVP) recommendations for implementing PDF.

http://www.granite.ab.ca/access/pdffiles.htm

[mehrhardt]

Thanks for the PDF reference. I bought PDF995. I changed the example they have to allow me to control the outfile name and reduced the sleep delay to make it run quicker.

It's creating about 16 PDFs a minute but I might have the delay set to high still so it may prove faster.