Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    11

    Unanswered: Autocalculated fields in form

    I'm trying to set up certain fields in a form to have autocalculated values based on values in preceding fields.

    For example this is what I tried for a field whose value should be 20% of a preceding field:

    Code:
    Private Sub Tax_BeforeUpdate(Cancel As Integer)
        If IsNull(Me!Labour) Then Exit Sub
        Me!Tax = (Me!Labour * 0.2)
    End Sub
    But this doesn't autofill for some reason, it just ignores any value entered (it doesn't give a syntax error in the code editor so I assume that that's ok at least).

    Basically I need this field to automatically fill in with a value of 20% of the other field. I also have another calculation which is basically:

    NetValue = 0 - (Labour + Materials)

    And a third which is:

    GrossValue = NetValue + Tax (Tax being the field in the code above)

    They should also round to 2 decimal places if possible.

    Can't see how this can be done though?

    (I did try using a Query Design as suggested elsewhere on the net- it seems such a messy convoluted way for a simple calculation, and although I could make thre query work in table view, I couldn't see how it could be inserted into the form so the calculated result is visible. Also I have another problem with it because we already have some values in the Transactions table for Tax, which were entered manually by the users- so if this field suddenly becomes autocalculated it could cause issues with the data that's already there...)
    Last edited by speckytwat; 04-27-11 at 11:36.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure whether the problem is there or not but I would use the AfterUpdate event and no rely on the default properties of objects. Also it seems to me that you should use the event on the Labour control, not on the Tax control, so:
    Code:
    Private Sub Labour_AfterUpdate(Cancel As Integer)
        If Not IsNull(Me.Labour.Value) Then
            Me.Tax.Value = (Me.Labour.Value * 0.2)
        End If
    End Sub
    You could even spare the IsNull() test as (Null * <Something>) yields Null.

    Also be aware that BeforeUpdate and AfterUpdate events are not activated if the value of the control is modified by some code: it only works when the modification happens through the interface. Ex.
    Code:
    Sub SomeProcedure()
        Me.Labour.Value = 125 ' <-- This will not trigger the AfterUpdate event for the control!
    End Sub
    Finally, computed values should never be stored into the tables and there is nothing messy in computing them in a query. The mess will come from the way you use to handle them and, sooner or later, stored computed values will cause some data inconsistency.
    Have a nice day!

  3. #3
    Join Date
    Mar 2011
    Posts
    11
    Hi, thanks for the reply. I tried that code but after I'd saved and went back to the form, as soon as I tried to input anything anywhere I got an error message, something about a conflict between values.

    I thought I could just set the control source for the Tax field to =Labour*0.2, but the probably then is it doesn't get saved into a field in the database, as far as I can see, it just displays the calculated value but doesn't put it in, say, a Tax field in the transactions table.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you simply want to display a calculated value you should use an unbound control for it.
    Have a nice day!

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by speckytwat View Post
    I thought I could just set the control source for the Tax field to =Labour*0.2, but the probably then is it doesn't get saved into a field in the database
    If the Control Source has been set to

    =Labour*0.2


    then it is an Unbound Field, as it should be! And as Sinndho said, "...computed values should never be stored into the tables..." They should simply be re-calculated when needed, in forms, reports, etc..

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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