Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1370
  • Last Modified:

Adding (none selected) to dblookupcombobox

How can I add the value (none selected) to a dblookupcombobox dropdownlist?

When user selects this value the table's fieldvalue must be set to 0 or cleared. So I guess selecting it will result in a combobox indexvalue of -1.

Please supply some working code.
Thanks. The Wiz,

PS: I'm using Developer Express 5.13 components, so if you know how to use it with those components I would be very greatfull. Otherwise I will figure that part out myself.
0
Stef Merlijn
Asked:
Stef Merlijn
  • 3
  • 2
  • 2
1 Solution
 
kretzschmarCommented:
usual you could do this in the LookupDataset itself like
(should be a query then)

Select KeyfieldName, ResultFieldName from LookupTableName
Union all  
Select 0, 'none selected' from LookupTableName

hope this helps you

meikl ;-)
0
 
kretzschmarCommented:
btw. if you want this entry also direct after editing a new record
you could use

Select KeyfieldName, ResultFieldName from LookupTableName
Union all  
Select NULL, 'none selected' from LookupTableName

instead

meikl ;-)
0
 
Pierre CorneliusCommented:
Let's say for arguments sake you have a client master data table with 2 fields
1. ClientCode
2. ClientName

You could Add a record for your purpose such as
1. ClientCode = 0
2. ClientName = "None Selected"

This will then also appear as an option in the lookup list box.

You can then set the NullValueKey property of the lookup control to "Del" so that if the user presses the delete key when on the control, it would automatically select the None Selected option.

I have put together a demo for you using the clients table from the DBDemos Database that comes with Delphi. Once you start the app, simply add a record in the grid and set the FirstName field to "None selected" and the ACCT_NBR field to 0.

unit main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBClient, Grids, DBGrids, DBTables, DBCtrls, StdCtrls;

type
  TForm1 = class(TForm)
    DBLookupComboBox1: TDBLookupComboBox;
    DataSource1: TDataSource;
    Table1: TTable;
    DBGrid1: TDBGrid;
    ClientDataSet1: TClientDataSet;
    DataSource2: TDataSource;
    ClientDataSet1AccNum: TFloatField;
    DBText1: TDBText;
    Label1: TLabel;
    procedure ClientDataSet1AccNumGetText(Sender: TField; var Text: String;
      DisplayText: Boolean);
    procedure FormCreate(Sender: TObject);
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.ClientDataSet1AccNumGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
begin
  if Sender.AsInteger = 0
    then Text:= 'None selected.'
    else Text:= Sender.AsString;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  ClientDataSet1.Edit;
  ClientDataSet1.FieldByName('AccNum').AsInteger:= 0;
  ClientDataSet1.Post;
end;

end.
object Form1: TForm1
  Left = 191
  Top = 115
  Width = 366
  Height = 298
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBText1: TDBText
    Left = 168
    Top = 24
    Width = 3
    Height = 13
    AutoSize = True
    DataField = 'AccNum'
    DataSource = DataSource2
  end
  object Label1: TLabel
    Left = 168
    Top = 8
    Width = 81
    Height = 13
    Caption = 'Keyfield value'
    Font.Charset = DEFAULT_CHARSET
    Font.Color = clWindowText
    Font.Height = -11
    Font.Name = 'MS Sans Serif'
    Font.Style = [fsBold]
    ParentFont = False
  end
  object DBLookupComboBox1: TDBLookupComboBox
    Left = 16
    Top = 24
    Width = 145
    Height = 21
    DataField = 'AccNum'
    DataSource = DataSource2
    KeyField = 'ACCT_NBR'
    ListField = 'FIRST_NAME'
    ListSource = DataSource1
    NullValueKey = 46
    TabOrder = 0
  end
  object DBGrid1: TDBGrid
    Left = 16
    Top = 56
    Width = 320
    Height = 185
    DataSource = DataSource1
    TabOrder = 1
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object DataSource1: TDataSource
    DataSet = Table1
    Left = 96
    Top = 152
  end
  object Table1: TTable
    Active = True
    DatabaseName = 'DBDEMOS'
    TableName = 'clients.dbf'
    Left = 136
    Top = 152
  end
  object ClientDataSet1: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    Left = 312
    Top = 8
    Data = {
      290000009619E0BD010000001800000001000000000003000000290006416363
      4E756D08000400000000000000}
    object ClientDataSet1AccNum: TFloatField
      FieldName = 'AccNum'
      OnGetText = ClientDataSet1AccNumGetText
    end
  end
  object DataSource2: TDataSource
    DataSet = ClientDataSet1
    Left = 280
    Top = 8
  end
end

Kind regards
Pierre Cornelius

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Pierre CorneliusCommented:
You could also derive your own ComboBox control to do this e.g.

  TMyDBComboBox = class(TDBComboBox)
    FLookupSource: TDataSource;
    FLookupField: TField;
    FKeyField: TField;
    FValues: TStringList;
  private
    procedure GetLookupList;
    procedure SetLookupSource(AValue: TDataSource);
    procedure SetKeyField(AValue: TField);
    procedure SetLookupField(AValue: TField);
    procedure ItemSelected(Sender: TObject);
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
  published
    property LookupSource: TDataSource read FLookupSource write SetLookupSource;
    property LookupField: TField read FLookupField write SetLookupField;
    property KeyField: TField read FKeyField write SetKeyField;
  end;

{ TMyDBComboBox }

