Jim Lawrence
accessd at shaw.ca
Fri Jun 8 10:34:31 CDT 2012
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