Databases - Delphi - DBLookupComboBox

The DBLookupComboBox provides a simple way to associate a long descriptive name (stored in a lookup table) with a short (or numeric) key.

When used with the KADao components, DBLookupComboBox's work fine - they display all related records.

However, when using the Interbase controls with Firebird, only the first record of the lookup table (or related query) is shown. Once you scroll through the possible values, then the list displays with multiple values as expected.

Firebird Related Fixes

When using the Interbase controls with Firebird, only the first record of the lookup table (or related query) is shown. Once you scroll through the possible values, then the list displays with multiple values ... as expected.

Assuming that DBLookupComboBox2 is connected to Query_DataSource.ListSource, simply moving to the last record fixes the problem.

procedure TForm1.DBLookupComboBox2DropDown(Sender: TObject);
begin
  Query_DataSource.DataSet.Last;
end;

IBDatabase1 -> IBQuery1 -> Query_DataSource

Without this fix, when IBQuery1.SQL is

select  "User_ID" from "Group_User_Map_tbl"
where "Group_ID" = 'Uad_1'

the drop down list shows all the records, but when IBQuery1.SQL is

select  "User_ID" from "Group_User_Map_tbl"

only one record is shown.

An alternate approach is

procedure TForm1.DBLookupComboBox2DropDown(Sender: TObject);
begin
  Query_DataSource.DataSet.FetchAll;
end;

Multiple DBLookupComboBoxes

Using KADao components, a single DataSource can be used with several DBLookupComboBox's ... each showing a different subset of the data by setting the filter when the list is displayed. However, with IB Components, redefining the query (or filter) changes the values for all associated DBLookupComboBoxes.

This is a real pain.

This is one work around ... but it uses regular ComboBoxes ... not DBLookupComboBoxes.

procedure TAdd_Port_Form.Init_Picklists;
var
 flag : boolean;
 s : string ;
 i : integer;
 ds : TDataset;

  procedure Load_ComboBox(Filter:string; CB: TComboBox);
  var
    i : integer;
  begin
    CB.Items.Clear;
    ds.Filter := '"Barcode_Type_ID" = ''' + Filter + '''';
    ds.Filtered := true;
    ds.First;
    flag := ds.Bof;

    if flag then begin
      s := ds.FieldByName('Barcode').AsString;
      CB.Items.Add(s);

      ds.Next;
      while not ds.eof do begin
        s := ds.FieldByName('Barcode').AsString;
        CB.Items.Add(s);
      ds.Next;
      end;
    end;

  end;

begin
  ds := SamplesDataModule.Barcodes_DataSource.DataSet;

  Load_ComboBox('Exp',   Port_UIComboBox  ); // Exposure Location
  Load_ComboBox('F_Hld', Filter_UIComboBox); // Filter Holder
  Load_ComboBox('S_Hld', Sample_UIComboBox); // Sample Holder
end;

Notice that for the IB components, FindFirst and FindNext are not implemented (they always return false).

Another solution is to provide a separate query object per lookup component.

Updating DBLookupComboBox's with new data

This is another area with no documentation.

Simple plan

Display a pick list based on some query
Post some data to the database based on the value in the pick list
When displayed again, the selected value should no longer appear in the pick list
Use Locate to check the table for an existing value (to prevent duplicates)

The main problem was that IBTable_Batches.Locate would not detect changes made to the table.

The solution is fairly simple

Use 2 components - one for the updates (I use an IBQuery) and one for the picklist (either an IBQuery or an IBTable)
Each component requires its own IBTransaction (very important)

This code works

  f := IBTable_Batches.Locate
           ('Batch_Name', v , [loCaseInsensitive]);
  if f then begin
    result := 'Duplicate';
    exit; // error
  end;


  s :=
     'INSERT into "Batches_tbl" (' +
     '"Batch_Name", "Description")' +
     ' Values (''' +
     Name        + ''', ''' +
     Description + '''' +
     ') '
    ;

 // Contents of ExecuteSQL(s) procedure - shown inline for readability

  IBQuery1.SQL.Text := s;
  IBQuery1.ExecSQL;
  IBQuery1.SQL.Text := 'commit'; // This allows other programs to see changes
  IBQuery1.ExecSQL;

 // end of ExecuteSQL


  IBTable_Batches.Close;         // Required to see the new record
  IBTable_Batches.Open;          // but won't work if the transaction is shared

Actually, Locate should not be used for this purpose because it potentially copies all the records from the server to the client. It is definitely better to execute a query and see if any rows are returned.

DBCtrlGrid

Give me a break, the DBLookupComboBox does not work in a DBCtrlGrid. (Actually, many components don't work in DBCtrlGrid's, but you would think that the DB components would.)

Here are a few work arounds

Author: Robert Clemenzi - clemenzi@cpcug.org