[AccessD] Ac2013 running out of resources

[Bill Benson]

If I don't do it all in VBA, then the user will be stuck sending each
report individually, no?

Why do you open the report first, what is that accomplishing? I don't see
any pause occurring in your code, but what happens when you are opening the
report in preview view?

I don't understand what you are saying regarding changing the WHERE clause
to a string, is that a way of limiting what the report shows? Isn't that
what I am accomplishing by editing the query sql?

Would I be better off changing the recordsource for the report, by creating
a new query each time rather than the same query?

I will try opening the report as you do, then printing it, and then closing
it - so long as this doesn't interrupt the process.

On Thu, Jun 4, 2015 at 5:41 PM, David McAfee <davidmcafee at gmail.com> wrote:

> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>