Understanding Detail Section Format Event 2

[mdthornton]

I'm having trouble with a report where I'm summing columns and printing sub-totals at certain points within the detail section based on another control's value. For reasons I won't go into here I can't sum up in a group footer (ie the normal way), I have to do it in the detail section.

I understand the theory behind the format/print and retreat events but can someone explain the following.

I reduced my result set (to 7 rows) so that the report fit on 1 page. If I break into the code however the format event is firing 14 times and the formatcount is always = 1.

Any help/explanation would be greatly appreciatated.

Mike T

 

[JoeMiller]

Mike:

This came out of online help for the Format event of Online Help:


For report detail sections, the Format event occurs for each record in the section just before Microsoft Access formats the data in the record. A Format macro or event procedure has access to the data in the current record.

So if you have 7 records, that's 7 format events for 1 detail section (that's why format show's 1). Now the reason it's firing 14 times is because of the Print event. The print event fires twice (and hence all other events twice) so that Access can do things like show you Page X of Y. That's why you are getting 14 fires of your code. You may want to look at this web page, it's unrelated to your exact issue, but talks about the printing firing twice.

http://www.mvps.org/access/reports/rpt0013.htm

HTH Joe Miller
joe.miller@flotech.net

 

[mdthornton]

Joe

Thanks for the article - that helped. To test the theory I removed the x of y page footer control and bingo only 7 format events and all my subtotals were correct. What I don't get is the formatcount value - I thought this would be = 2 on the second pass.

I narrowed my specific problem down to the first subtotal in the report and realized that for certain report criteria the value of the last subtotal in the report was being included in the second pass. I've fixed this by resetting the subtotal variable in one of the group footers.

Thanks for your help. If you have any thoughts on the formatcount value let me know.

Mike T

 

[JoeMiller]

I believe that FormatCount doesn't aggregate, so it will always be 1. Not too sure on that though, sorry.. Joe Miller
joe.miller@flotech.net

 

[mdthornton]

No worries. From the article you sent, it seems that in the right circumstances I can use the Me.Pages value as a surrogate FormatCount. I've tested this also and it seems to work. In fact, I have some reports in a previous project which I could only get to work by maintaining arrays of key/value pairs of data which I had seen before. This is a much simpler approach.

Thanks again,
Mike T