Issue in DBGrid with dataset calculated field

Question

I have a calculated filed 'Balance' which display the balance amount between two dataset fields and I used the next code to calculate its value:

var
  Balance : Currency;

...

procedure TMyForm.MyQueryCalcFields(DataSet: TDataSet);
begin
  if MyQuery.FieldByName('MyFirst Field').AsCurrency > 0 then    
    Balance := Balance+MyQuery.FieldByName('MyFirst Field').AsCurrency;

  if MyQuery.FieldByName('MySecond Field').AsCurrency > 0 then   
    Balance := Balance-MyQuery.FieldByName('MySecond Field').AsCurrency;

  MyQuery.FieldByName('Balance').AsCurrency := Balance;
end; 

and I used TDBGrid to display the result of my query , The dbgrid display the correct values until I start srolling its vertical scrollbars, in this case it shows wrong values.

I tried assigning '0' to the balance but its the same (Wrong values when start scrolling).

How I can make the dbgrid keep showing the right values?

Answer 1

OnCalcField event handler is designed for calculations that rely on the current record values only.

If the dataset's class you're using allows that, I suggest you to calculate the balance field once, as soon as you load the data, for example:

var
    Balance : Currency;
begin
    Balance := 0;

    MyQuery.DisableControls();
    try
        //loading data
        MyQuery.Open;
        
        //calculating balance
        while(not MyQuery.Eof) do
        begin
            MyQuery.Edit;
            Balance := Balance + MyQuery.FieldByName('MyFirst Field').AsCurrency - MyQuery.FieldByName('MySecond Field').AsCurrency;
            MyQuery.FieldByName('Balance').AsCurrency := Balance;
            MyQuery.Post;
            
            MyQuery.Next;
        end;
        MyQuery.First;
    finally
        MyQuery.EnableControls();
    end;
end;