Automate saving filtered report as PDF in Access 2007

  • Thread starter Thread starter Amp
  • Start date Start date
A

Amp

Is there any way to take advantage of the new "PDF or XPS" add-in in Access
2007 using VBA for a report that has a filter?

I have attempted using OutputTo and SendObject, but there is no filter
parameter and I can't figure out another way to filter the report.

I can filter the report using OpenReport but I am prompted to Save PDF As
and I can't figure out how to code the name of the file and its destination.
I email 50+ reports and I would hate to have to respond to that prompt 50+
times.


Any suggestions Gurus?
 
Amp said:
Is there any way to take advantage of the new "PDF or XPS" add-in in
Access
2007 using VBA for a report that has a filter?

I have attempted using OutputTo and SendObject, but there is no filter
parameter and I can't figure out another way to filter the report.

I can filter the report using OpenReport but I am prompted to Save PDF As
and I can't figure out how to code the name of the file and its
destination.
I email 50+ reports and I would hate to have to respond to that prompt 50+
times.


Any suggestions Gurus?
Click to expand...

Why not filter the report via the report's underlying query? Or, if the SQL
is simple enough, put that in the report's RecordSource and, when you want
to filter, add a where clause.
 
To the best of my knowledge that wouldn't be as efficient for what I want to
do. What I am doing is connecting to a table and using it as my recordset,
then I am looping through the table and grabbing data from each record which
is used to populate the record. I have a field call "ordernumber" that is
unique to each records that I am using to filter the report. Also, the
record has an email address field and I send the report to the address in
that field. The issue is I keep getting prompted for the filename and Save
As.
 
Comments inline:

Amp said:
To the best of my knowledge that wouldn't be as efficient for what I want
to
do. What I am doing is connecting to a table and using it as my
recordset,
then I am looping through the table and grabbing data from each record
which
is used to populate the record. I have a field call "ordernumber" that is
unique to each records that I am using to filter the report. Also, the
record has an email address field and I send the report to the address in
that field. The issue is I keep getting prompted for the filename and
Save
As.
Click to expand...

In your 1st post you hinted that OutputTo and SendObject didn't prompt you.
Is that the case?

That's what I posted, namely 2 suggestions for filtering the report prior to
it being loaded, so that you could then use OutputTo or SendObject.
 
***(Now that I reread it I feel like a didn't give a detailed enough of a
description)
I am using a table called "Unproccessed" as the record source for the report
and as it loops I using the order number as the where condition to populate
the desired report

strWhere = "[Order Number]='" & prmFilter & "'"
DoCmd.OpenReport prmRptName, , , strWhere, acHidden

I am using the outlook object model to create an email and attach the report
out as a PDF. I was using Leban's Report to PDF method to convert the report
to PDF and it works fine in a database that is not in a MS Access 2007 file
format

To answer your question yes, that is the case.

The SendObject didn't prompt for the PDF Save As, but it did give me the a
program is trying to send a email on your behalf warning (I forgot to add
that to the orginial post). In the environment I am using this code in I
can't modify Programmic Access or Macro Security in MS Outlook 2007 trust
center settings. Also it was sending the entire report and I couldn't figure
out how to filter it.

The OutputTo worked as far as no prompt to save the PDF as fine but is has
no filter or where condition.......are you suggesting I filter the report
prior then use the attachment.add function to the email?

What would be the best way to filter the report?
 
Amp said:
***(Now that I reread it I feel like a didn't give a detailed enough of a
description)
I am using a table called "Unproccessed" as the record source for the
report
and as it loops I using the order number as the where condition to
populate
the desired report

strWhere = "[Order Number]='" & prmFilter & "'"
DoCmd.OpenReport prmRptName, , , strWhere, acHidden

I am using the outlook object model to create an email and attach the
report
out as a PDF. I was using Leban's Report to PDF method to convert the
report
to PDF and it works fine in a database that is not in a MS Access 2007
file
format

To answer your question yes, that is the case.

The SendObject didn't prompt for the PDF Save As, but it did give me the a
program is trying to send a email on your behalf warning (I forgot to add
that to the orginial post). In the environment I am using this code in I
can't modify Programmic Access or Macro Security in MS Outlook 2007 trust
center settings. Also it was sending the entire report and I couldn't
figure
out how to filter it.

The OutputTo worked as far as no prompt to save the PDF as fine but is has
no filter or where condition.......are you suggesting I filter the report
prior then use the attachment.add function to the email?

What would be the best way to filter the report?
Click to expand...

Yes that's what I'm suggesting. Well there are a number of ways to do this,
so I'll pick the one that looks easiest to implement and we'll run with that
for starters. First of all, base the report on a query (SELECT * is fine),
not the table. Let's name it qryUnproccessed. When you want to filter the
report, rewrite the query's SQL string to add a where clause:

With CurrentDb.QueryDefs("qryUnproccessed")
.SQL = "SELECT Unproccessed.* From Unproccessed WHERE [Order Number]='"
& prmFilter & "'"
End With

(watch for word wrap)

Then use OutputTo as normal. One thing to remember is that the SQL is
permanently changed, so if you ever want an unfiltered report, you need to
pre-process it as above, but leaving out the where clause.

Hope that gets you off the ground.
 
Back
Top