Base data sheet form will not display calculated Field

Question

I have a Data Sheet form which has a calculated field column. However the field will not display even though it has the correct value. The field in question is "numRisk":

Sub Calculate_Risk (Form As Object)
    Dim OrderPrice, IfDonePrice, TotBrSymComm, BrComm, Risk As Double
    Dim Symbol As String
    Dim IntRateMult, noContracts As Integer
    If MinTick = 0 OR Rate = 0 Then
       Exit Sub
    End If
    Symbol = RTrim(Form.getByName("txtSymbol").CurrentValue)
    If Symbol = "" Then
       Exit Sub
    End If
    OrderPrice = Form.getByName("fmtOrder_Price").CurrentValue
    IfDonePrice = Form.getByName("fmtIf_Done_Price").CurrentValue
    noContracts = Form.getByName("fmtNo_Contracts").CurrentValue
    If NOT USIntRates Then
       Risk = ABS(OrderPrice - IfDonePrice) / MinTick
    Else
       Risk = ABS(OrderPrice\1 - IfDonePrice\1) * MinTick
       IntRateMult = IIf(Symbol = "FV" OR Symbol = "TU",400, 200)
       Risk = ABS(Risk - IntRateMult * ABS(OrderPrice - OrderPrice\1
       IfDonePrice + IfDonePrice\1)) 
    End If
    Risk = Risk * MinTickVal / Rate
    TotBrSymComm = BrSymComm + BrSymCommAud
    BrComm = IIf(TotBrSymComm = 0, BrCommission, BrSymCommAud + BrSymComm/Rate)
    Risk = noContracts*(Risk + BrComm * 2)
    Form.getByName("numRisk").Value = Risk
 End Sub

The subroutine is called from the following routine which is triggered when the form is loaded:

Sub FromListForm(Event as Object)
     Dim Form As Object
     Dim TodaysDate As New com.sun.star.util.Date
     Dim CurrDate As Date
     Form=Event.Source.getByName("MainForm_Grid")
     Form.RowSet.first()
     Do Until Form.RowSet.isAfterLast()
        Get_Contract(Form)
        Get_Broker_Comm(Form)
        Calculate_Risk(Form)
        If isEmpty(Form.getByName("OrderDate").Date) Then
           CurrDate = Date()
           TodaysDate.Day = Day(CurrDate)
           TodaysDate.Month = Month(CurrDate)
           TodaysDate.Year = Year(CurrDate)
           Form.getByName("OrderDate").CurrentValue = TodaysDate
        End If
        Form.RowSet.next()
     Loop
     Form.RowSet.last()
End Sub

Also is there a more efficient method to cycle through the rows? As this seems so slow I can see the row pointer moving down the table as each row is processed.

Answer 1

If I understand correctly, you're trying to enter individual values into each cell in a column of a tablegrid control? I don't believe that's possible.

Inside a tablegrid control, all values have to come from the underlying query. I recommend writing a query to do these calculations, and using that query as the basis for the form - that would solve both the problem of displaying the calculated result as well as improving the load speed of the form (since database logic in determining query results is almost always more efficient than a macro going row-by-row).

Alternately, you could have the calculated field be standalone, showing only the calculated result for the currently selected row of the tablegrid control. In this scenario, the "form loaded" event would only do the calculation for the first row, and the calculating macro would be triggered each time the row selection changed.