How to Place a Drop Down Pick List into a DBGrid

Close up of hand using checklist on digital tablet
Patrick George/Ikon Images/Getty Images

Here's how to place a drop down pick list into a DBGrid. Create visually more attractive user interfaces for editing lookup fields inside a DBGrid - using the PickList property of a DBGrid column.

Now, that you know what are lookup fields, and what are the options of displaying a lookup field in Delphi's DBGrid, it's time to see how to use the PickList property of a DGBrid column to enable a user to pick a value for a lookup field from a drop down list box.

A Quick Info on DBGrid Columns Property

A DBGrid control has a Columns property - a collection of TColumn objects representing all of the columns in a grid control. Columns can be set at design time through the Columns editor, or programmatically at runtime. You'll usually add Columns to a DBGird when you want to define how a column appears, how the data in the column is displayed and to access the properties, events, and methods of TDBGridColumns at runtime. A customized grid enables you to configure multiple columns to present different views of the same dataset (different column orders, different field choices, and different column colors and fonts, for example).

Now, each Column in a grid is "linked" to a field from a dataset displayed in the grid. What's more, each column has a PickList property. The PickList property lists values that the user can select for the column's linked field value.

Filling the PickList

What you will learn here is how to fill that String List with values from another dataset at run time. 
Recall, that we are editing the Articles table - and that a Subject field can only accept values from the Subjects table: ideal situation for the PickList!

Here's how to set up the PickList property.

First, we add a call to the SetupGridPickList procedure in the Form's OnCreate event handler.

procedure TForm1.FormCreate(Sender: TObject);
begin
  SetupGridPickList('Subject', 'SELECT Name FROM Subjects');
end;

The easiest way to create the SetupGridPickList procedure is to go to the private part of the form declaration, add the declaration there and hit the CTRL + SHIF + C key combination - Delphi's code completion will do the rest:

...
type
  TForm1 = class(TForm)
...
  private
    procedure SetupGridPickList(
        const FieldName : string; 
        const sql : string);
  public
...

Note: the SetupGridPickList procedure takes two parameters. The first parameter, FieldName, is the name of the field we want to act like a lookup field; the second parameter, sql, is the SQL expression we use to populate the PickList with possible values - in general the SQL expression should return a datataset with only one field.

Here's how the SetupGridPickList looks like:

procedure TForm1.SetupGridPickList(const FieldName, sql: string);
var 
  slPickList:TStringList;
  Query : TADOQuery;
  i : integer;
begin
  slPickList:=TStringList.Create;
  Query := TADOQuery.Create(self);
  try
    Query.Connection := ADOConnection1;
    Query.SQL.Text := sql;
    Query.Open;
    //Fill the string list
    while not Query.EOF do
    begin
      slPickList.Add(Query.Fields[0].AsString);
      Query.Next;
    end; //while

    //place the list it the correct column
    for i:=0 to DBGrid1.Columns.Count-1 do 
      if DBGrid1.Columns[i].FieldName = FieldName then
      begin
        DBGrid1.Columns[i].PickList:=slPickList;
        Break;
      end;
  finally
    slPickList.Free;
    Query.Free;
  end; 
end; (*SetupGridPickList*)

That's it. Now, when you click the Subject column (to enter into edit mode).

Note 1: by default, the drop-down list displays 7 values. You can change the length of this list by setting the DropDownRows property.

Note 2: nothing stops you from filling up the PickList from a list of values not coming from a database table. If, for example, you have a field that only accepts week day names ('Monday', ..., 'Sunday') you can build a "hard-coded" PickList.

"Uh, I need to click the PickList 4 times..."

Note that when you want to edit the field displaying a drop down list, you'll need to click the cell 4 times in order to actually pick a value from a list. The next code snippet, added to the DBGrid's OnCellClick event handler, mimics a hit to the F2 key followed by Alt + DownArrow.

procedure TForm1.DBGrid1CellClick(Column: TColumn);
begin
  //Making the drop-down pick list appear faster
  if Column.PickList.Count > 0 then
  begin
    keybd_event(VK_F2,0,0,0);
    keybd_event(VK_F2,0,KEYEVENTF_KEYUP,0);
    keybd_event(VK_MENU,0,0,0);
    keybd_event(VK_DOWN,0,0,0);
    keybd_event(VK_DOWN,0,KEYEVENTF_KEYUP,0);
    keybd_event(VK_MENU,0,KEYEVENTF_KEYUP,0);
  end;
end;