Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I update a table with a form (Access 2002)

    I have a from that I want to use to update a table; I have a field that I have placed on the form but I want this value to update a feild on my table. How do I set the value to be the calculated field.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I update a table with a form (Access 2002)

    Are you trying to store calculated fields in a table ?
    This should never be done as the calculation can always be recalculated in a query.
    Or did I not understand your question ?
    Francois

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

    Re: How do I update a table with a form (Access 2002)

    Your description is not very clear to me ("a field that I have placed on a form" should be "a control that ..."), but as far as I understand it, you can do either of the following:

    - Set the value of the (table) field to the calculated value in the Before Update event of the form.
    or
    - Set the value of the (table) field to the calculated value in the After Update event of each of the controls that contribute to the result of the calculation.

    Also see Francois' remarks.

  4. #4
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I update a table with a form (Access 2002)

    Han's I thought of using the after update control on the calculated field; but how do I write the code to update my intended field based on a calculated field being updated?

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

    Re: How do I update a table with a form (Access 2002)

    Not the After Update event of the calculated control (it won't occur), but of each control that contributes to the outcome of the calculation. Without knowing details, I can only give a very generic description:
    - Open the form in design view.
    - Select a control whose value plays a role in the calculation.
    - Activate the Event tab of the Properties window.
    - Click in the After Update event.
    - Select [Event Procedure] from the dropdown list.
    - Click the ... to the right of the dropdown arrow.
    - Make the code look like this, with the appropriate names and code substituted:

    Private Sub ControlName_AfterUpdate
    Me.SomeField = <insert calculation here>
    End Sub

    - Repeat for other controls that contribute to the calculated value.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I update a table with a form (Access 2002)

    I tried setting the update event on the calculated field; this way when the calculated field is updated it will set the value to the field I want to write to the table.

    So I did the following:

    Private Sub AccrualAmtGrs2_AfterUpdate()

    Me.AccrualAmtGrs = AccrualAmtGrs2

    End Sub

    AccrualAmtGrs is the field I want to write to the table; based on the AccrualAmtGrs2 being updated. But this did not work.

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

    Re: How do I update a table with a form (Access 2002)

    The After Update event occurs if the user has directly modified the value of a control. It does not occur for calculated controls.

    So please read my previous reply again.

  8. #8
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I update a table with a form (Access 2002)

    Thank You Hans; used a field that the user will update and which is used in the calculated field. Now it works perfectly.

Posting Permissions

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