Putting a TDBLookupComboBox in a Grid

Problem/Question/Abstract:

How do I display a DBLookupComboBox in a Grid?

Answer:

The TDBGrid is an interesting component in that it's not really a "grid;" rather, it's more or less a collection of rectangles that are dynamically drawn to display data. The operative word here is "dynamic." If you take a look at the events of a TDBGrid, you'll see an event handler called OnDrawDataCell. Without going into a lot of technical mumbo-jumbo, this event is responsible for drawing data (or whatever) in the "cell" of a grid. The default action, obviously, is to display the underlying data of the grid, but since it's visible, we have the opportunity of adding some enhanced functionality. And that's exactly what we do to display a drop-down edit box. Now some of you might be thinking at this point that if we're adding our own functionality to the OnDrawDataCell, are we actually manipulating the grid itself? The answer to that is no. What we're actually doing in this case is drawing OVER the cell to make it look like the cell is a drop-down. Okay, let's get to specifics...

Setting Up Your Application

The sample application that we'll be building is going to be a simple order entry screen. For simplicity's sake, we'll be using the the Orders.db and Customer.db tables from the DBDEMOS database that gets installed with Delphi, though you easily transfer what you do here to any other application where you need a lookup. For our application, we'll be using the Orders table as the data entry table, and the Customer table as the lookup to retrieve customer identifications. Okay, here we go...

The first thing you need to do is to create a new application in Delphi. On the main form of the application, drop the following components:

