Automate printing same report with 8 minor changes 1

[Conner]

I have a report ("rptSchool&quot that prints exam data on each student population in that school. The report is controlled by a parameter query that selects the school. Usually, I need to print only one school report at a time, so I simply enter the name of the school in the parameter and generate the appropriate report ... however, I now need to print 8 school reports at once and distribute each to the respective school ... how do I automate this so I don't have to enter 8 school names in a query and go through the same process 8 different times? Although I've never worked with arrays, my gut feeling is that I'm at a point where I need to make friends with them...I'm at a kindergarden "reading readiness" level with VBA so please go slow...

 

[dhookom]

I would use a multi-select list box on a form. Check out faq703-3936 for code you can use to implement this.

Duane
MS Access MVP

 

[PaulBricker]

Conner, you don't have to warm up to arrays to do this but you do need to give a little more info. Are there always going to be the same 8 schools? Will you have different schools at different time? Is something in the report different for each school?
You could put a multi select list box, that contains all the school names, on a form with a button and then in the Click Event for the button put something like this.

Dim ctl as Control
Dim varItem as Variant
Set ctl = Me.ListBoxName
For Each varItem in ctl.ItemsSelected
DoCmd.OpenReport "ReportName", acViewNormal, ,"[SchoolName] = '" & ItemData(varItem) & "'"
DoCmd.Close acReport, "ReportName"
Next varItem

What this does is goes to the first school selected and opens the report based on the SchoolName (or whatever your school name field is) and prints it then closes it and goes to the next school selected.

If this is how you want to do it post back with any specific questions.

Paul

 

[dhookom]

I like Paul's solution for multiple copies of the report. My solution would create a single report of multiple schools.

Duane
MS Access MVP

 

[Conner]

Paul
I'm having trouble with one line of the above code; you have to go slow with me ...

DoCmd.OpenReport "ReportName",acviewNormal,, --I understand to this point, but here is where I have trouble:

"[SchoolName]='" & ItemData(varItem) & "'"

Am I working with a function named ItemData? I know that Item Data is an object property, but I keep getting "undeclared function" errors ...

Also, the answer to your above questions: The report is always the same with no changes except the name of the school; that's the only data field that will ever vary, and there are only 8 schools whose names do not change.

 

[PaulBricker]

Sorry, that line should read

"[SchoolName]='" & ctl.ItemData(varItem) & "'"

ItemData is a property of the Listbox and I forgot to add the object (ctl) to it.

Paul

 

[Conner]

Paul--
That did the trick! worked flawlessly. Thanks.