Not sure what the name of your query is. I suggest you do not use the Report's filter property for selecting the appropriate ALTIDFILE. Instead, edit the Query Object's SQL. You can do this on the fly and it should be better performing than editing the Report's SQL or the Report's Filter property. Understand that the code below needs to be used "as is". You must edit the name of the Query. Other than that, the code should work, fingers crossed. (No way for me to test it here) Also, the Query Object must be dedicated for this procedure because it will always have a WHERE clause.
Code:
Dim strFileName As String
Dim strWhere As String
Dim strPath As String
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
strFileName = ""
strWhere = ""
strPath = "C:\Antipsychotics Testing\PC\"
strSQL = "SELECT DOC_AP_MASTER.[Fac ID], DOC_AP_MASTER.[Pharm-Doc], DOC_AP_MASTER.FACILITY_NAME, DOC_AP_MASTER.[Res ID], " & _
"DOC_AP_MASTER.[Resident Name], DOC_AP_MASTER.[Antipsychotic Medfication], DOC_AP_MASTER.[SS/LS], " & _
"DOC_AP_MASTER.[T/AT], DOC_AP_MASTER.[Dementia Medication], DOC_AP_MASTER.[AP Low Dose with Dementia Med], " & _
"DOC_AP_MASTER.[Parkinsons Medication], DOC_AP_MASTER.[PRN Only], DOC_AP_MASTER.PHARMACY, DOC_AP_MASTER.DOCTOR, " & _
"DOC_AP_MASTER.PHYSICIAN_NAME, DOC_AP_MASTER.[Other Prescriber], DOC_AP_MASTER.ALTIDFILE " & _
"FROM DOC_AP_MASTER "
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [ALTIDFILE] FROM [DOC_AP_MASTER]", dbOpenSnapshot)
If rs.RecordCount < 1 Then
MsgBox "No records found to export.", vbInformation, "Unable to export!"
Set rs = Nothing
Set db = Nothing
Exit Sub
End If
Set qdf = db.QueryDefs("QueryName")
While Not rs.EOF
strFileName = rs![ALTIDFILE]
strWhere = "WHERE [ALTIDFILE] = '" & strFileName & "'"
qdf.SQL = strSQL & strWhere
DoCmd.OutputTo acOutputReport, "Physician Summary", acFormatPDF, strPath & strFileName & ".pdf"
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set db = Nothing
Set qdf = Nothing
Windows 7 64bit 
Access 2010 64bit
Reply With Quote
Originally Posted by Kimberly2015
