Results 1 to 8 of 8
  1. #1
    Join Date
    May 2011
    Posts
    4

    Unanswered: Updating table from calculated fields on a form

    Hi all, first time poster here.

    I'm writing a customer orders database, where I have a series of forms. I have an 'Orders' form which contains a number of calculations, which takes calculations from other forms to give Grand Totals. For example:

    =Sum([OrderLineSubTotal]) - this totals all of the sub totals for each Order Line (where a customer orders more than one product on the same order)

    Then,

    =[Orders Subform].[Form]![OrderSubtotal] - This formula takes the total from the previous calculation and displays this on the form.

    My problem now is how to update the related table with this calculated info. I have a 'Save Order' button on my form which writes all other form elements to the table, but not any calculated fields. From research, it looks like I may need to do the following:

    1) Create an update query that takes the new data from the form and writes it to the appropriate record on the table
    2) Trigger that update query via a command button on the form

    My question is, how do I do 1 & 2 above?!?!

    All replies greatly appreciated.
    MES

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's again all good practices to store a computed value into a table. Sooner or later this always leads to data inconsistency.

    If you really want to do that, you can build a dynamic query (assemble a SQL statement using VBA) and have it executed (Currentdb.Execute strSQL). Triggers do not exist in Access.
    Have a nice day!

  3. #3
    Join Date
    May 2011
    Posts
    4
    Thanks for the reply. So you wouldn't advise that I calculate the values on the form, but in the table itself? If so, would this be done by a query?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No
    Yes
    ..for the reasons mentioned in post #2

    there are reasons to store calculated data (usually related to performance ie you cna't get the query to return the results in an appropriate time, or the information is required for other purposes elsewhere), but these do not apply in this case
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2011
    Posts
    4
    Sorry, maybe I am not understanding this correctly. So the calculation should be performed in the table?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No: Calculations should be performed by the query, using data from the table, but the results of the calculations (the computed values) should not be stored in a table.

    Ex. 1) Table1:
    Column_A (numeric)
    Column_B (numeric)

    2) Query1:
    SQL = "SELECT Column_A, Column_B, Column_A + Column_B AS SumAB FROM Table1;"

    3) Form1: RecordSource = "Query1"
    Text_A (TextBox): ControlSource = "Column_A"
    Text_B (TextBox): ControlSource = "Column_B"
    Text_SumAB (TextBox): ControlSource = "SumAB"
    Have a nice day!

  7. #7
    Join Date
    May 2011
    Posts
    4
    Thanks Sinndho, I shall try this. One question though - I will eventually want to produce reports using queries based on some of the calculated values. If these are not stored anywhere, how will that be possible?

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You simply re-run the calculations, in the queries, when needed.

    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
  •