Output one report for each record while changing recordsource

  • Thread starter Thread starter Lowell Moorcroft
  • Start date Start date
L

Lowell Moorcroft

Hello, all -

I'm having some problem conceiving how to execute a task.

I have an invoice report which needs to go to four
different types of customer, hence will take four
different recordsources. I'm planning to set the report to
different recordsources depending on which of four buttons
on a form is pressed. Each report then needs to be
exported, since these are invoices that need to be
preserved and not updated. I'm planning on using the
OutputTo method on the form where the report is selected,
along with acFormatRTF as the output format.

But two of the four reports need to be split up into one
report per record in the recordsource, since each invoice
record needs to be attached eventually to an e-mail
message, one message to each billee.

So how do I assign the recordsource to the report, but
cycle through the records so that OutputTo assigns each
record to a file?

Thinking about this so far has confused me as to how the
code should be distributed between the report and the
calling form. I find myself trying to confine the code to
just the form, since it seems the OutputTo has to be run
outside the report (or does it?).

I'm inclined to swap the recordsources for the invoice
report to avoid making four identical reports, each with
its own recordsource. This is in case the report needs to
be revised which therefore could be done in one report,
not four. In a pinch, though, I would make four reports.

But combining 1) assignment of the recordsource; 2) record
looping; and 3) the output to file, is what is daunting me.

The air code in the form would look like this, under my
current conception:

strSQL = SELECT, etc....

report.recordsource = strSQL

For each record in report.recordsource.open(strSQL)

DoCmd.OutputTo, etc.....

Next record

Does this look like it would work? Would something else
work better?

Thanks

Lowell Moorcroft
 
I think we've seen this question before, and I don't follow it any better
now than then to understand what you really need to do. Sounds, though, like
you're trying to do in one report what probably is multiple steps or items.

Please clarify (1) what you have in the way of stored data, and (2) what you
really need to accomplish, not a particular way that you have envisioned
implementing it. Please clarify here in the newsgroup, not by e-mail.

Larry Linson
Microsoft Access MVP
 
If my question was posted before, I didn't post it - in
fact, I haven't visited Access newsgroups for many months.
This problem is new for me, and I can't find it directly
treated in the Knowledgebase for Access. Sorry if it
repeats someone else's version.

I'll restate my request:

I need to export an Access report to .rtf files. Each
record in the report's recordset needs its own rtf file,
using a filename reflecting the contents of a field in the
record. I'm running the report by pressing a button on a
form.

Thank you for any help.

Lowell Moorcroft
 
I do not know of any way to export a report to multiple .RTF files. The way
I would approach that problem, I suspect, is to look at how I can generate
separate reports for each record or at some alternative that doesn't require
..RTF files, so I could generate them other than with a report. I suppose, if
you dug into the .RTF format, you could generate those files from VBA code,
but I've not done any .RTF formatting except by saving reports that way.

Sorry that I can't be more helpful.

Larry Linson
Microsoft Access MVP
 
Hi!

I have just been dealing with the same challenge myself.
The way I solved it was this:

1: Create a form that displays the recipients of the
report, (no other records must be shown).

2: Install a button on the form with the following code:
Private Sub cmdSendEmail_Click()
Dim strReportName As String
Dim strCriteria As String
Do
'this routine opens the report, showing only the
record that is active on the form
strReportName = "TGBonusrapport"
strCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenReport strReportName, acViewPreview, ,
strCriteria
'This routine finds the next record to send
DoCmd.GoToRecord
Loop

End Sub

2: Create the report and write the following code for
OnActivation:

Private Sub Report_Activate()
DoCmd.SendObject acSendReport, , acFormatRTF, [Email
Address], , , "<title of the email> " & [Date], "<text in
the mail>", 0
DoCmd.Close


End Sub

When the report is automatically closed, the routine
finds the next record and sends it.
You may have to relate to a warning from Outlook, forcing
you to click Yes for each outgoing mail. I am working on
this right now, but have not found a way around that yet.

Regards
Nils M Rugsveen
www.trendgames.net
 
Back
Top