Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Upstate, New York, USA
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Write value from calculated control back to table (Access 2000 SR3)

    I have an unbound textbox on a form that calculates a sum of several other bound controls. I'd like to be able to write the calculated value back to the table but have no idea how.

    Any suggestions are appreciated.

    Kyle

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Write value from calculated control back to table (Access 2000 SR3)

    In general, it is not advisable to store calculated information in a table:
    - It takes up unnecessary space, since the value can be calculated from other values whenever it's needed.
    - There is the risk that the stored value gets out of date if the contributing values are changed directly.

    If you can perform the calculation on a form, you can probably perform it in a query too; you can then use the query wherever the calculated value is needed.

    Admittedly, there are some situations in which storing a calculated value is desirable. In that case, you can store the value when the record is being stored, in the BeforeUpdate event of the form as a whole.

    Let's say that the calculated control is named txtCalculated, and that the field to be updated is named StoredValue. Make sure that StoredValue is part of the record source of the form. The code could look like this:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.StoredValue = Me.txtCalculated
    End Sub

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Write value from calculated control back to table (Access 2000 SR3)

    You could use DAO in VB to set up a connexion to the DB and update the table manually. I do this a lot.

    If you're not confident with that, an easier and almost codefree way would be to have another control bound to the destination field (which you want to save the calculated value in) and copy the value into that field, maybe when you click a button. Then the table will be automatically updated when you close the record.

    Is that a help?

    James

  4. #4
    Lounger
    Join Date
    Feb 2001
    Location
    Upstate, New York, USA
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Write value from calculated control back to table (Access 2000 SR3)

    Thanks for the quick responses, Hans and James. I ended up using the button because I couldn't get the BeforeUpdate code to work for some reason - the field remained empty.

    I appreciate the help.

    Kyle

Posting Permissions

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