Solved

Adding (none selected) to dblookupcombobox

Posted on 2004-08-26
7
1,330 Views
Last Modified: 2010-05-18
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
Comment
Question by:Delphiwizard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 11910276
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 11910287
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
 
LVL 14

Accepted Solution

by:
Pierre Cornelius earned 500 total points
ID: 11911334
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!

 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 11911661
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
 

Author Comment

by:Delphiwizard
ID: 11923941
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
 

Author Comment

by:Delphiwizard
ID: 11924012
And second, Pierre:

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

Thanks Stef
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 11929217
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question