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

    Unanswered: Calculations in Combo Boxes

    I'm trying to calculate a couple of fields in a form. It seems simple enough but there might be a little trick you experts can help me out with.
    Here it goes:
    I'm trying to calculate two fields: OrderAmount and DiscountAmount.

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

    They're both on the same form (Orders). And they're both combo fields, even though the second combo field; OrderAmount is protected since it's populated when OrderType is filled out.

    2) I also have another field called MarketingSourceCode in the same form as above. When I fill it out, it populates a field called MarketingSourceDesc, when this field is populated, it, in return populates another field called; DiscountAmount.
    Eg: If MarketingSourceDesc is AP25 , DiscountAmount returns $25.

    Both OrderAmount and DiscountAmount fields are Currency fields and combo boxes.

    All I want to do is: OrderAmount - DiscountAmount to get the OrderTotal.
    But when I do the calculation, it uses their ID fields to do the calculation. In other words; in the orders table; Yearly is the first record and its OrderTypeID is 4 and AP25 is the second record and its DiscountAmountID is 2.
    SO WHEN I CALCULATE THE DIFFERENCE BETWEEN THESE TWO FIELDS I GET 2, INSTEAD OF GETTING THE REAL )SALE AMOUNT - DISCOUNT AMOUNT).

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Try pulling the value from the combobox by using the column method Ex:

    CustomerComboBox.Column(4, CustomerComboBox.ListIndex + 1)

  3. #3
    Join Date
    Jan 2004
    Posts
    64

    Smile

    Thanks for your quick reply. I appreciate it. However, I didn't understand the solution.
    Where does the statement you gave me go?
    Eg: OrderType and OrderAmounts are comboboxes. When a value is picked from the OrderType then this return the $ amount for this order type.
    Do I put your statement in the control box for the OrderAmount and build and expression for it?
    Or do I put in the Row Source of the OrderAmount combobox?

    Thanks again for your help. This will save my life today.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by bsarman
    Thanks for your quick reply. I appreciate it. However, I didn't understand the solution.
    Where does the statement you gave me go?
    Eg: OrderType and OrderAmounts are comboboxes. When a value is picked from the OrderType then this return the $ amount for this order type.
    Do I put your statement in the control box for the OrderAmount and build and expression for it?
    Or do I put in the Row Source of the OrderAmount combobox?

    Thanks again for your help. This will save my life today.
    Whatever control uses the OrderTotal should have something like:

    ThatControl.Value=OrderAmounts.Column(XX,ListIndex ) - DiscountAmounts.Column(XX,ListIndex)

    Put that line in your After_Update event of the discounts combobox. Keep in mind I don't know WHAT COLUMN the amount resides in ... Replace the XX with the correct zero based column #.

  5. #5
    Join Date
    Jan 2004
    Posts
    64
    I canít get it to work. Hereís my two combo boxes. Can you tell me how I should do this the correct way?

    Combo box 1: OrderAmountF
    Control Source: OrderTypeID
    Row Source Type: Table/Query
    Row Source: SELECT [Order Types].[OrderTypeID], [Order Types].[OrderAmount] FROM [Order Types]
    Note: Order Types table has 3 columns: OrderTypeID, Description, OrderAmount
    Bound Column: 1
    Nothing in the Event.

    Combo box 2: DiscountAmountF
    Control Source: MarketingSourceCodeID
    Row Source Type: Table/Query
    Row Source: SELECT [DiscountTable].[MarketingSourceCodeID], [DiscountTable].[DiscountAmount] FROM [DiscountTable]
    Note: DiscountTable table has 3 columns: MarketingSourceCodeID, MarketingSourceCodeDesc, DiscountAmount
    Bound Column: 1
    Nothing in the Event.

    To total these 2 fields I created a text box and called it OrderTotals. In the Control Source of the OrderTotals, I tried to build an expression and put this in there. But it hasnít worked.
    [OrderTotals].[Value]=[OrderAmountF].[Column](3,[ListIndex])-[DiscountAmountF].[Column](3,[ListIndex])

    Could you tell me what Iím doing wrong?

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by bsarman
    I canít get it to work. Hereís my two combo boxes. Can you tell me how I should do this the correct way?

    Combo box 1: OrderAmountF
    Control Source: OrderTypeID
    Row Source Type: Table/Query
    Row Source: SELECT [Order Types].[OrderTypeID], [Order Types].[OrderAmount] FROM [Order Types]
    Note: Order Types table has 3 columns: OrderTypeID, Description, OrderAmount
    Bound Column: 1
    Nothing in the Event.

    Combo box 2: DiscountAmountF
    Control Source: MarketingSourceCodeID
    Row Source Type: Table/Query
    Row Source: SELECT [DiscountTable].[MarketingSourceCodeID], [DiscountTable].[DiscountAmount] FROM [DiscountTable]
    Note: DiscountTable table has 3 columns: MarketingSourceCodeID, MarketingSourceCodeDesc, DiscountAmount
    Bound Column: 1
    Nothing in the Event.

    To total these 2 fields I created a text box and called it OrderTotals. In the Control Source of the OrderTotals, I tried to build an expression and put this in there. But it hasnít worked.
    [OrderTotals].[Value]=[OrderAmountF].[Column](3,[ListIndex])-[DiscountAmountF].[Column](3,[ListIndex])

    Could you tell me what Iím doing wrong?
    Yes. You can't do it that way. If you VIEW CODE and select the DiscountAmountF combobox you will get the Before_Update wrapper method and then if you select the After_Update event you'll get that wrapper method. Put the assignment code in there.

  7. #7
    Join Date
    Jan 2004
    Posts
    64

    Thumbs up

    This is the solution I've found with your help (and it's working)

    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

    My mistake was to look for a solution in the event portion of totals. I had to create the event in the afterupdate section of orderamount and discountamount.

    Thanks again for your kind help.

Posting Permissions

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