Two (2) TTable Components
Two (2) TDatasource Components
One (1) TDBGrid
One (1) TDBLookupComboBox (You can drop this anywhere, we'll be positioning it at runtime)

To make things easier, set both TTables' DatabaseName properties to "DBDEMOS." Point the first table (Table1) to ORDERS.DB, and the second table (Table2) to CUSTOMER.DB (this will be our lookup table). Point DataSource1 to Table1 and DataSource2 to Table2. In plain english, you're setting DataSource1 and Table1 to point to the data entry table, while DataSource2 and Table2 point to the lookup data table. From there it's a matter of setting DBGrid1 to point to DataSource1.

Now with the TDBLookupComboBox, you've got to set a few properties, which is why I separated its setup from the other components. Besides, setting the properties of a TDBLookupComboBox has caused more than enough consternation among developers over time. From my point of view, or at least from what I remember when I wanted to just use this component by itself, one of the most confusing things about it was the way the properties were listed in the object inspector. But I guess that's neither here nor there. In any case here's what you do:

Set the DataSource property to DataSource1 (the same one that the DBGrid points to).
Set the DataField property to the CustNo field (this is the field that you're going to put lookup information into).
Now, set the ListSource property to DataSource2
Set the ListField property to the CustNo field.
This one's important: Drop down the KeyField property field and select CustNo from the list (It's the only field available). This will form the link between the two tables.
Finally, set the Visible property of the component to False - I'll explain that in a bit.

Once you're done with the steps above, set the Active properties of both tables to True. If you've done everything right, data should be displaying in the grid and you should see a value appear in the DBLookupComboBox. Now on to coding...

Making It Work

As I mentioned above, in order to make it appear that the DBGrid has a drop-down lookup, we use the OnDrawDataCell to draw the lookup combo box over the cell in which we want to get lookup information. In order to make this totally seamless to the user, we have to fulfill a few criteria:

Move and size the DBLookupComboBox over the cell in which we want to look up information.
Handle the lookup's visibility as the user scrolls from cell to cell in grid.
Handle focus control when the user enters the lookup cell.
Handle movement out of the DBLookupComboBox

The first and second criteria are easily met by writing code for OnDrawDataCell and OnColExit event handlers on the grid:

procedure TForm1.DBGrid1DrawDataCell(Sender: TObject; const Rect: TRect;
  Field: TField; State: TGridDrawState);
begin
  //Regardless of cell, do we have input focus? Also,
  //is the field we're on the same as the data field
  //pointed to by the DBLookupComboBox? If so, then
  //Move the component over the cell.
  if ((gdFocused in State) and
    (Field.FieldName = DBLookupComboBox1.DataField)) then
    with DBLookupComboBox1 do
    begin
      Left := Rect.Left + DBGrid1.Left;
      Top := Rect.Top + DBGrid1.Top;
      Width := Rect.Right - Rect.Left;
      if ((Rect.Bottom - Rect.Top) > Height) then
        Height := Rect.Bottom - Rect.Top;
      Visible := True;
    end;
end;

procedure TForm1.DBGrid1ColExit(Sender: TObject);
begin
  //Are we leaving the field in the grid that
  //is also the data field for our lookup?
  with DBGrid1, DBLookupComboBox1 do
    if (SelectedField.FieldName = DataField) then
      Visible := False;
end;

As you can see above, the OnDrawDataCell event handles the movement and sizing of the DBLookupComboBox and sets its visibility to True, while the OnColExit sets its visibility to False. In both cases, the conditional statement includes a comparison between the grid's field and the data field pointed to by the combo box. If they're the same, then they act. In the case of the OnDrawDataCell event though, the conditional also includes an evaluation of the State parameter. This is incredibly important because we only want to perform the drawing if a cell has input focus. If we were to remove this conditional, the component would be continuously drawn, causing an irritating strobe. Not good.

The third criteria exists because the DBLookupComboBox is not really part of the grid; it merely floats above it. Furthermore, since we're controlling the combo's behavior from the grid, it really doesn't ever receive input focus. The net result is that keystrokes don't get sent to the combo box, they get sent to the grid, even if the combo is displaying above the cell and is highlighted! If you tried typing a new customer number into the DBLookupComboBox at this point, nothing would appear to be happening. The combo box would remain highlighted. Actually, there is something happening - the grid's cell is actually getting updated. But you can't see it. In that case, what we have to do is make the grid give focus to the combo box as keys are pressed, and the place you do this is in the OnKeyPress event of the grid:

//If you edit the value in the lookup field, the grid actually
//has focus, so unless the keystroke is a Tab, then we need to
//send keystrokes to the LookupCombo

procedure TForm1.DBGrid1KeyPress(Sender: TObject; var Key: Char);
begin
  if (Key <> Chr(9)) then
    with DBGrid1, DBLookupComboBox1 do
      if (SelectedField.FieldName = DataField) then
      begin
        SetFocus;
        SendMessage(Handle, WM_CHAR, Word(Key), 0);
      end;
end;

The code above first checks the keypress to see if it isn't a Tab. If it was, it's ignored, and the user can move to an adjacent cell. But for any other key, we do our conditional to see if the field in the cell is the same as the data field for the combo. In that case, focus is set to the DBLookupComboBox and we send the keystroke message to it using the Win API SendMessage function. As much as possible, you want to avoid going to the Win API, but in this case, it's the only way to send a message.

Building on the third criteria, once you give focus control to the DBLookupCombo, it keeps focus. That's not bad in and of itself, but there's a catch. When you Tab out of the box, what happens is that focus is returned to the grid, but focus is also returned to the underlying cell. This means that in order to move to the next field, the user is forced to press Tab twice! There's no way to get around this phenomenon. However, there is a bit of trickery you can perform that will programmatically send another Tab to the grid. You do this in the OnKeyUp event of the DBGrid:

//If you choose an item from the lookup, you give focus
//control to it. The net result is that it takes two
//Tabs to move to the next cell. In that case, we need
//to send another Tab keystroke to the grid so that only
//one keystroke is needed to move to the next cell.

procedure TForm1.DBGrid1KeyUp(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if (Key in [VK_TAB]) and InBox then
  begin
    SendMessage(DBGrid1.Handle, WM_KEYDOWN, Key, 0);
    InBox := False;
  end;
end;

Notice the variable that's being set here: InBox. This is an implemenation-level variable that is used to determine whether or not the user has entered the CustNo cell. It's set to True in the OnEnter event of the combo box. Then in the OnKey up, if InBox is true and the keypress was a Tab, then we send the keystroke again. Otherwise, it's ignored. Here's the OnEnter of the DBLookupComboBox:

procedure TForm1.DBLookupComboBox1Enter(Sender: TObject);
begin
  InBox := True;
end;

Pretty straight forward....

But there is just one more tidbit that I have to throw at you to make this work problem-free.

One Last Tidbit

There's an option in the options property of the TDBGrid called dgCancelOnExit. This option is defined as follows in the online help:

When the user exits the grid from an inserted record to which the user made no modifications, the inserted record is not posted to the dataset. This prevents the inadvertent posting of empty records.

What does this have to do with what we're doing here? Well, let's say you insert a new record into the grid. If you immediately click on the CustNo lookup combo, your new record will disappear. Why? Well, based upon the definition above and based upon the code presented here, if you went to the CustNo field immediately following an insert, the grid would lose input focus! When dgCancelOnExit is set to True, if the grid loses focus before the record has been posted, the new row is deleted. Luckily, setting this option to False alleviates the problem.

Putting It All Together

To make the job of performing this technique easier, here's the full code listing of the form I used for the sample application:

unit main;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, DBCtrls, Db, DBTables;

type
  TForm1 = class(TForm)
    Table1: TTable;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    Table2: TTable;
    Table1OrderNo: TFloatField;
    Table1CustNo: TFloatField;
    Table1SaleDate: TDateTimeField;
    Table1ShipDate: TDateTimeField;
    Table1EmpNo: TIntegerField;
    Table1AmountPaid: TCurrencyField;
    Table2CustNo: TFloatField;
    DBLookupComboBox1: TDBLookupComboBox;
    DBGrid1: TDBGrid;
    procedure DBGrid1DrawDataCell(Sender: TObject; const Rect: TRect;
      Field: TField; State: TGridDrawState);
    procedure DBGrid1ColExit(Sender: TObject);
    procedure DBGrid1KeyPress(Sender: TObject; var Key: Char);
    procedure DBGrid1KeyUp(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure DBLookupComboBox1Enter(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation
var
  InBox: Boolean;
{$R *.DFM}

procedure TForm1.DBGrid1DrawDataCell(Sender: TObject; const Rect: TRect;
  Field: TField; State: TGridDrawState);
begin
  //Regardless of cell, do we have input focus? Also,
  //is the field we're on the same as the data field
  //pointed to by the DBLookupComboBox? If so, then
  //Move the component over the cell.
  if (gdFocused in State) and
    (Field.FieldName = DBLookupComboBox1.DataField) then
    with DBLookupComboBox1 do
    begin
      Left := Rect.Left + DBGrid1.Left;
      Top := Rect.Top + DBGrid1.Top;
      Width := Rect.Right - Rect.Left;
      if ((Rect.Bottom - Rect.Top) > Height) then
        Height := Rect.Bottom - Rect.Top;
      Visible := True;
    end;
end;

procedure TForm1.DBGrid1ColExit(Sender: TObject);
begin
  //Are we leaving the field in the grid that
  //is also the data field for our lookup?
  with DBGrid1, DBLookupComboBox1 do
    if (SelectedField.FieldName = DataField) then
      Visible := False;
end;

//If you edit the value in the lookup field, the grid actually
//has focus, so unless the keystroke is a Tab, then we need to
//send keystrokes to the LookupCombo

procedure TForm1.DBGrid1KeyPress(Sender: TObject; var Key: Char);
begin
  if (Key <> Chr(9)) then
    with DBGrid1, DBLookupComboBox1 do
      if (SelectedField.FieldName = DataField) then
      begin
        SetFocus;
        SendMessage(Handle, WM_CHAR, Word(Key), 0);
      end;
end;

//If you choose an item from the lookup, you give focus
//control to it. The net result is that it takes two
//Tabs to move to the next cell. In that case, we need
//to send another Tab keystroke to the grid so that only
//one keystroke is needed to move to the next cell.

procedure TForm1.DBGrid1KeyUp(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if (Key in [VK_TAB]) and InBox then
  begin
    SendMessage(DBGrid1.Handle, WM_KEYDOWN, Key, 0);
    InBox := False;
  end;
end;

procedure TForm1.DBLookupComboBox1Enter(Sender: TObject);
begin
  InBox := True;
end;

end.

So now, you have everything you need to "drop" a TDBLookupComboBox onto a grid. By the way, you can use this technique for ANY windowed component; that is, any component that has a Handle property. This includes forms, panels, memos, etc.. Try it out!

Note: Some of you old hats at Delphi might immediately exclaim, "What's the use of this article? In Delphi 3 and above, we have  the capability of specifying a cell in a DBGrid to be a drop-down edit." Well, that's the thing, isn't it? You have to fill in the values of the Items property yourself. What I'm suggesting here is adding a TDBLookupComboBox that will enable you to look up information from another data source. This isn't available in ANY version of Delphi.

By the way, this isn't my original idea, and in fact, the technique has been around since Delphi 1. But it's valid and applicable to later versions of Delphi.