1. Tech

Your suggestion is on its way!

An email with a link to:

http://delphi.about.com/od/usedbvcl/l/aa101403b.htm

was emailed to:

Thanks for sharing About.com with others!

Drop down list (DBLookupComboBox) inside a DBGrid - part 2
Page 2: DBLookupComboBox in a DBGrid!
 Win prizes by sharing code!
Do you have some Delphi code you want to share? Are you interested in winning a prize for your work?
Delphi Programming Quickies Contest
 More of this Feature
• Part 1: Three ways to set up a drop down list in a DBGrid.

• Page 1: DBLookupComboBox in a DBGrid?


• Download sample QuickiesContest.mdb database


• Download full source CODE
 Join the Discussion
"Post your views, comments, questions and doubts to this article."
Discuss!
 Related Resources
• Adding components into a DBGrid - the theory

• free DB Course.TOC

• DBGrid related articles and tips

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
TDBLookupComboBox 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:
     AdoQuery1.FieldByName('Email').DisplayWidth:=10;
     AdoQuery1.FieldByName('Name').DisplayWidth:=10;
     AdoQuery1.DropDownWidth:=150;

    Magic...
    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. We've already discussed the theory - 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;
    

    That's it. Run the project and voila ... one nicely looking drop down list box (with two columns!) all over the AuthorEmail field's column.
    What does happen when you select a value (Name; Email) from the drop down list? 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.

    DBLookupComboBox in a DBGrid

    If you need any help, after you download and explore the full source code for this project, I encourage you to post any questions on the Delphi Programming Forum.

    Need more DBGrid related articles?
    Be sure to check the "Adding components to a DBGrid" article and the rest of the articles dealing with the DBGrid (and other db-aware) components; of course don't miss the "DBGrid to the Max" article collection!

    First page > DBLookupComboBox in a DBGrid? > Page 1, 2

  • ©2014 About.com. All rights reserved.