select ec.*
from tt_emp e, tt_emp_contract ec
where (coalesce(e.tt_nonactive,0)=0)
and e.tt_emp_id = ec.tt_emp_id

with EmployeeContracts do
begin
  // Retrieve contracts of all active employees
  if (not Active) then
  begin
     Open;
  end;

  // Is record already correctly positioned?
  if (FieldByName(SEmpID).Asinteger=AEmpID) and
     (FieldByName(SFromDate).AsDateTime<=APeilDatum) and
     (FieldByName(SToDate).AsDateTime>=APeilDatum) then
  begin
     Result := True;
     Exit;
  end;

  if not FindKey([AEmpID]) then  // Make sure the data are up to date. Refresh from the server.
  begin
     Refresh;  // ERROR HERE
  end;

  if FindKey([AEmpID]) then
  begin
     while (FieldByName(SempID).Asinteger=AEmpID) and (not EOF) do
     begin
        if (FieldByName(SFromDate).AsDateTime<=APeilDatum) and
           (FieldByName(SToDate).AsDateTime>=APeilDatum) then
        begin
           Result := True;
           Exit;
        end;

        Next;
     end;
  end;
end;

unit uClientDatasetRefresh;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Param, FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf,
  FireDAC.DApt.Intf, FireDAC.Stan.Async, FireDAC.DApt, FireDAC.UI.Intf,
  FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Phys, FireDAC.Phys.FB,
  FireDAC.Phys.FBDef, FireDAC.VCLUI.Wait, Data.DB, Vcl.StdCtrls, Vcl.Grids,
  Vcl.DBGrids, Vcl.ExtCtrls, FireDAC.Comp.Client, FireDAC.Comp.DataSet,
  Datasnap.Provider, Datasnap.DBClient;

type
  TFrmClientDatasetRefresh = class(TForm)
    ClientDataSet1: TClientDataSet;
    DataSetProvider1: TDataSetProvider;
    FDQuery1: TFDQuery;
    FDConnection1: TFDConnection;
    Panel1: TPanel;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    function PositionOnEmployeeContractRecord(AEmpID: integer; ADate: TDateTime = 0): Boolean;
  public
  end;

var
  FrmClientDatasetRefresh: TFrmClientDatasetRefresh;

implementation

{$R *.dfm}

procedure TFrmClientDatasetRefresh.Button1Click(Sender: TObject);
begin
   PositionOnEmployeeContractRecord(20652);   // Has records in tt_emp_contract
   PositionOnEmployeeContractRecord(1024);    // Has no records in tt_emp_contract
end;

const
   SEmpID    = 'tt_emp_id';
   SFromDate = 'tt_fromdate';
   SToDate   = 'tt_todate';

function TFrmClientDatasetRefresh.PositionOnEmployeeContractRecord(AEmpID: integer; ADate: TDateTime = 0): Boolean;
begin
   Result := False;

   if (AEmpID=0) then Exit;
   if ADate=0 then ADate := Date;

   with ClientDataSet1 do
   begin
      if (not Active) then
      begin
         Open;
      end;

      if (FieldByName(SEmpID).Asinteger=AEmpID) and
         (FieldByName(SFromDate).AsDateTime<=ADate) and
         (FieldByName(SToDate).AsDateTime>=ADate) then
      begin
         Result := True;
         Exit;
      end;

      if not FindKey([AEmpID]) then
      begin
         Refresh;
      end;

      if FindKey([AEmpID]) then
      begin
         while (FieldByName(SempID).Asinteger=AEmpID) and (not EOF) do
         begin
            if (FieldByName(SFromDate).AsDateTime<=ADate) and
               (FieldByName(SToDate).AsDateTime>=ADate) then
            begin
               Result := True;
               Exit;
            end;

            Next;
         end;
      end;
   end;
end;

end.

object FrmClientDatasetRefresh: TFrmClientDatasetRefresh
  Left = 0
  Top = 0
  Caption = 'ClientDataset Refresh'
  ClientHeight = 276
  ClientWidth = 560
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  Position = poScreenCenter
  PixelsPerInch = 96
  TextHeight = 13
  object Panel1: TPanel
    Left = 0
    Top = 0
    Width = 560
    Height = 41
    Align = alTop
    BevelOuter = bvNone
    TabOrder = 0
    ExplicitLeft = 16
    ExplicitTop = 8
    ExplicitWidth = 185
    object Button1: TButton
      Left = 32
      Top = 8
      Width = 75
      Height = 25
      Caption = 'Test'
      TabOrder = 0
      OnClick = Button1Click
    end
  end
  object DBGrid1: TDBGrid
    Left = 0
    Top = 41
    Width = 560
    Height = 235
    Align = alClient
    DataSource = DataSource1
    TabOrder = 1
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    IndexFieldNames = 'tt_emp_id;tt_fromdate'
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 288
    Top = 8
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = FDQuery1
    Left = 376
    Top = 8
  end
  object FDQuery1: TFDQuery
    Connection = FDConnection1
    SQL.Strings = (
      'select ec.*'
      'from tt_emp e, tt_emp_contract ec'
      'where (coalesce(e.tt_nonactive,0)=0)'
      'and e.tt_emp_id = ec.tt_emp_id')
    Left = 448
    Top = 8
  end
  object FDConnection1: TFDConnection
    Params.Strings = (
      'DriverID=FB'
      'Database=*****.GDB'
      'Password=masterkey'
      'User_Name=SYSDBA')
    LoginPrompt = False
    Left = 528
    Top = 8
  end
  object DataSource1: TDataSource
    DataSet = ClientDataSet1
    Left = 216
    Top = 8
  end
end

TT_EMP_ID    Integer
TT_FROMDATE  DateTime
TT_TODATE    DateTime
TT_HOURS     Float
... more

Index TT_I0_EMP_CONTRACT on TT_EMP_ID, TT_FROMDATE   Primary, Unique