Originally Posted by
vianda
I have a report consisting of providers with their # of cases. The number of providers varies as the # of cases. I need to separate them by provider to a pdf file.
At this moment the code I have it is exporting 98 providers and creating 98 pdf files with providers names. But each file contains all the providers. I have 90 pdf files with 98 providers in each pdf file.
Something obviously is not working.
This goes in button to export file to PDF
Code:
Private Sub Export_to_PDF_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Provider'sName] FROM [TotalPayrollProviders] ORDER BY [Provider'sName];", dbOpenSnapshot)
Do While Not rst.EOF
strRptFilter = "[Provider'sName] = " & Chr(34) & rst![Provider'sName] & Chr(34)
DoCmd.OutputTo acOutputReport, "TotalPayrollProviderStatementsPDF", acFormatPDF, "F:\PAYROLL\PDF" & "\" & rst![Provider'sName] & ".pdf"
DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub
Also in Close and Open events of Report to be exported
Code:
Private Sub Report_Close()
strRptFilter = vbNullString
End Sub
Private Sub Report_Open(Cancel As Integer)
If Len(strRptFilter) <> 0 Then
Me.Filter = strRptFilter
Me.FilterOn = True
End If
End Sub
And a Public Variable
Code:
Public strRptFilter As String
My report also has a break on Provider'sname footer (yes) and on Page header (no)
I would appreciate if anybody look the code I have and give me some guidance. VBA Code is not my strength.
Thanks
You're m close.
The DoCmd.OutputTo does not allow a filter to be applied.
The trick is to open the report in preview mode first to apply the WHERE parameter to filter the records. Only need two additioanl lines lines of code.
You will not need a global variable or code in the reporet's On Open Event.
If yu already havce a report aht will print for multilpe providers to paper now, you shoudl be able to use that oe. You should not need a special version of the report jsut to save to PDF. I have not had to so far.
I did add some other code that I normally use.
Code:
Private Sub Export_to_PDF_Click()
Dim rst As DAO.Recordset
Public strRptFilter As String ' not need to use a public variable
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Provider'sName] FROM [TotalPayrollProviders] ORDER BY [Provider'sName];", dbOpenSnapshot)
' make sure that we have data
If rst.RecordCount > 0 then
rst.MoveFirst
Do While Not rst.EOF
strRptFilter = "[Provider'sName] = " & Chr(34) & rst![Provider'sName] & Chr(34)
' open the report hidden in preview mode setting the where parameter
DoCmd.OpenReport "TotalPayrollProviderStatementsPDF",acViewPreview,,strRptFilter,acHidden
' save the opened report
DoCmd.OutputTo acOutputReport, "TotalPayrollProviderStatementsPDF", acFormatPDF, "F:\PAYROLL\PDF" & "\" & rst![Provider'sName] & ".pdf"
' close the report
DoCmd.Close acReport, ""TotalPayrollProviderStatementsPDF"
DoEvents
rst.MoveNext
Loop
End if ' rst.RecordCount > 0
rst.Close
Set rst = Nothing
End Sub
It it were mine, I would use the Profivder's Priamry key to select the reocrds not the name.
Something like
Code:
"SELECT DISTINCT [ProviderUniqueID], [Provider'sName] FROM [TotalPayrollProviders] ORDER BY [Provider'sName];"
Code:
strRptFilter = "[ProviderUniqueID] = " & rst![ProviderUniqueID]