Multiple expressions in one event procedure (After Update)

[Guest]

Hi Everyone,

My question is, is it possible to have multiple expressions within one
Event Procedure? Here’s what I’m trying to do;

I have 5 fields – # of Crashes, # of Injuries, Crash Rate, Traffic Volume,
Injuries per Crash

Expressions
1. Traffic Volume = # of Crashes / Crash Rate
2. Injuries per Crash = # of Crashes / # of Injuries

Event Procedures (After Update)
# of Crashes
Private Sub Number_of_Crashes_AfterUpdate()
[Intersections_DataTable.Traffic Volume] = [Number of Crashes] / [Crash Rate]
[Intersections_DataTable.Number Injuries per Crash] = [Number of Injuries] /
[Number of Crashes]
End Sub

# of Injuries
Private Sub Number_of_Injuries_AfterUpdate()
[Intersections_DataTable.Number Injuries per Crash] = [Number of Injuries] /
[Number of Crashes]
End Sub

I’m trying to have Traffic Volume and Injuries per Crash auto calculate
whenever a value in # of Crashes, # of Injuries or Crash Rate change.

Right now I have expressions 1 and 2 in a Event Procedure (After Update) for
# of Crashes. I also have expression 2 in an Event Procedure (After Update)
for # of Injuries.

The problem is that it seems to only partially work correctly. When I first
created it and would enter numbers into # of Crashes the Event Procedure
would auto calculate the value for Traffic Volume just fine. Then I started
to enter numbers into the # of Injuries field which uses Expression 2, this
to seemed to work fine. But now when I enter numbers into the # of Crashes
field it will only change Injuries per Crash. For some reason it seems to be
skipping over Expression 1 and only doing Expression 2. Am I missing
something in my # of Crashes After Update Event Procedure that tells the
program to look at Expression 1 AND Expression 2. Thanks in advance for any
help you can provide.

mfranz

 

[John Vinson]

I’m trying to have Traffic Volume and Injuries per Crash auto calculate
whenever a value in # of Crashes, # of Injuries or Crash Rate change.

Why?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

You can set the Control Source of the [Injuries per Crash] textbox to
the expression.

If you MUST - for some reason - store the data in the table (bearing
in mind that you might thereby be storing incorrect data), you can
calculate it in the Form's BeforeUpdate event (rather than any of the
control's events).

John W. Vinson[MVP]

 

[Guest]

Hi John,

Thanks for the help. The reason I’m trying to have the Traffic Volume and
Injuries per Crash auto calculate is because I’m trying to reduce the work
load of the person(s) who will be using the database. These are just two out
of 8 or 9 additional fields that all have their values created through an
mathamtical formula. So rather than make the person figure out the values for
each field times each new record that had to be entered, I wanted to reduce
it down to just a couple of fields they had to enter. Each record is going to
represent the same location but in a different year.
I ended up creating an Update Query that seems to do the job so thanks also
for that idea. I also tried placing it directly in the form as you suggested
but nothing seemed to happen. The rows that I wanted calculated are in a
subform, would I place the expression in the main form’s BeforeUpdate or in
the subform’s BeforeUpdate? Thanks again for all your help, I really
appreciate it.

mfranz

I’m trying to have Traffic Volume and Injuries per Crash auto calculate
whenever a value in # of Crashes, # of Injuries or Crash Rate change.

Why?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

You can set the Control Source of the [Injuries per Crash] textbox to
the expression.

If you MUST - for some reason - store the data in the table (bearing
in mind that you might thereby be storing incorrect data), you can
calculate it in the Form's BeforeUpdate event (rather than any of the
control's events).

John W. Vinson[MVP]

 

[John Vinson]

Hi John,

Thanks for the help. The reason I’m trying to have the Traffic Volume and
Injuries per Crash auto calculate is because I’m trying to reduce the work
load of the person(s) who will be using the database.

I agree absolutely that they should be automatically calculated.

Where I disagree is in the idea of STORING that calculated value in
the table.

It is *NOT NECESSARY* to store the values in the table in order to
present the results to the person using the database; in fact, to
reiterate, it is *WRONG* to store the values in the table, if they can
be calculated.

These are just two out
of 8 or 9 additional fields that all have their values created through an
mathamtical formula. So rather than make the person figure out the values for
each field times each new record that had to be entered, I wanted to reduce
it down to just a couple of fields they had to enter. Each record is going to
represent the same location but in a different year.
I ended up creating an Update Query that seems to do the job so thanks also
for that idea. I also tried placing it directly in the form as you suggested
but nothing seemed to happen. The rows that I wanted calculated are in a
subform, would I place the expression in the main form’s BeforeUpdate or in
the subform’s BeforeUpdate? Thanks again for all your help, I really
appreciate it.

Neither.

If you can calculate the value in an Update query then you can
calculate - and display - the value in a Select query.

It sounds like you're assuming that the value can only be shown to the
user if it is stored in the table. That assumption IS WRONG. You can
base a Form on a query with a calculated expression; you can base a
Report on a query; you can export a query to an external file.

If you can explain why your database is an exception to this very
basic rule, it is *possible* to store the derived data (in the
Subform's BeforeUpdate event if you must know); it's just a Bad Idea
to do so!

John W. Vinson[MVP]