views:

1036

answers:

4

I have coded a MS Access 2000 report that displays a calendar with one month per page and projects added to particular days. The only data in the underlying record source is a list of months. The structure is created via the On Page event, which also reads in other data.

When this report is opened, I've noticed that the On Page event does not seem to be triggered for the first page. (I attribute this to the fact that On Page in reports is activated when a page is cached rather than when a page is displayed, such as On Current for Access forms.)

When the report is displayed my work around is to use the On Activate event to force the On Page subroutine to run even though that event has not been called. However when the report is exported it does not trigger the On Activate event and the first page of the export is in one of two formats: 1) if the report was open in Access (ie On Activate had been triggered previously) the first page is identical to either the page after the one being displayed or the last page, except with the correct month (which comes from the underlying record source) 2) if the report was not open in Access the first page contains just the structure within the report design view (ie lots of empty boxes)

My best workaround is to force a (otherwise pointless) cover page to ensure the first page contains nothing that needs code to run, but this is far from ideal. Can I force the code to run for the first page of an export? Or maybe I'm misunderstanding how On Page works and I need to restructure my code? (I've also noticed that On Page seems to run twice for the last page).

A: 

Have you considered the Format event for the various sections, especially the Detail section? Format or Print are a more usual events for manipulating reports.

Remou
+1  A: 

I would recommend restructuring your code so that you build your data in one query, multiple queries, or in VBA, and then open the report with the new datasource. I might still have Access 2000 at home to check, but at work I can test both 2003 and 2007, and in both versions, the OnPage event fired before each page was displayed. If you are experiencing different behavior, I suspect it's because Access isn't sure how to handle what you are asking it to do.

Typically a report like the one you are describing would be designed the other way around: the datasource for the report would contain all the project information. Is there something about the data you're trying to display that prevents you from building a query that would contain all of it?

Dave DuPlantis
A: 

Remou,

That's exactly the answer I was hoping for - I thought these events should exist but could not find them. Many thanks.

Dave DuPlantis,

Thanks for your answer. I did originally consider building the report the other way around, but I thought this would be too complex given the nature of the results I wanted. Ie each page of the report uses different boxes to build a calendar (corresponding to the days of the week within that month). Project data is then added to appropriate boxes and various properties changed.

Even if I use queries as much as possible, I think I still would have to use code when each page was loaded, so would not escape this issue. Maybe there's a bug in Access 2000 relating to On Page, which luckily is not reproduced for On Format and On Print. Thanks again.

J Davey
Another option, if you're up for it, is building the report in Word or Excel instead of in Access. It'll take more work to set up, but you'll have much more control over how the report is displayed, and you won't have to use events to do it.
Dave DuPlantis
A: 

Could you please specify the format that you are exporting to? There are numerous problems in Access 2000 that you are using, and 2003 that I am using with export to RTF. There are a few tricks out there I have picked up that enable format on the fly, checkboxes, and other things that normally don't make it to the output file.

The boxes you mentioned for the calendar would be a challenge if exporting to rtf.

--cheers,

Mike

Mike