If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Applying Filter in code
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 |
#3
|
|||
|
|||
Applying Filter in code
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? |
#4
|
|||
|
|||
Applying Filter in code
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? |
#5
|
|||
|
|||
Applying Filter in code
Thanks Basil. It worked just fine.
|
Thread Tools | |
Display Modes | |
|
|