Report filter not working with recordset (1 Viewer)

Marylin555

New member
Local time
Today, 09:09
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

It might be easier if you could post a sample copy of your db.
 

June7

AWF VIP
Local time
Today, 05:09
Joined
Mar 9, 2014
Messages
5,423
strRptFilter is in wrong argument. Need another comma.

DoCmd.OpenReport "Invoice", acViewPreview, , strRptFilter, acHidden

If PID is a number field, don't need Chr(34) nor need to compare to empty string in SQL criteria.
 

Marylin555

New member
Local time
Today, 09:09
Joined
Jul 17, 2020
Messages
4
strRptFilter is in wrong argument. Need another comma.

DoCmd.OpenReport "Invoice", acViewPreview, , strRptFilter, acHidden

If PID is a number field, don't need Chr(34) nor need to compare to empty string in SQL criteria.

It is now prompting for a parameter value that the user would have to enter. ????
 

June7

AWF VIP
Local time
Today, 05:09
Joined
Mar 9, 2014
Messages
5,423
rst is not part of field name in table:

strRptFilter = "[PID] = " & Chr(34) & rst![PID] & Chr(34)
 

Marylin555

New member
Local time
Today, 09:09
Joined
Jul 17, 2020
Messages
4
rst is not part of field name in table:

strRptFilter = "[PID] = " & Chr(34) & rst![PID] & Chr(34)
rst is not part of field name in table:

strRptFilter = "[PID] = " & Chr(34) & rst![PID] & Chr(34)


THANK YOU, THANK YOU!!!! You do not even know how much I have struggled with this particular code! It worked perfectly!!!!!!!!! Thanks again!
 

Users who are viewing this thread

Top Bottom