Marylin555
New member
- Local time
- Today, 02:38
- Joined
- Jul 17, 2020
- Messages
- 4
Hello everyone,
I have spent days trying to research my issue with regards to a recordset that I am trying to separate into pdfs based on a provider id. I've modified the below code so many different ways but it always prints out every record for each provider - so I get a file for each provider but it contains every providers records in each file. Hope this makes sense. I know I must be missing something minute. Please help! Thank you so much in advance!
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Provider], [PID] " & _
"FROM [Invoice] WHERE [Provider] IS NOT NULL AND [Provider] <> '' AND " & _
"[PID] IS NOT NULL AND [PID] <> '' " & _
"ORDER BY [Provider] ASC, [PID] ASC;", dbOpenSnapshot)
Dim folderPath As String
folderPath = selectFolder()
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
strRptFilter = "rst![PID] = " & Chr(34) & rst![PID] & Chr(34)
Debug.Print strRptFilter
DoCmd.OpenReport "Invoice", acViewPreview, strRptFilter, acHidden
Debug.Print strRptFilter
DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, folderPath & "\Invoice " & ReplaceIllegalCharacters(rst![Provider], "_") & " " & ReplaceIllegalCharacters(rst![PID], "_") & ".pdf"
DoCmd.Close acReport, "Invoice", acSaveNo
DoEvents
rst.MoveNext
Loop
End If ' rst.RecordCount > 0
rst.Close
Set rst = Nothing
I have spent days trying to research my issue with regards to a recordset that I am trying to separate into pdfs based on a provider id. I've modified the below code so many different ways but it always prints out every record for each provider - so I get a file for each provider but it contains every providers records in each file. Hope this makes sense. I know I must be missing something minute. Please help! Thank you so much in advance!
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Provider], [PID] " & _
"FROM [Invoice] WHERE [Provider] IS NOT NULL AND [Provider] <> '' AND " & _
"[PID] IS NOT NULL AND [PID] <> '' " & _
"ORDER BY [Provider] ASC, [PID] ASC;", dbOpenSnapshot)
Dim folderPath As String
folderPath = selectFolder()
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
strRptFilter = "rst![PID] = " & Chr(34) & rst![PID] & Chr(34)
Debug.Print strRptFilter
DoCmd.OpenReport "Invoice", acViewPreview, strRptFilter, acHidden
Debug.Print strRptFilter
DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, folderPath & "\Invoice " & ReplaceIllegalCharacters(rst![Provider], "_") & " " & ReplaceIllegalCharacters(rst![PID], "_") & ".pdf"
DoCmd.Close acReport, "Invoice", acSaveNo
DoEvents
rst.MoveNext
Loop
End If ' rst.RecordCount > 0
rst.Close
Set rst = Nothing