Storing calculated values

[Guest]

I know this is not good practice but there are one or two essential,
fixed-for-all-time calculated values that I do need to store in my tables.
A couple of weeks ago Doug Steele and John Vinson gave some seemingly clear
and simple advice but I haven't been able to get it working.
Using a very simple example:
Table and form include Field1, Field2, Field3.
Field3 = Field1 multiplied by Field2.
It displays fine in the form.
Does not store in the table.
What do I do, how and where, to force the value displayed in Field3 on the
form to be stored in Field3 in the table???
Many thanks
"Little Crow"

 

[Guest]

Backup the database first. Do not use the form but use an update query.

 

[Guest]

The text box control for Field3 should be a bound control, i.e. its RowSource
should be the name of the field. In the AfterUpdate event procedures of both
of the controls bound to Field1 and Field2 assign the computed value to the
control bound to Field3:

Me.Field3 = Me.Field1 * Me.Field2

Storing computed values is correct if the values from which they are derived
can change with time, but the computed value needs to remain constant at the
value computed at the time when the row was inserted into the table, e.g. in
an invoice which needs to reflect the prices when the invoice was raised, not
the current prices. If the values can always be derived from the base
values, however, the computed value should not be stored as it leaves the
door open to update anomalies.

Ken Sheridan
Stafford, England

 

[Guest]

Karl -
Could you explain what you mean by that?
I have built various queries but what do you mean by an "update query"?
Where is it placed - how does the calculated value field get to know that it
depends on this query?
And how does this get to force the calculated value into the table as soon
as a user has input the source data? Is it automatic, somehow? It would be no
good if it required someone to trigger it manually.
Thanks

 

[Guest]

Thanks, Ken, but unfortunately that didn't work.
I just cannot believe how difficult it is to get a calculated amount to
store in the database - incredible!!!
I entered those expressions carefully into the AfterUpdate property of both
Field1 and Field2, and when I entered a value into Field1 on the form I got
this message:

The Expression AfterUpdate you entered on the event property setting
produced the following error - The object does not contain the Automation
object 'Me'

What now, please?
Many thanks
Little Crow
Beaconsfield, England

 

[John Vinson]

Thanks, Ken, but unfortunately that didn't work.
I just cannot believe how difficult it is to get a calculated amount to
store in the database - incredible!!!
I entered those expressions carefully into the AfterUpdate property of both
Field1 and Field2, and when I entered a value into Field1 on the form I got
this message:

The Expression AfterUpdate you entered on the event property setting
produced the following error - The object does not contain the Automation
object 'Me'

You need to remove the expression from the AfterUpdate line, and
instead click the ... icon and choose "Code Builder". Put that line of
code between the Sub and End Sub lines that Access will give you for
free. The control's AfterUpdate property (once you close the VBA
editor) will show [Event Procedure].

It's actually easy - once you know the secret handshake and the knock!
<g> To some extent it may be intentionally a bit difficult, just
BECAUSE it's usually something that *should not* be done.

John W. Vinson[MVP]

 

[Guest]

YES! YES! YES! AT LAST!
John, can't thank you enough!!!

Thanks, Ken, but unfortunately that didn't work.
I just cannot believe how difficult it is to get a calculated amount to
store in the database - incredible!!!
I entered those expressions carefully into the AfterUpdate property of both
Field1 and Field2, and when I entered a value into Field1 on the form I got
this message:

The Expression AfterUpdate you entered on the event property setting
produced the following error - The object does not contain the Automation
object 'Me'

You need to remove the expression from the AfterUpdate line, and
instead click the ... icon and choose "Code Builder". Put that line of
code between the Sub and End Sub lines that Access will give you for
free. The control's AfterUpdate property (once you close the VBA
editor) will show [Event Procedure].

It's actually easy - once you know the secret handshake and the knock!
<g> To some extent it may be intentionally a bit difficult, just
BECAUSE it's usually something that *should not* be done.

John W. Vinson[MVP]

 

[Guest]

Ok.... Go to the database view. On the left side of the window it says
Objects and list Tables, Queries, Forms, Reports, etc. Click on Queries.
Above the label Objects are menu and icons for Open, Design, and New. Click
on New. It opens a window that list Design View as the default in the list.
Click OK. Again another window that the has three panes - Tables, Queries,
Both. Scroll down the list to your table and double click. Then click Close.
Click and drag the fields you will be working with the Field row of the
grid. Enter any criteria in the Criteria row of the grid to limit the
records to be selected. Click the red exclamination mark on the icon bar to
run the select query. Peruse the data to make sure it only contains the
records that you want. Click on the green triangle to return to the design
view.

If this select query pulls the correct records then change it to an Update
Query by clicking on the icon that has two datasheets offset and says 'Query
Type' when you hold the mouse cursor over it a couple of seconds. Click and
select Update Query.

In the grid row labeled Update To enter the update information for the field
you want to update (column). In your case it is the calculation.

The query will evaluate the calculation for each record and then update the
field with the calculation results.

 

[Guest]

Karl -
I really appreciate that detailed explanation. I'm going to try that first
thing tomorrow - it's after midnight here in UK now and I need my beauty
sleep. Badly, some say...
Thanks again
Little Crow