SubReport Counts Doubled 1

[LarryDeLaruelle]

I just encountered a strange behaviour in Access.

I created a sub report to display names and positions. In the general declarations I dimmed integer variables to hold the count of positions.

In the Detail Section's On_Format event I have a Select Case statement to increment the counters based on the value held in the field 'Position'.

I put unbound text controls in the Report Footer and in the Footer's On_Format event I assign the values of the counters to the appropriate control.

All this works just fine.

I then created an unbound report and placed the sub report in the detail section. Now, when I run this unbound report all values in the sub-form counters are doubled.

I put a break point at the beginning of the Select Case statement and discovered that this code is processing twice. My guess (uneducated at best) is that the subform's On_Format event is being invoked twice -- once from the main form's On_Open (?) and once from the sub-form's On_format(?).

I did a work-around by simply dividing the final result in the sub-form's controls by two. Not elegant but it works.

Any ideas on why this is happening and how I can implement a better solution to this?

As always, I appreciate your help. Thank.s
Larry De Laruelle
larry1de@yahoo.com

 

[jflachman]

Does this help? From Access help subject for the On Format event:

There are times when Microsoft Access must return to previous sections on a report to perform multiple formatting passes. When this happens, the Retreat event occurs as the report returns to each previous section, and the Format event occurs more than once for each section. You can run a macro or event procedure when the Retreat event occurs to undo any changes that you made when the Format event occurred for the section. This is useful when your Format macro or event procedure carries out actions, such as calculating page totals or controlling the size of a section, that you want to perform only once for each section.
Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[LarryDeLaruelle]

Jonathan:

Thanks. That explains the strange behaviour and gives me a place to fix it.

I've gotten so used to using Tek-Tips that I forget that Access help does, occasionally, offer useful help.

Thanks for the tip.
Larry De Laruelle
larry1de@yahoo.com

 

[LarryDeLaruelle]

Jonathan:

The doubled results I was getting seemed to be the result of having the Can Grow property of the sub report set to Yes which triggered the multiple formatting passes.
Unfortunately, the Undo method does not seem to be available on reports.

I solved the problem by setting both the Can Grow and Can Shrink properties to No so that the formatting occured only once.

Thanks again for steering me in the right direction.
Larry De Laruelle
larry1de@yahoo.com

 

[jflachman]

That seems like a band-aid solution that leaves your report formatting suboptimal.

Rather than that, put the opposite code in the Retreat event that you have in the Format event.

That way, when the Format event happens by itself, you get the right answer. Thereafter, when the Format event happens again, the Retreat also happens and just subtracts the data back from your totals. Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[LarryDeLaruelle]

Jonathan:

One more gold star.

That is a nice solution. Certainly solves the problem of having to guestimate the largest size necessary for the sub report.

Thanks again.
Larry De Laruelle
larry1de@yahoo.com