Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    2 Star Lounger
    Join Date
    May 2002
    Location
    Dubai, UAE, United Arab Emirates
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

    Thanks for your input!

    e

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Dubai, UAE, United Arab Emirates
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •