Applying Filter in code

I have a report that contains data for multiple Project Managers. The underlying datasource is a complicated query that takes around 30 seconds to run. I would like to cycle through the Project Managers and email the report to individual Project Managers.

Presently, I cycle through the individual Project Managers and create a table that includes records only for the current Project Manager and have the report use this "temporary" table as the underlying datasource. This approach is taking a long time and I think there may be a more efficient method.

Would it be better (more efficient) to have the report use the original query and have the report filter on the individual Project Managers. That is, instead of changing the underlying datasource, cycle through the Project Manager list and update the report filter to show only the current Project Manager.

If the second approach is recommended, do you have sample code that opens a report, changes the filter, then saves the report?

Thanks in advance,
Rob

You can set the filter on report_open based on whatever criteria you choose.
Are you using a form to do the mailing?

Anyway, you could have a loop as such to do the emailing of reports (this is air code by the way, so if I get things a little wrong, sorry) - have this in a database module:

Option Compare Database
Option Explicit
Public ManagerID as Integer

Public Sub SendReports()

Dim dbs As Database, rstManagers As Recordset,

Set dbs = CurrentDb
Set rstManagers = dbs.OpenRecordset("tblManagers", dbOpenDynaset)

rstManagers.MoveFirst

Do until EOF
ManagerID = rstManagers("MgrID")
DoCmd.SendObject acSendReport, "rptManagers", acFormatRTF, rstManagers("MgrEmail"), , , , _
"Subject Text", "Message Text", False
rstManagers.MoveNext
Loop

Set dbs = Nothing
Set rstManagers = Nothing

End Sub

Now all this would do would send the report as is to every Manager in your table. But if you add code as follows in your Report_Open code (on your report), it will filter it for the manager when sending.

Private Sub Report_Open(Cancel As Integer)

Me.Filter = "[MgrID] = " & ManagerID
Me.FilterOn = True

End Sub

I would suggest that you use a form to action the emailing by a user - and I would also say that you should put in a handler on report_nodata for those managers that might not have any data.

I hope this has helped give you an idea.

Basil

----- Rob-LMS wrote: -----

I have a report that contains data for multiple Project Managers. The underlying datasource is a complicated query that takes around 30 seconds to run. I would like to cycle through the Project Managers and email the report to individual Project Managers.

Presently, I cycle through the individual Project Managers and create a table that includes records only for the current Project Manager and have the report use this "temporary" table as the underlying datasource. This approach is taking a long time and I think there may be a more efficient method.

Would it be better (more efficient) to have the report use the original query and have the report filter on the individual Project Managers. That is, instead of changing the underlying datasource, cycle through the Project Manager list and update the report filter to show only the current Project Manager.

If the second approach is recommended, do you have sample code that opens a report, changes the filter, then saves the report?

Thanks in advance,
Rob

Thanks for the response. Your example is just like I have implemented. However, when applying the filter, where does the report get the value ManagerID? I believe I need to
1) Open report
2) change filter
3) save report
4) mail report

Do you agree?

If you have a look at the code I wrote, the emailing bit which is held in a seperate module has this at the top:

Option Compare Database
Option Explicit
Public ManagerID as Integer

Now what this means is that the ManagerID is a public variable that can be referenced from all forms/reports/modules.

In the first part of the loop statement we are populating this variable with the ID of the current manager who we are wmailing - as so:

Do until EOF
ManagerID = rstManagers("MgrID")
DoCmd.SendObject ...

To send the report it has to open it - and it is here (on Report_Open event) that the report is filtered according to the ManagerID variable that we just set:

Me.Filter = "[MgrID] = " & ManagerID
Me.FilterOn = True

[MgrID] I have used to represent the name of the ID field in the report's recordset.

Try it and see.

If it doesn't work, tell me what the field names are etc

Basil


----- Rob-LMS wrote: -----

Thanks for the response. Your example is just like I have implemented. However, when applying the filter, where does the report get the value ManagerID? I believe I need to
1) Open report
2) change filter
3) save report
4) mail report

Do you agree?

Thanks Basil. It worked just fine.