How many records in a filtered report? (A2007)

[Dale Fye]

I've got a shortcut menu that I'm using for all of my reports. It includes a
print option, a Send as PDF option, and a close option.

Because of the limitiation of the SendObject method to send a filtered
report, I've written some code to save the report as a temporary file,
automate Outlook, attach the file, display the message, and delete the temp
file. Generally, this will only occur with a single record in the report, so
I could get the [Title] value associated with that record by setting a
reference to the report, and then looking at the value of the control in the
report. However, when the report has multiple records, I cannot do this, so
I need to concatenate the list of [Title] values into the filename.

I've tried the following, but the recordset that the RecordSource property
returns is all the records in the table.

Set rs = CurrentDb.OpenRecordset(Screen.ActiveReport.RecordSource)
'rs.Filter = screen.ActiveReport.Filter
While Not rs.EOF
strFileName = strFileName & "-" & rs("Title")
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

I tried adding the Filter line, but that doesn't seem to do it either,
although it does give me the ID values associated with the filtered records.
I could use that, parse the string, and DLOOKUP() to get the [Title] field
associated with each record in the report, but that seems like an awful lot
of work.

Anybody have any ideas?

 

[Albert D. Kallal]

I've got a shortcut menu that I'm using for all of my reports. It
includes a
print option, a Send as PDF option, and a close option.

Because of the limitiation of the SendObject method to send a filtered
report,

I not aware of that limitation you speak of.

The simply solution here is to open the report first using a "where" clause
to restrict records, and THEN use the send object.

for example, to send the ONE record you viewing in a form, we go:

Dim strR As String
Me.Refresh
strR = "name of report to email"
DoCmd.OpenReport strR, acViewPreview, , "id = " & Me.ID, , "test"
DoCmd.SendObject acSendReport, strR, acFormatRTF, "(e-mail address removed)", , , ,
"test msg", True
DoCmd.Close acReport, strR

 

[david]

:~) I think your problem was the decision to use multiple titles
for the file name, but --

If you want to try to use the Filter property, you have to open
a new recordset:

rs.Filter = screen.ActiveReport.Filter
Set rsFiltered = rs.OpenRecordset

(david)

 

[Dale Fye]

Albert,

I've never tried that, nor seen anyone recommend it when this issue has been
raised before. That would be exactly what I need, without the hassle of
automating Outlook and saving the report as a temporary file.

I'll give that a try in the AM.

Thanks.

 

[Dale Fye]

Dave,

Actually, the problem is trying to use the same shortcut procedure for every
report. I have to make it general enough that it will account for all
occurances. While providing some sort of meaning to the file name. I guess
I could ask the user for the name they want to use for the file (that sure
beats the current Temp.pdf).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

:~) I think your problem was the decision to use multiple titles
for the file name, but --

If you want to try to use the Filter property, you have to open
a new recordset:

rs.Filter = screen.ActiveReport.Filter
Set rsFiltered = rs.OpenRecordset

(david)

I've got a shortcut menu that I'm using for all of my reports. It
includes a
print option, a Send as PDF option, and a close option.

Because of the limitiation of the SendObject method to send a filtered
report, I've written some code to save the report as a temporary file,
automate Outlook, attach the file, display the message, and delete the
temp
file. Generally, this will only occur with a single record in the report,
so
I could get the [Title] value associated with that record by setting a
reference to the report, and then looking at the value of the control in
the
report. However, when the report has multiple records, I cannot do this,
so
I need to concatenate the list of [Title] values into the filename.

I've tried the following, but the recordset that the RecordSource property
returns is all the records in the table.

Set rs = CurrentDb.OpenRecordset(Screen.ActiveReport.RecordSource)
'rs.Filter = screen.ActiveReport.Filter
While Not rs.EOF
strFileName = strFileName & "-" & rs("Title")
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

I tried adding the Filter line, but that doesn't seem to do it either,
although it does give me the ID values associated with the filtered
records.
I could use that, parse the string, and DLOOKUP() to get the [Title] field
associated with each record in the report, but that seems like an awful
lot
of work.

Anybody have any ideas?

--
Dale

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Albert,

That worked great (learn something new every day).

Now the issue is that the attached report (pdf) has the same name as the
report in Access. which is not very meaningful to the recipient. There is
no way to change that using SendObject, is there?

--
Dale

email address is invalid
Please reply to newsgroup only.

 

[Albert D. Kallal]

Albert,

That worked great (learn something new every day).

Now the issue is that the attached report (pdf) has the same name as the
report in Access. which is not very meaningful to the recipient. There
is
no way to change that using SendObject, is there?

Yes, you can give the name by simply setting the caption....

So, to make the doc name an invoice number, you could go:

Dim strR As String
Me.Refresh
strR = "name of report to email"
DoCmd.OpenReport strR, acViewPreview, , "id = " & Me.ID, , "test"
reports(strR).Caption = "Invoice ref " & me!InvoiceNum
DoCmd.SendObject acSendReport, strR, acFormatRTF,
"(e-mail address removed)", , , , "test msg", True
DoCmd.Close acReport, strR

So, it is the caption setting of the report that controls the name you
give....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)




invoice ref #

 

[Dale Fye]

Neat techniques. Thank, Albert.

--
Dale

email address is invalid
Please reply to newsgroup only.