Update query question

[Larry]

I have a table with a field where the data is a calculated value. The
original query's field looks like this:

calcUnitTotalFBM: ([Thickness]*[Width]*[calcUnitTotalLength])/12

Originally, I have this calculated value entered in the actual field in the
table, which is UnitTotalFBM on the original data entry form. And there
will usually be multiple records.

But he user wants to be able to change either Thickness or Width, which
requires the recalculation of UnitTotalFBM for all the records on that form.
An update query seems like the solution because it would update just that
field with the new value. However, an update query evidently doesn't do
calculated fields.

Any advice would be appreciated...

-Larry

 

[Michel Walsh]

Use a standard SELECT query, not an update one. The other fields are not
recomputed, automatically, as ONE field from which it depends, change. No,
issue a manual Me.Recalc (in the after update event of the controls
associated to the fields, if this is what you want).


Hoping it may help,
Vanderghast, Access MVP

 

[PatdeLux]

However, an update query evidently doesn't do
calculated fields.

Who says that ?
You may use an expression in your update query.
Plus you may use a parameter or an expression that point to a form
field, even unbound.

Patrick
www.idevlop.com

 

[Larry]

Hi Michel,

If it were a single record, that would work. The values Thickness and Width
were in a different table on the main form and UnitTotalFBM on a subform
with multiple records. But I did figure it out.

In the AfterUpdate event for the Thickness and Width fields on the main
form, I run this:

Me.Refresh
'Recalculate FBM.
Set rst = frmCustomerLumberTallyUnit.Form.Recordset
rst.MoveFirst

Do Until rst.EOF
frmCustomerLumberTallyUnit.Form.UnitTotalFBM =
frmCustomerLumberTallyUnit.Form.calcUnitTotalFBM
rst.MoveNext
Loop

So that goes through and updates all the records with the new calculated
value. Obviously, the calculated field calcUnitTotalFBM resides in the
select query on the subform.

And thanks for the input. I appreciate it!

Larry
Maximize Software, Inc.

Use a standard SELECT query, not an update one. The other fields are not
recomputed, automatically, as ONE field from which it depends, change. No,
issue a manual Me.Recalc (in the after update event of the controls
associated to the fields, if this is what you want).


Hoping it may help,
Vanderghast, Access MVP

I have a table with a field where the data is a calculated value. The
original query's field looks like this:

calcUnitTotalFBM: ([Thickness]*[Width]*[calcUnitTotalLength])/12

Originally, I have this calculated value entered in the actual field in
the table, which is UnitTotalFBM on the original data entry form. And
there will usually be multiple records.

But he user wants to be able to change either Thickness or Width, which
requires the recalculation of UnitTotalFBM for all the records on that
form. An update query seems like the solution because it would update
just that field with the new value. However, an update query evidently
doesn't do calculated fields.

Any advice would be appreciated...

-Larry

 

[Larry]

Who says that ?

Microsoft's own documentation for one.

You may use an expression in your update query.
Plus you may use a parameter or an expression that point to a form
field, even unbound.

If you can figure out how, I'd like to know...