1. ## Calculated field/control (a2002)

I need to calculate energy usage for equipment. The equipment can be rated by 1 of 4 different units. Each unit involves a different equation and I want to save the result in a field, KW/H. I'm guessing SELECT CASE is the way to go and that I should put it on an event procedure for KW/H. I was thinking of including the field as a tab stop and using the "Got Focus" event. I don't however want it to recalculate the amount once it's entered unless I tell it to. Is there a better approach?

Elizabeth

2. ## Re: Calculated field/control (a2002)

G'day Elizabeth
At first blush I think I would put code on either the GotFocus event, or if appropriate - the AfterUpdate event for the variables the result depends on. You'll need to check that all your variables are entered when you calculated - whether using the GotFocus for the result or the AfterUpdate for the variables. You might want to consider a BeforeUpdate event to check the calculation has been done to ensure your record is complete before it's saved.
However - if you manually want to perform the calculation based on user input - then I guess a command button would do the job - again you'll have to check your variables are all present and accounted for before calculating.
Regards Ken.

3. ## Re: Calculated field/control (a2002)

Tell me more re the AfterUpdate for the variables. The calculations/fields or controls are below. Could you locate the AfterEvent on both [RateAmt] and [AmpRating]?
HP -- [kw/h] = [RateAmt] x .746
Watts -- [kw/h] = [RateAmt] x .001
Volts -- [kw/h] = ([RateAmt] x [AmpRating]) x .001
BTU -- [kw/h] = [RateAmt] x .00001

How were you thinking of checking that the calculation had been performed in the Before Update? Do an IF...THEN for a null value?

e

4. ## Re: Calculated field/control (a2002)

Working in the dark a little without your application in front of me ...

I assume that you have a control on the form for RateAMT and AmpRating.
I assume you have controls for HP, Watts, Volts, BTU as well.

Basically put the following code on the AfterUpdate event for RateAMT and AmpRating:

Sub XXX_AfterUpdate
If Not IsNull([RateAmt]) Then
.....[HP] = [RateAmt] * 7.46
.....[Watts] = [RateAmt] * .001
.....[BTU] = [RateAmt] * .00001
.....If Not IsNull([AmpRating]) Then
..........[Volts] = ([RateAmt] * [AmpRating]) * .001
.....Endif
Endif
End Sub

The BeforeUpdate requirement is really only valid if your fields in your record are not required. If they are required access will generate an error when the user tries to leave the record without entering all the required values. But say you don't have the ability to make all the fields required:

Sub Form_BeforeUpdate(Cancel as Boolean)
If IsNull([RateAmt]) Then
.....MsgBox "Warning: You cannot leave Rate Amount empty.",vbOKOnly+vbInfo,"Missing Information."
.....Cancel = True
ElseIf IsNull([AmpRating]) THen
.....MsgBox "Warning: You cannot leave Rate Amount empty.",vbOKOnly+vbInfo,"Missing Information."
.....Cancel = True
Else
.....Cancel = False
Endif
End Sub

... Something like that. Setting Cancel to True will stop the record being written from buffer to file.

Regards Ken

5. ## Re: Calculated field/control (a2002)

I have a cboRating that includes the choice of BTU, HP, Volts, Watts. That's why I was thinking there would be the need for IF...THEN. Does that change your approach?

E

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•