Here's how to place a DBLookupComboBox into a DBGrid. Create visually more attractive user interfaces for editing lookup fields inside a DBGrid – place a DBLookupComboBox into a cell of a DBGrid.
What a great idea! Let's see how to create the best data editing grid ever!
CREATING A LOOKUP WITH A DBLOOKUPCOMBOBOX
To show a DBLookupComboBox inside a cell of a DBGrid we'll need to make one available for us at run time.
Select the "Data controls" page on the Component Palette and pick a DBLookupComboBox. Drop one anywhere on the form (leave the default name, "DBLookupComboBox1") – it doesn't matter where, since most of the time it will be invisible or floating over the grid.
Next, you have to add one more DataSource and a DataSet component to "fill" the combo box with values. Drop a TDataSource (name: DataSource2) and a TAdoQuery (name: AdoQuery1) anywhere on the form.
For a DBLookupComboBox to work properly several more properties must be set, these properties are key to the lookup connection:
- DataSource and DataField determine the main connection. The DatField 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 (one or more) of the lookup dataset that are actually displayed in the combo. ListField can show more than one field. Multiple field names 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); begin with DBLookupComboBox1 do begin 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, as in the example above, 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. The next code snippet, placed inside the OnCreate event for the form, ensures that both author name and it's email are displayed inside the drop down list:
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. I'll show you only the code here (you'll have the option to download the entire project later):
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); begin if (gdFocused in State) then begin if (Column.Field.FieldName = DBLookupComboBox1.DataField) then with 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); begin if DBGrid1.SelectedField.FieldName = DBLookupComboBox1.DataField then DBLookupComboBox1.Visible := False end;
Next, note that when in editing mode, all keystrokes are going to the DBGrid's cell, 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 – [Tab] should move the input focus to the next cell.
procedure TForm1.DBGrid1KeyPress(Sender: TObject; var Key: Char); begin if (key = Chr(9)) then Exit; if (DBGrid1.SelectedField.FieldName = DBLookupComboBox1.DataField) then begin DBLookupComboBox1.SetFocus; SendMessage(DBLookupComboBox1.Handle, WM_Char, word(Key), 0); end end;