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

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


"Larry" <2larry2@2maximizesoftware2.com> wrote in message 
news:AD9B4E14-4DE6-41CF-A2FD-C93C07F38162@microsoft.com...
>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
>
> 

On Apr 18, 9:36 pm, "Larry" <2lar...@2maximizesoftware2.com> wrote:
....
> 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

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.

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message 
news:%23IAONhfgHHA.4936@TK2MSFTNGP04.phx.gbl...
> 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
>
>
> "Larry" <2larry2@2maximizesoftware2.com> wrote in message 
> news:AD9B4E14-4DE6-41CF-A2FD-C93C07F38162@microsoft.com...
>>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
>>
>>
>
> 

"PatdeLux" <patrick.honorez@gmail.com> wrote in message 
news:1176928751.597350.73920@e65g2000hsc.googlegroups.com...
> On Apr 18, 9:36 pm, "Larry" <2lar...@2maximizesoftware2.com> wrote:
> ...
>> However, an update query evidently doesn't do
>> calculated fields.
>>
> 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...

> Patrick
> www.idevlop.com
>