constructor TMyDBComboBox.Create(AOwner: TComponent);
begin
  inherited;
  FLookupSource:= TDataSource.Create(self);
  FLookupField:= TField.Create(FLookupSource.DataSet);
  FKeyField:= TField.Create(FLookupSource.DataSet);
  FValues:= TStringList.Create;
  OnSelect:= ItemSelected;
  GetLookupList;
end;

destructor TMyDBComboBox.Destroy;
begin
  FValues.Free;
  inherited;
end;

procedure TMyDBComboBox.GetLookupList;
begin
  if (FlookupSource.DataSet <> nil)
      AND (FLookupField.FieldName <> '')
      AND (FKeyField.FieldName <> '') then
  with FLookupSource do
  begin
    if DataSet.Active then
    begin
      Items.Clear;
      FValues.Clear;
      DataSet.First;
      Items.Add('None selected');
      FValues.Add('0');
      While not DataSet.Eof do
      begin
        Items.Add(DataSet.FieldByName(FLookupField.FieldName).AsString);
        FValues.Add(DataSet.FieldByName(FKeyField.FieldName).Value);
        DataSet.Next;
      end;
    end;
  end;
end;

procedure TMyDBComboBox.ItemSelected(Sender: TObject);
begin
  try
    DataSource.DataSet.FieldByName(DataField).Value:= FValues[self.ItemIndex];
  except
  end;
end;

procedure TMyDBComboBox.SetKeyField(AValue: TField);
begin
  if AValue <> FKeyField then
  begin
    FKeyField:= AValue;
    GetLookupList;
  end;
end;

procedure TMyDBComboBox.SetLookupField(AValue: TField);
begin
  if AValue <> FLookupField then
  begin
    FLookupField:= AValue;
    GetLookupList;
  end;
end;

procedure TMyDBComboBox.SetLookupSource(AValue: TDataSource);
begin
  if AValue <> FLookupSource then
  begin
    FLookupSource:= AValue;
    GetLookupList;
  end;
end;


NOTE:
The above is very basic and serves just as illustration.
For example, you could add a event handler in case of the dataset changing to call the GetLookupList again.
The point I'm trying to make is that you can customise it in just about any way you want depending on the amount of time you want to spend on it.

Kind regards
Pierre
0
 
Stef MerlijnDeveloperAuthor Commented:
Hi Pierre,

You mention this in your first example.

  object ClientDataSet1: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    Left = 312
    Top = 8
    Data = {
      290000009619E0BD010000001800000001000000000003000000290006416363
      4E756D08000400000000000000}
    object ClientDataSet1AccNum: TFloatField
      FieldName = 'AccNum'
      OnGetText = ClientDataSet1AccNumGetText
    end
  end

How do I get this Datapacked into my project? Where does it come from, it's not a property of the ClientDataSet?
0
 
Stef MerlijnDeveloperAuthor Commented:
And second, Pierre:

How to implement the TMyComboBox (without adding it as a new component to my Delphi Ide).

Thanks Stef
0
 
Pierre CorneliusCommented:
Your first question:
===========

I defined the 2 fields at design time and right-clicked on the ClientDataSet and selected "create dataset". That's where it comes from. To get it to work simply do the following:
1. Create a new project
2. View the form
3. press Alt + F12
4. copy and paste the following into the .dfm file:

object Form1: TForm1
  Left = 191
  Top = 115
  Width = 366
  Height = 298
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBText1: TDBText
    Left = 168
    Top = 24
    Width = 3
    Height = 13
    AutoSize = True
    DataField = 'AccNum'
    DataSource = DataSource2
  end
  object Label1: TLabel
    Left = 168
    Top = 8
    Width = 81
    Height = 13
    Caption = 'Keyfield value'
    Font.Charset = DEFAULT_CHARSET
    Font.Color = clWindowText
    Font.Height = -11
    Font.Name = 'MS Sans Serif'
    Font.Style = [fsBold]
    ParentFont = False
  end
  object DBLookupComboBox1: TDBLookupComboBox
    Left = 16
    Top = 24
    Width = 145
    Height = 21
    DataField = 'AccNum'
    DataSource = DataSource2
    KeyField = 'ACCT_NBR'
    ListField = 'FIRST_NAME'
    ListSource = DataSource1
    NullValueKey = 46
    TabOrder = 0
  end
  object DBGrid1: TDBGrid
    Left = 16
    Top = 56
    Width = 320
    Height = 185
    DataSource = DataSource1
    TabOrder = 1
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object DataSource1: TDataSource
    DataSet = Table1
    Left = 96
    Top = 152
  end
  object Table1: TTable
    Active = True
    DatabaseName = 'DBDEMOS'
    TableName = 'clients.dbf'
    Left = 136
    Top = 152
  end
  object ClientDataSet1: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    Left = 312
    Top = 8
    Data = {
      290000009619E0BD010000001800000001000000000003000000290006416363
      4E756D08000400000000000000}
    object ClientDataSet1AccNum: TFloatField
      FieldName = 'AccNum'
      OnGetText = ClientDataSet1AccNumGetText
    end
  end
  object DataSource2: TDataSource
    DataSet = ClientDataSet1
    Left = 280
    Top = 8
  end
end

5. Press ALT + F12 again to return to form view


Your second question:
==============

Add the following to the FormCreate event:

  with TMyDBComboBox.Create(self) do
  begin
    parent:= self;
    Left:= 16;
    Top:= 248;
    DataSource:= DataSource2;
    DataField:= 'AccNum';
    LookupSource:= DataSource1;
    KeyField   := Table1.FieldByName('ACCT_NBR');
    LookupField:= Table1.FieldByName('Address_1');
  end;


Let me know if you have any more hassles...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now