If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
ACC2000: bloat after saving report via code
So I have a routine that makes a PDF for each customer in a loop. It
opens a report in design view, changes the recordset to process only 1 customer, save the report, and makes a PDF from the changed report. I use this code inside the loop: ----------begin code---------- DoCmd.OpenReport sReportname, acViewDesign ' Must come before "set rpt" Set rpt = Reports(sReportname) 'rpt.Filter = sWhere ' Access 2000 has a bug here, cannot use .Filter 'rpt.FilterOn = True rpt.RecordSource = sMySQL DoCmd.Close , , acSaveYes DoEvents ' Allow Access to save report. iRptCount = iRptCount + 1 sPDFName = curdir & sSlsno & sCustnum & ".pdf" ' PDF path and filename. Call StatusBar("Report " & iRptCount & " of " & cnt & ", Filename=" & sPDFName) ' DEBUGGING bSaveDialog = False bShowPDF = False blRet = ConvertReportToPDF(sReportname, vbNullString, sPDFName, bSaveDialog, bShowPDF, 0, "", "", 0, 0) ' Lebans method ----------end code---------- My loop bombs out about record 310. What I found out is, the Access MDB file slowly increases in size as I go through the loop (I watched it via Windows Explorer) until it reaches a point where I get an error "Cannot continue. OpenReport not available at this time." This error is from the Docmd above when I open the report in design mode to change it. If I compact the db via code then I can manually run my loop again, but it will run from the beginning, not from where the error was. Another problem is, I cannot select a range of records less than 300 in number because the customer number is a string, not numeric. (I did not design the db, a major national company did.) And if I compact the db every 300 records, it will stop my program, and till not restart it. Oh, and I verified that Access 2000 does have a bug whereby the .Filter property of the report object does not work reliably. So that is out of the question. Anyone have any ideas how I can make 700+ pdf files in this loop without Access bombing out? Thank you. |
#2
|
|||
|
|||
ACC2000: bloat after saving report via code
I would not implement a solution that relies on changing the design of a form
or report from code. In this case, I would probably create a single record table to store the filter information. The report's record source could then be modified to use this table. Then use code in your loop to set the values of the field(s) in the filter table. You can then open the report without having to worry about changing the record source property. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "A Man" wrote: So I have a routine that makes a PDF for each customer in a loop. It opens a report in design view, changes the recordset to process only 1 customer, save the report, and makes a PDF from the changed report. I use this code inside the loop: ----------begin code---------- DoCmd.OpenReport sReportname, acViewDesign ' Must come before "set rpt" Set rpt = Reports(sReportname) 'rpt.Filter = sWhere ' Access 2000 has a bug here, cannot use .Filter 'rpt.FilterOn = True rpt.RecordSource = sMySQL DoCmd.Close , , acSaveYes DoEvents ' Allow Access to save report. iRptCount = iRptCount + 1 sPDFName = curdir & sSlsno & sCustnum & ".pdf" ' PDF path and filename. Call StatusBar("Report " & iRptCount & " of " & cnt & ", Filename=" & sPDFName) ' DEBUGGING bSaveDialog = False bShowPDF = False blRet = ConvertReportToPDF(sReportname, vbNullString, sPDFName, bSaveDialog, bShowPDF, 0, "", "", 0, 0) ' Lebans method ----------end code---------- My loop bombs out about record 310. What I found out is, the Access MDB file slowly increases in size as I go through the loop (I watched it via Windows Explorer) until it reaches a point where I get an error "Cannot continue. OpenReport not available at this time." This error is from the Docmd above when I open the report in design mode to change it. If I compact the db via code then I can manually run my loop again, but it will run from the beginning, not from where the error was. Another problem is, I cannot select a range of records less than 300 in number because the customer number is a string, not numeric. (I did not design the db, a major national company did.) And if I compact the db every 300 records, it will stop my program, and till not restart it. Oh, and I verified that Access 2000 does have a bug whereby the .Filter property of the report object does not work reliably. So that is out of the question. Anyone have any ideas how I can make 700+ pdf files in this loop without Access bombing out? Thank you. |
Thread Tools | |
Display Modes | |
|
|