Making a Drop Down List in a DBGrid

Want to make the best data editing grid ever? Below are instructions for building a user interface for editing lookup fields Inside a DBGrid. Specifically, we'll be looking at how to place a DBLookupComboBox into a cell of a DBGrid.

What this will do is call upon information from a data source that will be used to populate a drop-down box.

To show a DBLookupComboBox inside a cell of a DBGrid, you first need to make one available at run time...

Create a Lookup With a DBLookupComboBox

Select the "Data controls" page on the Component Palette and pick a DBLookupComboBox. Drop one anywhere on the form and leave the default name of "DBLookupComboBox1." It doesn't matter where you put it since most of the time, it will be invisible or floating over the grid.

Add one more DataSource and DataSet component to "fill" the combo box with values. Drop a TDataSource (with the name DataSource2) and TAdoQuery (name it AdoQuery1) anywhere on the form.

For a DBLookupComboBox to work properly, several more properties must be set; they're the key to the lookup connection:

• DataSource and DataField determine the main connection. The DataField is a field into which we insert the looked-up values.
• ListSource is the source of the lookup dataset.
• KeyField identifies the field in the ListSource that must match the value of the DataField field.
• ListFields is the field(s) of the lookup dataset that are actually displayed in the combo. ListField can show more than one field but multiples should be separated by semicolons.
  You have to set large enough value for the DropDownWidth (of a ComboBox) to really see multiple columns of data.
  Here's how to set all the important properties from code (in the form's OnCreate event handler):

procedure TForm1.FormCreate(Sender: TObject);
beginwith DBLookupComboBox1 dobegin
 DataSource := DataSource1; // -> AdoTable1 -> DBGrid1
 ListSource := DataSource2;
 DataField := 'AuthorEmail'; // from AdoTable1 - displayed in the DBGrid
 KeyField := 'Email';
 ListFields := 'Name; Email';
 
 Visible := False;
 end;
 DataSource2.DataSet := AdoQuery1;
 AdoQuery1.Connection := AdoConnection1;
 AdoQuery1.SQL.Text := 'SELECT Name, Email FROM Authors';
 AdoQuery1.Open;
end;

Note: When you want to display more than one field in a DBLookupComboBox, like in the above example, you have to make sure that all columns are visible. This is done by setting the DropDownWidth property.

However, you'll see that initially, you have to set this to a very large value which results in dropped list being too wide (in most cases). One workaround is to set the DisplayWidth of a particular Field shown in a drop-down list.

This code, placed inside the OnCreate event for the form, ensures that both the author name and it's email are displayed inside the drop-down list:

AdoQuery1.FieldByName('Email').DisplayWidth:=10;
AdoQuery1.FieldByName('Name').DisplayWidth:=10;
AdoQuery1.DropDownWidth:=150;

What's left for us to do, is to actually make a combo box hover over a cell (when in edit mode), displaying the AuthorEmail field. First, we need to make sure the DBLookupComboBox1 is moved and sized over the cell in which the AuthorEmail field is displayed.

procedure TForm1.DBGrid1DrawColumnCell
 (Sender: TObject; 
 const Rect: TRect; 
 DataCol: Integer; 
 Column: TColumn; 
 State: TGridDrawState);
beginif (gdFocused in State) thenbeginif (Column.Field.FieldName = DBLookupComboBox1.DataField) thenwith DBLookupComboBox1 do 
 begin
 Left := Rect.Left + DBGrid1.Left + 2;
 Top := Rect.Top + DBGrid1.Top + 2;
 Width := Rect.Right - Rect.Left;
 Width := Rect.Right - Rect.Left;
 Height := Rect.Bottom - Rect.Top;
 Visible := True;
 end;
 end
end;

Next, when we leave the cell, we have to hide the combo box:

procedure TForm1.DBGrid1ColExit(Sender: TObject);
beginif DBGrid1.SelectedField.FieldName = DBLookupComboBox1.DataField then 
 DBLookupComboBox1.Visible := False
end;

Note that when in editing mode, all keystrokes are going to the DBGrid's cell but we have to make sure they are sent to the DBLookupComboBox. In the case of a DBLookupComboBox, we are primarily interested in the [Tab] key; it should move the input focus to the next cell.

procedure TForm1.DBGrid1KeyPress(Sender: TObject; var Key: Char);
beginif (key = Chr(9)) then Exit;
 if (DBGrid1.SelectedField.FieldName = DBLookupComboBox1.DataField) thenbegin
 DBLookupComboBox1.SetFocus;
 SendMessage(DBLookupComboBox1.Handle, WM_Char, word(Key), 0);
 end
end;

When you pick an item ("row") from a DBLookupComboBox, the value or the corresponding KeyField field is stored as the value of the DataField field.