Running the same report many times on different records 1

[Tamrak]

Good Morning,

I would like to see whether this is doable. I have created an access report. Let's say, I have 20 records in the table. My boss asks me to print one record at a time. I can do a parameter query and ask the record number to be printed. That is one of the option.

My question is, is there anyway I can do some VB codes to print the reports 20 times from a different records, from the first to the last record?

For example, when the code runs, the report will do a print preview on the first record. Then, another print preview of the second record will pop-up on a different window. Then, the third, the fourth and so on..

If it is possible, please kindly provide me with some codes. I will be very much appreciated.

Let say -> Report name is "rptCustomer". The Table name is "tblCustomer" and the name of the database is Customer.mdb, which is resided in C:\Database\Customers.

Thank you for your assistant.

 

[patyavila]

Tamrak, first, let me ask you: do you need to print a hardcopy of your report for each (or selected) records in a table or you want to view an on screen report?

 

[Tamrak]

Hi Patyavla,

The report can be print-previewed on the screen because the boss wants the output to be in PDF file. Access has a snapshot, not a PDF.

What I am looking is to do a print preview first. Then, shoot each individual record into PDF.

Like I previously stated, parameter query will work, one record at a time. However, I would like to know whether there is a way to print-preview 20 separate records from the same report? I know that it is weird request. I am stumpped and could not think of the solution at this moment. The records will be print-previewed from top to bottom, first to last.

Thanks again for your reply.

 

[lameid]

No you can't print preview 20 copies of the same report. If you open a report that is already opened, Access simply activates the open version. However, I believe there are several FAQ's about printing to PDF. You can however, tell access to print the report 20 times by using a recordset, stepping through each record and printing the report for each record.

 

[Tamrak]

Lameid,

Can you provide an example of using a recordset? I do appreciate it. Thanks.

 

[lameid]

Dim cnn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Set cnn = CurrentProject().Connection
RS.Open "TableOrQueryName", cnn, adOpenDynamic, adLockOptimistic

While Not RS.EOF
DoCmd.OpenReport "ReportName", acPreview,,"Field = " & RS.Field
RS.Movenext
Wend


You'll have to modify the open report line if Field is not a number but is text or a date.

 

[ck1999]

lamied where would you put this code?

In a module?

ck1999

 

[lameid]

Definitely in a module... If you want it to run on a button click you could just put it on the Click event of the button... What is missing from this code is a procedure declaration...

Sub ProcedureName

All code in beteween these two lines

End Sub

If you had a button named cmdRunReport when you go to properties you can find the click event and click the build (button with three dots). Select Code Builder and paste the code from my post and modify it as necessary.

When you build code that way for a control event it automatically creates the sub and end sub lines... For a button named cmdRunReport it would be...

Sub cmdRunReport_Click

End sub

 

[Tranquilo]

Actually, I've found the easiest way to do the same report for a range of objects (in this case people) is to just do a main report, then group by the identifier for each individual (say social security number or whatever field you're using for a key), then set up a subreport (this will be the report you're already using) in the 'group by' portion of your main report and link it to the main report by the same identifier.

A little dumb I know, but it's better than spending alot of time trying to code it into a report. I do the same thing printing up applications for store licenses. We download applications, then import them into access as a background image. Then when doing a bunch of stores in the same area (say all the stores in texas), I can just pull up the one report and we'll have the application filled out for all of the stores in the state of texas. You'll have to fiddle with the margins and headers and stuff, but I've found it to be the easiest solution.

 

[lameid]

Tranquilo,

First, your solution does not work for this situation as he needs individual print runs to get separate PDF files.

Second, you don't need sub-reports as you can add sorting and grouping to the report and force new pages as needed.

 

[ck1999]

I am getting an error "user defined type not definied" I am using Access 97. DO i need a reference


Chris

 

[lameid]

Thats ADO code which is supported in Access 2000 forward. In Access 97 you need DAO code...

Dim db As DAO.database
Dim Rs As DAO.Recordset
Set db = currentdb()
Set RS = DB.Openrecordset ("TableOrQueryName")

While Not RS.EOF
DoCmd.OpenReport "ReportName", acPreview,,"Field = " & RS.Field
RS.Movenext
Wend

 

[ck1999]

I have the code so it is compiling now. Although it is only opening the report for the first record. How do I get the other records' report to open.

Chris

 

[lameid]

OOPS... YOu'd have to close the report... in the loop. Realistically for the original poster you'd want to change the Openreport to print or run a specific function to export it. Remember this does not keep the reports open as separate instances, it closes them.

Dim db As DAO.database
Dim Rs As DAO.Recordset
Set db = currentdb()
Set RS = DB.Openrecordset ("TableOrQueryName")

While Not RS.EOF
DoCmd.OpenReport "ReportName", acPreview,,"Field = " & RS.Field
docmd.Close acReport, "ReportName",acSaveNo
RS.Movenext
Wend