This month, Chris Weber addresses questions from real-life,
Access training seminar attendees about report totals, splash
screens, trapping keystrokes, managing control widths, when
there’s more in a memo than meets the eye, triple state
check boxes, tables for one, and using the new Dirty
event. The answers aren’t always obvious, especially to
the new generation of Access users.
I have a report that lists order totals by employee with a
running total alongside. However, I only want to show the
running total field alongside the very last order total. I
know I can just put the total in a footer below the list, but
the requirement is to show it alongside the last entry to recreate
the same look we had in a previous system. I’ve tried
hiding the control by setting its visible property, but I can’t
seem to figure out when the last entry will format so that I
can unhide it. Is there a property that will let me do this?
As I understand your question, you currently have the
data shown in Figure 1, but you’d like it to print as
shown in Figure 2. The fundamental problem here is
either finding out when the last order total will format,
or possibly knowing ahead of time what the total will
be so that when the running total control equals that
value it can be made visible. In either case, a domain
function comes to the rescue.
One way of finding which item is the last one is to
use a domain function. I’m particularly fond of domain
functions as they’re fast and easy to use, require a
single line of code, and don’t require saving a separate
query object in the database container. Regardless of
which domain function you use, they all have the same
functionname(fieldname, table or query name, _
The wherecondition is the same as a where clause
in a query without the word “WHERE,” just as you
might use in an OpenForm method.
For my first solution to knowing when the last
order total will print, you’ll need to know the print
sequence of the employee’s order totals. For example,
if you have the totals listed by date, then you can use
the DMax function in an invisible control in a header ...
Figure 1. How the
Figure 2. How the
data should look.