Binding a foreign key when adding a new value to the master-detail table Delphi

Question

There are 2 tables linked by master-detail. When adding a new value to the detail table, the foreign key selected from the master table is not bound.

The M-D connection itself is performed on the form using two Dblookupcombobox and DataSource, ADOQuery for each, respectively. enter image description here

Using the [ + ] buttons, new values are added that are not present in the combobox. But the problems start at the second [ + ] (aka detail), when creating a new line, you need it to bind the foreign key from the previous LookUpComboBox (Master). Button code of the second button [+]:

begin
Form4.ADOQuery1.SQL.Clear;
Form4.ADOQuery1.SQL.Add('Select City from City WHERE   City='+#39+Form5.DBEdit1.Text+#39); //checking for duplicates
Form4.ADOQuery1.Open;
if Form4.ADOQuery1.IsEmpty then
  begin
  Form4.Query_city.FieldByName('City').AsString := Form5.DBEdit1.Text; //The PROBLEM is  SOMEWHERE HERE! It Adds a new value without binding the foreign key
  Form4.Query_city.Open;
  Form4.Query_city.Post;
  MessageBox(Handle, 'New data entered','Adding a new value',MB_ICONINFORMATION);
  end
 else
  begin
  Form4.Query_spec.Cancel;
  Form4.ADOQuery1.Cancel;
  MessageBox(Handle,PChar(''+Form5.DBEdit1.text+' already on the list!'),'Error',MB_ICONWARNING);
  end;
 end;

The new value is written to DBEdit1. It has a corresponding binding to tables. So How i can insert field with with the corresponding foreign key?

Answer 1

You are making this unneccessarily difficult because of the way your code is structured. Try something like this instead:

Open ADOQuery1, SELECTing its entire contents, with e.g.

procedure TForm4.OpenCitiesTable;
begin
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('Select City from City');
  ADOQuery1.Open;
end;

and leave it open while the user does whatever operations lead to wanting to add a new city.

Then, when the users wants to add a city, call this procedure, e.g. supplying the City value from Form5.DBEdit1.Text.

procedure TForm4.AddCity(ACity : String);
begin
  ACity := Trim(ACity);  // remove any leading or trailing blanks
  //  Do any formatting checks on ACity here, re.g convert it to Proper case

  //  check for adding a duplicate
  if ADOQuery1.Locate('City', ACity, []) then begin
    ShowMessageFmt('%s is already in the City table', [ACity]);
    Exit;
  end;

  try
    ADOQuery1.Insert;
    ADOQuery1.FieldByName('City').AsString := ACity;
  finally
    ADOQuery1.Post;
    //  at this point you might want to refresh the contents of whatever source
    //  you are populating Form5.DBEdit1.Text from
  end;
end;

I assume you can adjust the code relating to TForm4.Query_spec yourself;

Btw, you might want to consider using a TDBLookUpComboBox instead of your DBEdit1.