Automating Report Creation 1

[Flo79]

Hi There,

Here is a question that should be of use for many:

I have a table with Customers, each of whom are identified by a unique Customer ID.

For each customer I have a report build in Access which shows each customer's activity.

Every month I need to send each of our customers their own individual report.

I am presently forced to create each report one-by-one for each customer (totalling over 200).

What I would like to accomplish:

I would love to find a way to automate this process.
Code would loop through my customer table and print each customer's individual report to a PDF File.

I imagine this tasks could be accomplished fairly easily in vba. I have no idea how to do this however.

Any Help Would be greatly appreciated.

Regards,

flo 79

 

[MichaelRed]

Obviously I don't fully understand the overall situation. If you just need to be able to seperate the customers, use a Customer Id group and place a page break in the section header / footer or even just have the section break? If pageenation is also imvolved, you just need to re-set the page number in the section where the break is done, or the Customer ID Headedr.




MichaelRed

 

[Flo79]

Michael, I must have explained myself badly. In simple terms, I am trying to accomplish the following:

I need to create a series of Reports, one after the other. Each report needs to be outputed as a PDF file to a previously specified location.

My problem has nothing to do with sorting/grouping or anything thelike.

In essence, I have a table with customers that I need to send a report to. Each customer has a unique ID
The report itself draws from a parameter query the output of which depends on a single customer ID which I fid it.

I now need a way to write code that starts with the first record in my customer Table, then prints a report to PDF based on that customer's ID. This Process is then to be repeated automaticly for every subsequent customer in the table.
If there are 100 customers in my table, the end result should be 100 different PDF files, which were created from Access reports.

I hope this makes more sense...

Thanks again for taking the time to try answering my question.

Cheers!

 

[MichaelRed]

is your process intended to transmit the pdf's to the customers, or print the report(s) and snail mail the repost (sections)?

If you send the pdfs' eletronically, then yes, you will find it easier to print the seperate reports. If the report(section(s)) are snail mailed then you can -with appropiate sorting/grouping etc.- just generate the single report and "burst" (split) it according to the customer.

Assuming you are going the print to PDF file, you could set up hte report to accept parameters (I use OpenArgs for most of these situations) and have the data gathering done in the report based on the parameters. This also provides part of the mechanisim for the generation of the PDF file name (it would presumably include the customerID). Doing this, reduces the process of generating the various reports to opening the report in a loop with the openargs parameter(s) set to identify hte customer and any other pertinien information. The "loop" could be based on just a recordset which included the customer id and the other (if any) parameters.

e.g.

dim db as sometyoeof database
dim rs as sometypeof recoredset

set db = opendatabsethinnggy
set rs = db.openrecordset(tablenamewiththecustomers, ... )

while not rs.eof
[tab]docmd.openreport yourreportnamehere, openargs:= [tab][tab]whateveryouneedher
[tab]rs.movenext
wend



MichaelRed

 

[GingerR]

Hi. I've seen this topic discussed quite a bit around here. Have you tried searching on "pdf" yet?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Flo79]

Thank you Ginger!

Your tip about what to look for ultimately led me to the solution I was desperately seeking.

I simply had to dig deeper.

Ultimately I found the solution in a great little (and cheap) program called win2pdf. Unlike Adobe Distiller, which I was using before, win2pdf can easily be controlled using vba.

I run code that loops through my recordset, outputting each report to pdf as an individual file, the name and location of which I can dynamically control in vba. No popup windows or the like.

For those who want to know more, I am happy to share my code.

Thanks again to everyone contributing to this thread and overall great forum!

Cheers!

Flo79

 

[kiloslug]

I have a similar problem as I need to send out daily reports to a list of people. Could I look at your code?

 

[Flo79]

Here is some sample code.

I am using win2pdf software to create pdf files from access. This software is relatively cheap and can be controlled easily through VBA.
You need to have this software in order for the below code to work.

Code
--------------------------------------------------


Public Function CreateReports()

Dim db As DAO.Database
Dim StrSQL As String
Dim qd As DAO.QueryDef

Dim Sender, strWhere, File_Name_Summary, File_Path, File_Ext, Report_Template_Name, Report As String

File_Ext = ".pdf"
File_Path = "C:\Documents and Settings\Administrator\Desktop\Attachments"

Dim rs_Cover As DAO.Recordset
Dim Filter_Cover, File_Name_Cover As String

Set qd = db.CreateQueryDef("")

StrSQL = "SELECT [Partner_ID], [Document_Name], [Sub_Entity_ID] FROM [Email_Attachments_Cover]"
qd.SQL = StrSQL & " WHERE [Partner_ID] = [Param_1]"
qd.Parameters("Param_1").value = rst!Partner_ID

' rst!Partner_ID = Unique customer ID from another table to loop through

Set rs_Cover = qd.OpenRecordset()


If rs_Cover.RecordCount >= 1 Then

rs_Cover.MoveFirst

Do Until rs_Cover.EOF

File_Name_Cover = "" & File_Path & "\" & rs_Cover!Document_Name & "" & File_Ext & ""


SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFFileName", File_Name_Cover


Filter_Cover = "[Sub_Entity_ID]=Clng('" & rs_Cover!Sub_Entity_ID & "') AND [Partner_ID]=Clng('" & rs_Cover!Partner_ID & "')"

DoCmd.OpenReport "RPT_Cover", acNormal, , Filter_Cover

rs_Cover.MoveNext

Loop

rs_Cover.Close

End If

Set qd = Nothing


End Function

-------------------------------------------

If you are having trouble I am happy to help you with a more general code sample at a latter time.