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.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;