How do I display a DBLookupComboBox in a Grid? Woll2Woll's InfoPower
wwDBGrid enables you to do this, but how do I do it myself in Delphi?
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. |
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!
Copyright © 1998 Brendan V. Delumpa
|