How to Autocomplete several Tables with DBEdit

Question

I have two tables (actually 3).

One table collects data from the fields of two other tables using a drop-down list. Data is added to the other two tables using a TDBEdit.

The problem is that now I have to enter the same data twice: the first time in TDBEdit, and the second time in TDBLookupComboBox.

How to add a record to TDBEdit so that it writes data to both tables, not just to DB_ALL

Answer 1

I hope I have understood what you are asking for, given the structures of the tables shown in your screenshot.

The code below shows how to set up a DBLookUPComboBox to display a city to select for a given person in a person table. I've used TClientDataSets so that it is self-contained abd so that the entire setting upo is done in code rather than by property settings in the Object Inspector.

Obviously, the DBGrid and dbNavigator are connected to the dsPerson datasource.

Code

  TForm1 = class(TForm)
    cdsCity: TClientDataSet;
    cdsPerson: TClientDataSet;
    dsPerson: TDataSource;
    DBGrid1: TDBGrid;
    DBLookupComboBox1: TDBLookupComboBox;
    dsCity: TDataSource;
    DBNavigator1: TDBNavigator;
    procedure FormCreate(Sender: TObject);
  end;
  [...]
  procedure TForm1.FormCreate(Sender: TObject);
  var
    Field : TField;
  begin

    Field := TIntegerField.Create(Self);
    Field.FieldName := 'CityId';
    Field.DataSet := cdsCity;

    Field := TStringField.Create(Self);
    Field.FieldName := 'City';
    Field.Size := 40;
    Field.DataSet := cdsCity;

    cdsCity.CreateDataSet;
    cdsCity.InsertRecord([3, 'Moscow']);
    cdsCity.InsertRecord([4, 'Leningrad']);

    Field := TIntegerField.Create(Self);
    Field.FieldName := 'PersonId';
    Field.DataSet := cdsPerson;

    Field := TIntegerField.Create(Self);
    Field.FieldName := 'CityID';
    Field.DataSet := cdsPerson;

    Field := TStringField.Create(Self);
    Field.FieldName := 'Name';
    Field.Size := 40;
    Field.DataSet := cdsPerson;
    cdsPerson.CreateDataSet;

    cdsPerson.InsertRecord([1, 4, 'Ivan']);
    cdsPerson.InsertRecord([2, 3, 'Kate']);

    DBLookupComboBox1.DataField := 'CityID';
    DBLookupComboBox1.DataSource := dsPerson;
    DBLookupComboBox1.KeyField := 'CityID';
    DBLookupComboBox1.ListField := 'City';
    DBLookupComboBox1.ListSource := dsCity;

  end;

Note that instead of (or as well as) using a DBLookUpComboBox, you could also define a lookup field in the cdsPerson dataset, by adding the code below before the call to cdsPerson.CreateDataSet. If you do, the cdsPerson will have an extra, CityName, column. This will display in the DBGrid - you may need to scroll it right to see it - and if you click in one of the CityName cells, you'll see that it activates an in-place, drop-down list from which a city can be selected, like so

When you select a different City Name, the CityID in the Person record is automatically updated.

  Field := TStringField.Create(Self);
  Field.FieldName := 'CityName';
  Field.Size := 40;
  Field.DataSet := cdsPerson;
  Field.FieldKind := fklookUp;
  Field.LookUpDataSet := cdsCity;
  Field.LookUpKeyFields := 'CityID';
  Field.LookupResultField := 'CityName';
  Field.KeyFields := 'CityID';

If I have understood your comment correctly, try this:

• add a second DBGrid and DBNavigator and a DBEdit to the form.

• set all their datasources to dsCity and set the DBEdit's DataField to CityName.

You can then add a new City to the City table and specify its CityID (in the grid) and CityName (in the grid or DBEdit). Notice that as soon as you save it using the second DBNavigator, you can then click in the CityName cell of the Person grid and the new CityName will be in the drop-down list. If you wanted to do this edit to the current record of the Person table, you could to that by adding an AfterPost event handler on the City table and add code to it like this:

procedure TForm1.cdsCityAfterPost(DataSet : TDataSet);
begin
  cdsPerson.Edit;
  try
    cdsPerson.FieldByName('CityId').AsInteger := DataSet.FieldByName('CityId').AsInteger;
  finally
    cdsPerson.Post;
  end;
end;