Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    64

    Unanswered: Calculate Fields With AfterUpdate or Another Method?

    I was trying to calculate a couple of fields in a form. The calculation works but when I scroll to the next or previous record, the total from the last record still appears in the record I scroll to. If I scroll to another record and update any of the 4 fields below, the total gets corrected but if there’s no update done in the record scrolled into then the total shows wrong. So if I add an order for Customer 3 and the total is: $1,000, when I go to the next or previous record that $1,000 total still shows for Customer 1, and 3, 4 and so on.
    Note: I use AfterUpdate to get the calculation where the problem seems to be.

    I add up four fields to get totals for OrderTotals.

    1) This field is called OrderType in the form. When I fill it out, it populates another field called OrderAmount.
    Eg: If OrderType is Yearly
    OrderAmount returns $1,000

    2) This field is called PromoCode in the same form as above. When I fill it out, it populates 4 other fields: PromoCodeDesc, PromoStartDate, PromoEndDate, DiscountAmount.
    Eg: If PromoCode is AP25
    PromoCode: $25.00 Off First Month, PromoStartDate: 1/1/04, PromoEndDate: 1/30/04. DiscountAmount: $25.

    3) AdditionalSaleF
    4) AdditionalDiscountF

    I used the code below:
    ----------------------------------------
    Private Sub MarketingSourceCodeF_AfterUpdate()

    OrderTotals.Value = Nz(Me.OrderAmountF.Column(1, Me.OrderAmountF.ListIndex), 0) + AdditonalSaleF.Value - Nz(Me.DiscountAmountF.Column(1, Me.DiscountAmountF.ListIndex), 0) - AdditionalDiscountF.Value

    End Sub
    ----------------------------------------
    Also, there’s another issue with fields 1 and 2.
    For Field 1;
    OrderType is a combobox field and the salesrep selects the record from the drop down list which auto populates OrderAmount field.
    OrderAmount is combobox as well which doesn’t make sense since this is auto populated when OrderType is selected. When I try to make OrderAmount field a text field and set the control Source as the table where OrderAmount is located, it doesn’t work.

    For Field 2;
    PromoCode is a combobox field and the salesrep selects the record from the drop down list which auto populates 4 other fields: PromoCodeDesc, PromoStartDate, PromoEndDate, DiscountAmount.
    Same issue as above:
    All PromoCodeDesc, PromoStartDate, PromoEndDate, DiscountAmount fields are comboboxes as well which doesn’t make sense since these are auto populated when PromoCode is selected. When I try to make PromoCodeDesc, PromoStartDate, PromoEndDate, DiscountAmount fields text fields and set the Control Source as the table where they’re located, it doesn’t work.

    Your urgent help is really appreciated since I’m supposed to finish this by today.

    THANKS EVERYONE FOR TAKING THE TIME TO HELP!!!

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    You must do the summation on the Forms' ON Current event. Onlt then you'll get correct calc on every record.

  3. #3
    Join Date
    Jan 2004
    Posts
    64

    Thumbs up

    That worked wonderfully!!!
    Now I get the correct totals for each record.
    Thank you very much.

    Would you be able to suggest a solution for the second part of my question regarding the combo boxes? I'm trying to set the auto populated fields in the calculation fields to be text boxes rather than combo boxes since there's no data entry required here.

    Thanks again for sharing the knowledge.

Posting Permissions

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