clearing variables after a section

[greg d. via AccessMonster.com]

hello. i'm self-taught with vba which means i may not make this very clear
so please bear with me...

i have a report where the grouping is by salesperson (header section
invisible, footer visible) and where the page header and page footer are both
visible. in the salesperson's footer section, i have a user-defined function
that creates a running total of certain values in the detail section for the
report (basically totalling up different parts of each sale for each
salesperson). the variables i'm using are public (i think -- they're at the
top of the vba page which is what i think makes them public).

the problem is i tried to call a function to clear them at the page footer
but it's not working... here's my line of code for this:

If Me.section("Detail").WillContinue = True Then Call ClearVars

the problem is that if the detail section is long enough to make a
salesperson's report move on to two pages, it resets the variables for the
2nd page.

what i would really need is an on-change event for the section but they
didn't ask me what i thought when they wrote access. i'm using 2007, if it
matters. thanks for the help.

greg

 

[Jim Burke in Novi]

First off the variables should be declared at the very top before any
subs/functions which it sounds like you did. You should define them as
Private (e.g. Private var1 as Integer), which means they're available
anywhere in the code for the report but nowhere outside of the report. It
sounds like you want to clear/reset your variables each time you come to a
new salesperson.You should show your salesperson header and just drag it so
that it doesn't take up any report space, and then right-click on the header
bar for it and select properties, then create an OnFormat event procedure for
the header. This is where you should intialize your salesperson-related
variables. Then create an OnFormat event proc for the salesperson footer to
do the calculations. Get rid of the 'If Me.section("Detail").WillContinue '
stuff. Hope I didn't misinterpret what your trying to do.

Jim B

 

[Marshall Barton]

hello. i'm self-taught with vba which means i may not make this very clear
so please bear with me...

i have a report where the grouping is by salesperson (header section
invisible, footer visible) and where the page header and page footer are both
visible. in the salesperson's footer section, i have a user-defined function
that creates a running total of certain values in the detail section for the
report (basically totalling up different parts of each sale for each
salesperson). the variables i'm using are public (i think -- they're at the
top of the vba page which is what i think makes them public).

the problem is i tried to call a function to clear them at the page footer
but it's not working... here's my line of code for this:

If Me.section("Detail").WillContinue = True Then Call ClearVars

the problem is that if the detail section is long enough to make a
salesperson's report move on to two pages, it resets the variables for the
2nd page.

what i would really need is an on-change event for the section but they
didn't ask me what i thought when they wrote access. i'm using 2007, if it
matters.

Note that it's unlikely that you function will do what you
want because the sections in a report can be processed as
many times and in whatever order is needed to generate the
report.

The "section change" event you are looking for is the group
header section's Format event.

 

[kaosyeti]

now i could swear that i had set it up the way you suggested at one point and
it didn't work, but i gave it a try again and low and behold, the numbers
computed properly. i must have tried so many combinations of clearing those
variables in different sections that i missed the obvious answer. thanks.

on the plus side, even more important was the snippet you included in your
post about public vs. private variables. i did NOT know the exact difference
and when i think of the implications of a variable being declared public
accidentially, it makes me nervious. i had thought that 'private' variables
were within a function or procedure and public were throughout the whole
object (form, report, etc.). but what you describe is totally different.
thanks a whole bunch for that!

greg

First off the variables should be declared at the very top before any
subs/functions which it sounds like you did. You should define them as
Private (e.g. Private var1 as Integer), which means they're available
anywhere in the code for the report but nowhere outside of the report. It
sounds like you want to clear/reset your variables each time you come to a
new salesperson.You should show your salesperson header and just drag it so
that it doesn't take up any report space, and then right-click on the header
bar for it and select properties, then create an OnFormat event procedure for
the header. This is where you should intialize your salesperson-related
variables. Then create an OnFormat event proc for the salesperson footer to
do the calculations. Get rid of the 'If Me.section("Detail").WillContinue '
stuff. Hope I didn't misinterpret what your trying to do.

Jim B

hello. i'm self-taught with vba which means i may not make this very clear
so please bear with me...

[quoted text clipped - 21 lines]

greg