Print report cycling through List Box

[steveg242]

Hello,

Here is what I have:
I have a form with a list box, which lists all those registered for a student orientation. I then have a button to an unbound report which prints a registration worksheet. This report looks up the student's name, test scores, suggested courses based on those scores, and various other information from multiple tables, assigns them to variables, then to controls on the report. This is done in the On Open event of the report.

But it was designed to work for record highlighted. Which was fine until now. Now I'd like to take it one step further and have it go through the list from beginning to end, printing the report for each record in the list.

Maybe I am missing something simple, but I can't seem to pull it off. The closest I can get is getting it to print the first record n times.

Any ideas? Any other info I should include?

 

[GinaWhipp]

Hmm, this should work...
http://www.access-diva.com/vba3.html

Will need some adjusting to work with List Box.

 

[steveg242]

Thanks, I will certainly give this a try and report back next week.

 

[vbaInet]

Since you mentioned a listbox (and I'm guessing you're talking about a multi select listbox) you need to look into the ItemsSelected property of a listbox to be able to get those rows that are highlighted.

 

[steveg242]

Ah, no. The list box is designed to highlight one record at a time. But what I'm looking to do is print a report for every record in the list, highlighted or not.

 

[GinaWhipp]

The code I provided should get you started to loop thru the List Box as you are just looking for the *list* just just the highlighted ones.

 

[vbaInet]

The title is a bit misleading.

 

[steveg242]

Passing a value from a form to a report

Ok, sorry it has taken so long, but after a busy season I am finally able to get back to this. And after all effort, I decided to abandon the idea and start from scratch. So instead of dealing with a listbox which displays every record, I am going from a different listbox which just has a list of dates.

So it's much simpler. I am just creating a temp table based on the selected date, then opening a recordset of that table and looping through each record. All I really need in the report is the ID number. All the other info needed for the report can be pulled from that. But now my question is how do I pass the ID number from the form to the report?

What I have is:

    rst.MoveFirst

    Do Until rst.EOF
        varID = rst!ID
        DoCmd.OpenReport "MyReport", acViewPreview
        rst.MoveNext
    Loop

(Once I'm satisfied it works, I'll get rid of the acViewPreview so it prints directly.) I tried making varID a public variable. I tried using the OpenArgs parameter, my first attempt at ever doing so. No matter what I did, on the report end of things, the variable was read as null.

My only thought is to put an invisible text box in the form and use that. Seems kind of "brute force". Any better way to do this?

 

[GinaWhipp]

Hmm, maybe something like (untested)....

rst.MoveFirst
 
Do Until rst.EOF
varID = rst!ID
DoCmd.OpenReport "MyReport", acViewPreview
DoCmd.OutputTo acOutputReport, "", acFormatPDF, YourPath & varID & MyReport
rst.MoveNext
Loop

 

[vbaInet]

Re: Passing a value from a form to a report

I am just creating a temp table based on the selected date, then opening a recordset of that table and looping through each record.

Why do you need a temp table?

Although GinaWhipp's method will do the same thing, what I describe in the following link (an excerpt from a post) avoids the need to open and close the report for each cycle. There are definitely time and bandwidth savings.

http://www.access-programmers.co.uk/forums/showpost.php?p=1049034&postcount=10

 

[steveg242]

Perfect! Thanks a lot guys!