Report snapshot for eac record

[Jusenkyo]

Hello all

I am emailing a report to someone in snapshot format, which shows all of the records in the underlying query.

What I want to do is email a report for each record in the query, as opposed to sending them all in one query.

If that makes sense!

Anyone?
Cheers
J

 

[OnTheFly]

If I am understanding you correctly you are trying to attach several reports to an email as Snapshots having each report only show a particular record.

If this is true, I do know a way to do this. You would create the Snapshot reports first, saving to a particular folder with unique names and then add them to the email as attachments.



Hope this helps.

OnTheFly

 

[Jusenkyo]

Not quite sure what your talking about there mate!

Whenever a snapshot report is created, it will automatically put into it however many records the underlying query has. So if there are three records on the report, the snapshot will have three pages.

I want to split these three pages into three snapshots, which im assuming is something to do with only showing one record in the underlying query (Its an ADP, so I can use 'Top 1' to show only one record).

Make sense?

Cheers

 

[OnTheFly]

The only way I know how to accomplish this is to filter the report and save it that way, then save it to a snapshot, then attach it to an email. I know it is a bit of a work around but there is no way that I know of to seperate several records into single snap shot reports.

Maybe an example would help clarify what I am trying to explain.

Say you have a query that returns Student Grades. Fields returned are Student Name, Student ID and Grade. Now you want to create a report for each student. If you were simply printing these you could use a loop to print the report one student at a time with

DoCmd.OpenReport "StudentGrade",adViewNormal,,"StudentID=3"

However, when Outputting to a Snapshot you can't do it this way. You would have to open the report in design view change the Record Source to be something like "SELECT * FROM Students WHERE StudentID=3". Then save the report and then output it to a snapshot.

It sounds complicated but the code is not too bad. Also, if you want to email all these seperate reports on a single email, you could incorperate it all into the same loop.

Let me know if this is what you might want to do and I will give you a sample of code to do it.



Hope this helps.

OnTheFly

 

[Jusenkyo]

Hmmm.

Im not sure that would be the most efficient way of doing things, as there could be a large number of reports that need to be sent, so i would imagine saving them and sending them would take a long long time!

My idea was, in VB, to grab the list of data that is going onto the report into a recordset, select the first one, mail it in snapshot format, loop to the next record, and so on...

Would that be a better idea?

 

[OnTheFly]

I don't think speed is going to be the deciding factor here because either method should run OK. However, if you are talking a lot of records then attaching seperate reports to one email would probably not be the best idea because the size the email could become big. But then again, does the person you are sending this to want to receive 50 emails all at once?

You could do a controlled emailing that sends say, 10 reports at a time until they are all sent.

Either way you will still have to modify the report, save it with the filtered recordset and send it in a email.

Hope this helps.

OnTheFly