MS Access VBA loop error, Run-Time Error 3071-CodePudding

I have an MS Access database that creates physician report information. The final report has many pages consisting of all the physicians in my query.

My goal is to take this large report and move physician specific information into a separate folder as a pdf file. The large final report is master linked based on the physicians EPIC_ID. I also have a separate table that has distinct EPIC_ID with physician names (tblPhysicianID_Range). I want to go down the list of physicians from this table, and pull out specific information for each physician, create a pdf file of that information and send it to a folder with that physicians name.

At one point I had this code working just fine, but I had to make some adjustments to the large final report. None of the adjustments affected what links everything together, the EPIC_ID.

Exact error I'm getting: Run-time error '3071': This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try Simplifying the expression by assigning parts of the expression to variables.

And this is the highlighted debug: DoCmd.OpenReport "rptCombined", acViewPreview, , _ strRptFilter, acHidden ' open the report hidden in preview mode setting the where parameter

Here is the entire code:

Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim strFolder1 As String, strFolder2

DoCmd.OpenQuery "qryPhysicianID_Range_tbl"

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Prov_Order_Name],[EPIC_ID] FROM  [tblPhysicianID_Range] ORDER BY [EPIC_ID];", dbOpenSnapshot)

If rst.RecordCount > 0 Then ' make sure that we have data

    rst.MoveFirst

Do While Not rst.EOF
    strRptFilter = "[EPIC_ID] = " & Chr(34) & rst![EPIC_ID] & Chr(34)
   
    strFolder1 = "U:\Co\Physician\Reappointment\Jordans Test\" 'common folder for files to go
    
    strFolder2 = strFolder1 & rst.Fields("[Prov_Order_Name]") & "\" 'creates folder by Provider Name
    If Dir(strFolder2, vbDirectory) = "" Then MkDir strFolder2 'determines if folder exists or not, and if it doesn't it makes one
    
    DoEvents
    rst.MoveNext
Loop

End If

If rst.RecordCount > 0 Then ' make sure that we have data

    rst.MoveFirst

Do While Not rst.EOF
    
    strRptFilter = "[EPIC_ID] = " & Chr(34) & rst![EPIC_ID] & Chr(34)
    
    strFolder1 = "U:\Co\Physician\Reappointment\Jordans Test\" 'common folder for files to go
    
    DoCmd.OpenReport "rptCombined", acViewPreview, , _
    strRptFilter, acHidden  ' open the report hidden in preview mode setting the where parameter

    DoCmd.OutputTo acOutputReport, "rptCombined", acFormatPDF, _
    strFolder1 & rst.Fields("[Prov_Order_Name]") & "\" & rst.Fields("[Prov_Order_Name]") & ".pdf" ' save the opened report

    DoCmd.Close acReport, "rptCombined" ' close the report
    
    DoEvents
    rst.MoveNext
Loop

End If

rst.Close
Set rst = Nothing
End Sub

CodePudding user response：

"And this is the highlighted debug: DoCmd.OpenReport "rptCombined", acViewPreview, , _ strRptFilter, acHidden ' open the report hidden in preview mode setting the where parameter"

This is pointing to the report, if the report is based on a select query, check the syntax of the query and ensure the query runs well by returning values.

Second thing you have to look at (this is based on the error message description in your post), the report filter has to be looked into.

if you share the images of the query design view and query SQL view, more clarity will come then, that can aid to a solution.

CodePudding user response：

I was able to figure out the main problem why this wasn't working.

It turns out my "where" statement in the OpenReport section;

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

Wasn't working because my subreports weren't linked properly to the correct table [EPIC_ID] that was being used, and in addition, this was initially set up to find physician names, not ID's which are numbers/integer, so I switched the "where" to;

strRptFilter = "[EPIC_ID] = " & rst![EPIC_ID]

and it worked!

I now have a problem though, with having the subreports show their table in the final pdf even when there is no data for that physician, but I will post that as a separate question. Thanks!