Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving to Table (2.0 :()

    ...hopefully I am just missing something....

    I have designed a form that my company uses to log quotes in and post to a table.
    I am having a problem getting the total quote amount to post to the table.
    Right now it is a calculated field that multiplies the dollar amount by the piece amount.
    I have thought about putting an invisible control source for the total amount but cannot get the calculated field to mirror/copy/auto-update to the control sourced field.
    I have thought about setting an update query to run independently and update the total amount column.
    I have thought about omitting it entirely and just doing the calculations in a subsequent query.
    Then I thought about asking for help.

    Which do you recommend and did I miss something?

    Thanks,
    Mark

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving to Table (2.0 :()

    Just my opinion ... I do not store these type of calculated fields in a table, because they can be recalculated when needed in a query, form or report.
    However, if you feel you must .... On the OnExit event of the Cost or Quantity textbox (which ever is entered last), place code similar to this:

    Private Sub Quantity_Exit(Cancel As Integer)
    'Calculates and displays Extended Cost

    Dim vCost As Currency
    Dim vQuantity As Double
    Dim vcalcExtendedCost As Currency

    vCost = [Forms]![yourFormName]![Cost]
    vQuantity = [Forms]![yourFormName]![Quantity]
    vcalcExtendedCost = vQuantity * vCost
    [Forms]![yourFormName]![yourTotalQuotetextboxName] = vcalcExtendedCost
    Requery
    Refresh

    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving to Table (2.0 :()

    I recommend you do not store the calculated value in your table unless you have a very good reason. One of the rules set by one of the first three normal forms is not to include any value in a table that depends on the value in another field. If you have the cost of the object and the number of objects you can always recreate the total value in a query or report.

    If you absolutely must store the total value in the table because of some business rule then add a control to the form and set its control source property to the total field in your table. Set this control's visible property to no or false. In the before update event of the form set this control equal to the control with the calculated value.

Posting Permissions

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