Data Sheet will not display Calculated Field

[p75213]

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.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.

[doug]

While I do not fully understand the purpose of the code, it appears that you are changing the value in a control using a macro. There is a step that I do not see in the code snippets, which is to commit the new value in the control. Thus, for example, after Form.getByName("numRisk").Value = Risk in most situations you will need to execute Form.getByName("numRisk").Commit.

This might not be as obvious with the datasheet view, because I think sometimes it appears to operate normally without that last step. However, omitting to commit will at least sometimes result in the problems that I think you are describing. It is a simple remedy to a problem I also previously encountered, and I hope this helps.

[p75213]

Hi Doug,
Unfortunately the Commit method did not help. I notice if I change the underlying database fields rather than the form fields the change is reflected in the form ie. Form.RowSet.Columns.getByName(“database_field_name”).Value = Risk. However the field “numRisk” is a calculated field and is not linked to a single database field.

[doug]

so if you were to type directly into that field, would it accept the new data? As in, is the control Read Only? Is the recordset updateable? If it has a caclulated field, it might not be.

[p75213]

The field is read only. This is the case even when it is set to Read-Only=“N”. The recordset is updateable.

[p75213]

I made the calculated field (it is originally calculated on another screen 99% of the time) a field in table futures_orders. The whole thing works perfectly now. I know your not supposed to do that but hey - whatever it takes. Thanks for your help.