Jim Hewson
jm.hwsn at gmail.com
Fri Jun 8 15:01:40 CDT 2012
As I was going through the ideas expressed by Jim below, I was bombarded with different opinions on using DoEvent. Most stated it would top out the CPU if used in Access and it was not a good idea to use it. One obscure site recommended a solution in passing talking about something else. It said to open the report in Preview, print it, close it and then go to the next report. It actually works! When printing the entire questionnaire, it appears that there is a blank sheet of paper behind the form... I can live with that. Thanks, Jim for your suggestions. Without them I would not have found the solution. Jim On Fri, Jun 8, 2012 at 12:29 PM, Jim Hewson <jm.hwsn at gmail.com> wrote: > These are new ideas, I haven't tried them. > I'll try them and see what happens. > Thanks, > Jim > On Fri, Jun 8, 2012 at 10:34 AM, Jim Lawrence <accessd at shaw.ca> wrote: > >> Hi Jim: >> >> It sounds very complex. >> >> I do not have an answer specifically to your situation as there is no way >> to >> guess at all the details without the same report sitting right in front of >> me. >> >> That said, I have ran into some similarly complex reporting issues and >> have >> found a few of methods which have always worked reliably. >> >> 1. I always run complex reports from calls to external code through the >> Report_Open event. In the external code the report source data can be >> easily >> modified then printed, then modified again and printed again etc. >> >> 2. Sometimes another report can be run from the initial report within the >> external code but it must be through the setting of a flag and running the >> DoEvent command and then on to the next report. >> >> 3. Of course one report can be daisy-chained to another all under program >> control within each report. >> >> 4. Another more risky report method is to turn off and on over-lapping, >> hidden and unhidden section details. These reports will work fine but get >> too tricky and the report may just crash and never be able to be edited >> again. >> >> 5. I do not trust the DLookup command. It has done weird things in the >> past. >> I always just make a call and run a query get the data needs. It is just >> about as fast but never fails or returns invalid info. There are times >> when >> there are multiple matches and it is beyond Dlookup to resolve. >> >> 6. It that does not work you can always roll your own. Back in A97 days, I >> hand coded a Access report. It was just about as pretty, just about as >> fast >> but it was very versatile. The output was written in Postscript and pushed >> directly to the printer port. For some clients, I do the same but instead >> I >> write the report in HTML and you can do anything in this type of report >> even >> add fancy fonts, calls to multiple data sources, add curves, circles, >> icons, >> insert pictures and so on. ;-) >> >> In summary I would suggest you take a look at the DoEvent command as it >> has >> solved more than one issue for me. >> >> Hope something here helps. >> >> Jim >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson >> Sent: Friday, June 08, 2012 7:15 AM >> To: Access Developers discussion and problem solving >> Subject: [AccessD] Problem with printing multiple reports >> >> Warning - this is rather long. >> >> Windows 7, Access 2007 -- This database has always been in A2007 - so it >> was never upgraded to a newer version. >> >> What I'm trying to do is print several reports using a button on a pop up >> (modal) form. The form is opened from a button on another form. Behind >> that form is a master form that stores pertinent data for doing several >> things and is kept open during the entire session. >> >> The database is used as a survey questionnaire. The researcher can can >> change questions (text, responses, order, question type, etc.), assign >> questions to sections (max 25), assign variables and question coding . >> Sections can be ordered in any way the researcher deems necessary. >> >> The reason I want to print multiple reports is that each section is a >> separate report and I want to print the entire questionnaire instead of >> printing one section at a time. The current questionnaire has 18 sections >> and therefore has 18 reports to be printed. All reports have sub-reports. >> Some have sub or sub-sub-reports - depending on the section critieria. >> >> The print questionnaire form works -- IF it is opened from the navigation >> pane. If it is opened by the button on the form - it crashes Access and >> closes the file. The message I get is "Microsoft Office Access has stopped >> working Window can try to recover your information and restart the >> program" My one choice is to "Restart the program". It prints the first >> section. >> >> The original code had a DAO recordset that used a query as a record >> source. With the recordset, I had a Do While ... Loop to find the >> specific >> section and report that needed to be printed in order. I used a DLookup >> function to find the report needed and then a select Case to find the >> specific report name. The where clause was in the Loop because it changed >> for each report. At the end just before the Loop, I had a >> rsPrint.MoveNext. >> >> It appeared that the .MoveNext was the issue because that's where it would >> crash. >> >> So... I read somewhere that maybe the .MoveNext might be culprit and that >> maybe changing the order of events might work... it didn't. I also read >> somewhere that maybe I need to put in .MoveFirst and .MoveLast at the >> beginning... didn't work... either before the With rsPrint statement or >> after the statement and before the Loop... didn't work. On this forum, I >> read that maybe I need to validate .BOF = False Or .EOF = False in an If >> statement and them go on with the Loop.... didn't work. >> >> I read that maybe the entire Loop thing was the problem... or even the >> Select Case was the issue... or the domain funtions... eliminated them... >> didn't work. >> >> I redid the form... it had hidden fields which populated on loading that >> had the criteria for the where clause and the specific report needed. So >> all I needed to do was call the field for the Where clause and the >> report... didn't work. >> >> I thought maybe it might be the pop up or modal status was the issue... >> nope, still crashed. >> >> After everything I've done.... I finally come to conclusion that it's >> crashing just after DoCmd.OpenReport statement... the first report prints >> but it ALWAYS crashes and doesn't get to the next line. >> >> Where can I go from here? Am I stuck telling my users that they can only >> print one section at a time? >> >> Thanks in advance for any insight you have. >> >> Jim >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > >