Dynamic line total

[wwwredback]

Hi All,

After yet more help.

I’ve got a combo box (AccountType) that on selection dynamically creates the fields in my subform (OrdersSubform), here is the code:

Private Sub AccountType_AfterUpdate()

Me.OrdersSubform.Form.UnitPrice.ColumnHidden = True
Me.OrdersSubform.Form.TradePrice.ColumnHidden = True
Me.OrdersSubform.Form.DIYPrice.ColumnHidden = True

Select Case Me.AccountType
Case "Account"
Me.OrdersSubform.Form.UnitPrice.ColumnHidden = False
Case "Trade"
Me.OrdersSubform.Form.TradePrice.ColumnHidden = False
Case "DIY"
Me.OrdersSubform.Form.DIYPrice.ColumnHidden = False
End Select
Me.OrdersSubform.Requery
End Sub

My problem is now to create a line total for each of the accounttypes depending on what is selected:

I have tried this piece of code but doesn’t seem to be working, can anyone give me any pointers of what is wrong please.

SELECT DISTINCTROW [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, Products.UnitPrice, Products.TradePrice, Products.DIYPrice, [Order Details].Quantity, [Order Details].Discount, IIf(Forms!Orders!AccountType="DIY",Products.DIYPrice,IIf(Forms!Orders!AccountType="Trade",Products.TradePrice,Products.UnitPrice)) AS Price, CCur(Price*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice
FROM Products INNER JOIN [Order Details] ON Products.ProductID=[Order Details].ProductID
ORDER BY [Order Details].OrderID;

Thanks

James.