' For all entries, loop through, generate, store and email the report
Do While Not RS_1.EOF
' This routine is divided into two parts - 1) generate and store the report and 2) email the report
' We generate the report for all supervisors and store them in the designated folder.
'**********************************************************************************************************************
' Section 1 - generate and store reports
'**********************************************************************************************************************
' Obtain the supervisor's name
ImmedSup = RS_1!ImmedSup 'the Supervisor is being renamed here. It originally is SupervisorName from the value that is in the Supervisor field from the strSQL above in the code, which is a list of all the supervisor name
myFileName = myPath & "Accruals Report - " & ImmedSup & ".pdf" 'the SupervisorName is the value of the Supervisor field from the strSQL above in the code, which is a list of all the supervisors names
' Open the report with the filter and export it as PDF to the designated folder. Close the report.
If Not IsNull(ImmedSup) Then
'DoCmd.OpenReport "rptAccrualSummaryWithReportsTo", acViewReport
DoCmd.OpenReport "rptAccrualSummaryWithReportsTo", acViewReport, "", "ImmedSup=" & """" & ImmedSup & """", acNormal
'DoCmd.OpenReport "rptAccrualSummaryWithReportsTo", acViewReport, , "ImmedSup=" & """" & ImmedSup & """", acNormal
DoCmd.OutputTo acOutputReport, "rptAccrualSummaryWithReportsTo", "PDFFormat(*.pdf)", myFileName, False, "", 0, acExportQualityPrint
DoCmd.Close acReport, "rptAccrualSummaryWithReportsTo"
End If
Jim,
I actually prefer to change the SQL of the query that is the report's record source. I use a little DAO code as described on this Tek-Tips FAQ http://www.tek-tips.com/faqs.cfm?fid=7433
Duane
| www.tek-tips.com There are times when the easiest method of creating complex queries with multiple filters is to change the SQL property of a saved query. This works well when you ... |
Sent: Thursday, November 2, 2017 1:26 PM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Filter report with vba
Jim,
What happens if you get rid of the first
DoCmd.OpenReport "rptAccrualSummaryWithReportsTo", acViewReport
Regards,
Duane Hookom
MN
Sent: Thursday, November 2, 2017 1:14 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Filter report with vba
Hello all,
I am trying to filter a report in VBA. The user wants to limit the records being emailed out for an Accruals report. The user wants to select the employees in a table. Then the report is to be emailed to the supervisors. So I have the following code to open the report. The user will open the table and click a Yes/No check box and then I need the report to use the SelectEmployee field to be used to exclude employees with the check box selected. I have tried to add the filter on the docmd.openreport line but it never filters the report.
Thank You for any help.
Jim Wagner
ImmedSup = RS_1!ImmedSup 'the Supervisor is being renamed here. It originally is SupervisorName from the value that is in the Supervisor field from the strSQL above in the code, which is a list of all the supervisor name
myFileName = myPath & "Accruals Report - " & ImmedSup & ".pdf" 'the SupervisorName is the value of the Supervisor field from the strSQL above in the code, which is a list of all the supervisors names
' Open the report with the filter and export it as PDF to the designated folder. Close the report.
If Not IsNull(ImmedSup) Then
DoCmd.OpenReport "rptAccrualSummaryWithReportsTo", acViewReport
DoCmd.OpenReport "rptAccrualSummaryWithReportsTo", acViewReport, "", "ImmedSup=" & """" & ImmedSup & """", acNormal
DoCmd.OutputTo acOutputReport, "rptAccrualSummaryWithReportsTo", "PDFFormat(*.pdf)", myFileName, False, "", 0, acExportQualityPrint
DoCmd.Close acReport, "rptAccrualSummaryWithReportsTo"
End If
Posted by: Jim Wagner <luvmymelody@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
Tidak ada komentar:
Posting Komentar