Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    speckytwat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    22

    Calculated 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...)

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You do not need code; you can set the control source of the tax textbox control to =[labour]*0.2

    You should not need to store the tax value in the underlying table since, in most cases, calculated values are not stored.

  3. #3
    speckytwat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    22
    Thanks, by doing this though I wouldn't be storing the value in the Tax box in the Tax field in the table, am I right? I'd just be displaying 20% of the Labour field, in the Tax field, but the Tax field should actually store this value of 0.2 * Labour permanently.

    If I change the control source, what effect would this have on other data (number values) already inserted into the Tax field in existing records?

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Thanks, by doing this though I wouldn't be storing the value in the Tax box in the Tax field in the table, am I right?
    Correct


    Why do you need to store the calculated value? Just store the labour value (as it is before applying the rate) and the rate (0.2) itself. You generally store the values used in the calculation but not the calculated value.

  5. #5
    speckytwat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    22
    Well, the company want to be pay able to query on the amount of tax paid over time, add it up, compare between different suppliers / clients etc. which is why it was set up as a "proper" field in the table...

    I was also trying to set this up using the Query design function, but all this seems to do is create a new datasheet, there didn't seem to be a way of somehow dropping it into the form as a new field...

    Seriously considering going back to Excel where this would have been much much easier!

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Well, the company want to be pay able to query on the amount of tax paid over time, add it up, compare between different suppliers / clients etc. which is why it was set up as a "proper" field in the table...
    This can easily be done with a couple of queries even without having the calculated value stored. You would do the calculation on the fly

    query name: qrytaxdetail
    SELECT companyID, labour, taxrate, labour*taxrate as taxvalue
    FROM your table


    SELECT companyID, sum(taxvalue) as SumOfTaxes
    FROM qrytaxdetail
    GROUP BY CompanyID

  7. #7
    speckytwat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    22
    Problem is, I can't seem to see how I can get any queries I create to display on the form. Just a bit uncomfortable about not using all the data too (originally all this came from some Excel sheets, where the tax had been figured out manually and entered in the field as a static number- so still want o keep this data if possible. That's the problem in a way, I'm trying to create something a bit better than just users calculating and adding the tax value themselves.

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Problem is, I can't seem to see how I can get any queries I create to display on the form.
    You would create the query and then bind a form to the query.

    You can also use the query and compare to what you currently have in the table to make sure the calculated value in the query matches the value that was entered. This should give you assurances that the data is sound.

  9. #9
    speckytwat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    22
    That's the problem, when I went in to Query Design and selected the two fields, I could create a small datasheet from it but that's about it, it doesn't have a form view, just Datasheet, Pivot table etc. I can't see any menu command for creating a form view from the query, so it isn't currently any use to the end user...

    I managed to create a new form from the query but all this does is allow a typed-in value for Labour to have tax calculated for it- what I want is for the existing form (where the user is adding in all the new records) to have this same function. The other thing is that it seems to retain a value I typed in earlier for Labour. So all in all the query isn';t a lot of use from an end user perspective unfortunately...

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Highlight the query in the navigation pane, then Create (main tool bar), Form. In Access 2007, if you have a query or table highlighted and click on create form, Access will automatically create a form bound to that data source. In Access 2003, you could use the form wizard and then Access prompted you for the data souce (table or query) and you would select what you wanted from there.

  11. #11
    speckytwat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    22
    Thanks, yes I did create a new form from the query, unfortunately what I really want is for that query function to be on the form where the users add all the new records, rather than its own new form, which just has the Labour and Tax fields (and the Tax field isn't actually a field at all; really, just a calculation)

    How can I get the query onto the form for adding the new records? I can only see how to create a new form from it...

    To try and make it easier to explain I've attached two screenshots, one of the form that I created from the query, and one of the query in design view...

  12. #12
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    A form can only be bound to 1 record source (either a table or a query). You have the following options:

    1. Alter your current form's record source to include the calculated value (i.e. change it from a table to a query).

    2. Add a subform to your current form, the subform would be bound to the query you created, but you would have to relate the main form record to the subform record; I don't know how you would do that without additional information on your table structure.

    3. Use a control on the form that uses the Dlookup() function that will use the query you created, again you will have to link the DLookup() to the current record.

    I think option 1 is the best approach

  13. #13
    speckytwat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    22
    That's a bit of a problem then, because the whole purpose of the Add New Record form is that it links to the Transactions table and allows users to fill in the relevant data and add new records.

    If I change it to point to the query, it will stop working as an input form for the Transactions table and will just be the pointless "calculate 0.2 times the preceding value" form, so the users will be able to do even less as far as I can see??

  14. #14
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    If I change it to point to the query, it will stop working as an input form for the Transactions table and will just be the pointless "calculate 0.2 times the preceding value" form, so the users will be able to do even less as far as I can see??
    If a query is based on a single table, you can still base a form on the query to add new records, so I don't follow what you are saying.

    Storing a calculated value is not a good database practice because it can mess up your data integrity if someone goes in and changes a value that was used to calculate it.

    I would strongly recommend going back to what I said earlier, have the user enter the labour value and the rate and then just have a control that shows (but does not store) the calculated tax value. If you need to check previously entered tax values to verify the rate, that should be done outside of the data entry form. I would use an update query to back calculate the rate and add it to the table (you would have to have a field for it of course). Once you get that taken care of you can remove the field that holds the calculated tax value. That way your data will be consistent between current and old records.

  15. #15
    speckytwat is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    22
    I tried changing the Add New Record form so that it pointed to the query rather than the table, and this caused all the various text boxes to stop working. Also the query form only has the two fields involved in the calculation (Labour and tax) so I don't see how it can be used to add new records (which have a load of other fields besides these).

    I'm trying to figure how I can have these queries (such as the one that shows the calculated tax value) on the original Add New Record form (which I really want to keep as it took quite a while to set up and get working), when a form can apparently only point to either a single table or a single query but not both.

    Also if the calculated tax value isn't stored, I don't see how I can then run reports on it which the users will want- they will want to know how much has been paid by a certain contractor for example, etc. If it isn't stored I can't get the value?

    I'm thinking this is starting to sound like far more effort than its worth, as it sounds like I have to rewrite my forms as query-based rather than table-based at the very least. It seems that I can't have the query for calculation AND the fields for adding the record all on the same form (at least not easily).

    Starting to despair with this as it seemed logically as it might be a simple thing to do...

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated fields
    By graciemora in forum Forms
    Replies: 1
    Last Post: 02-04-2011, 06:07 AM
  2. calculated fields
    By nashr1928 in forum Forms
    Replies: 1
    Last Post: 07-21-2010, 04:49 PM
  3. Summing Calculated Fields
    By Zoran in forum Queries
    Replies: 1
    Last Post: 03-31-2010, 01:59 PM
  4. Sum of calculated fields
    By nkuebelbeck in forum Forms
    Replies: 9
    Last Post: 03-12-2010, 01:32 PM
  5. many decimal places in calculated fields
    By GordS in forum Access
    Replies: 1
    Last Post: 02-04-2009, 11:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums