[AccessD] Ac2013 running out of resources

[David McAfee]

Is there a reason that you have to do everything in VBA?
Can you have one or two queries and reports and decide which one to run as
you are looping through the recordset?

This is how I ended up doing mine (passing the where clause as the report
parameter):

Dim Path As String, DateString As String, FileName As String, ShowPdf As
Boolean
Dim intRowCount As Integer
reportName = "rptLossOfMediCalCoverage"
DateString = Format(Date, "YYMMDD")
Path = "E:\Eligibility\Reports\" & DateString & "\"

'Check if directory exists, if not, make it
If Dir(Path, vbDirectory) = "" Then MkDir Path

 'Open record set of director names
 Dim db As Database
 Dim rs As Recordset
 Set db = CurrentDb
 Set rs = db.OpenRecordset("select Director from dbo_vwDirectors")
 If rs.BOF And rs.EOF Then
       'This really should not happen
       MsgBox "No directors were returned from dbo_vwDirectors"
        rs.Close
        Set rs = Nothing
        Exit Sub
Else
      rs.MoveLast
      rs.MoveFirst
      If rs.RecordCount >= 1 Then  '1 or More records found.
         For intRowCount = 1 To (rs.RecordCount + 1)
         FileName = "rptLossOfMediCal_" & DateString & "_" &
Replace(rs![director], " ", "_") & ".pdf"
         DoCmd.OpenReport reportName, acViewPreview, , "Director = '" &
rs![director] & "'"   'Can you put your where clause in a string and use it
here?
         'Or depending on the situation, set report name to a different
report?
         ShowPdf = False
         'DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, (Path &
FileName)
         DoCmd.OutputTo acOutputReport, reportName, "PDFFormat(*.pdf)",
(Path & FileName), ShowPdf, "", 0

         rs.MoveNext
         intRowCount = intRowCount + 1
         DoCmd.Close acReport, reportName
         Next intRowCount

         Else 'Record Count = 0, This shouldnt happen, but trap just in case
           MsgBox "No records were returned from dbo_vwDirectors"
        End If
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End If